SQL Server Express 2008, Joins, and Timeout Expired Error Message

Question

I have a stored procedure that does quite a bit of joins. The query though runs pretty fast, around 3 seconds. I just cant figure out the below error poping up every once in a while. I event cache the document that uses this query for a minute so it doesnt get ran over and over. I am using Entity Framework 5, and my stored procedure is using a CTE to do the paging. Any clues or insight?

 System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at System.Data.Objects.ObjectContext.ExecuteStoreQueryInternal[TElement](String commandText, String entitySetName, MergeOption mergeOption, Object[] parameters)
   at System.Data.Objects.ObjectContext.ExecuteStoreQuery[TElement](String commandText, Object[] parameters)
   at System.Data.Entity.Internal.InternalContext.ExecuteSqlQuery[TElement](String sql, Object[] parameters)
   at System.Data.Entity.Internal.InternalContext.ExecuteSqlQueryAsIEnumerable[TElement](String sql, Object[] parameters)
   at System.Data.Entity.Internal.InternalContext.ExecuteSqlQuery(Type elementType, String sql, Object[] parameters)
   at System.Data.Entity.Internal.InternalSqlNonSetQuery.GetEnumerator()
   at System.Data.Entity.Internal.InternalSqlQuery`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Tournaments.Data.Repositories.Games.GamesRepository.GetGamesPaged(IGamesCriteria criteria)

Entity Framework Method

 public PagedResult<GameComplex> GetGamesPaged(IGamesCriteria criteria)
        {
            var results = DataContext.Database.SqlQuery<GameComplex>("EXEC [Schema].[Database] @Page, @PageSize, @SortOrder, @SortDirection, @EventId, @DivisionId, @DivisionTeamId, @Date, @SearchToken, @MemberId",
                new SqlParameter("Page", criteria.Page),
                new SqlParameter("PageSize", criteria.PageSize),
                new SqlParameter("SortOrder", GetDataValue(criteria.SortOrder)), 
                new SqlParameter("SortDirection", GetDataValue(criteria.SortDirection)),
                new SqlParameter("EventId", GetDataValue(criteria.EventId)),
                new SqlParameter("DivisionTeamId", GetDataValue(criteria.DivisionTeamId)), 
                new SqlParameter("DivisionId", GetDataValue(criteria.DivisionId)), 
                new SqlParameter("Date", GetDataValue(criteria.Date)),
                new SqlParameter("SearchToken", GetDataValue(criteria.SearchToken)),
                new SqlParameter("MemberId", GetDataValue(criteria.MemberId))).ToList();

            return new PagedResult<GameComplex>
                       {
                           Page = criteria.Page,
                           PageSize = criteria.PageSize,
                           Total = results.Any(q => q != null) ? results.FirstOrDefault().Total : 0,
                           Results = results
                       };
        }

SQL Server Stored Procedure Parameter Signature

ALTER PROCEDURE [Schema].[Database]
    @Page INT = 1,
    @PageSize INT = 10,
    @SortOrder NVARCHAR(100) = 'Id',
    @SortDirection VARCHAR(4) = 'ASC',
    @EventId INT = NULL,
    @DivisionId INT = NULL,
    @DivisionTeamId INT = NULL,
    @Date DATETIME = NULL,
    @SearchToken NVARCHAR(100) = NULL,
    @MemberId INT = NULL
AS

score 3 · Accepted Answer · edited May 23 '17 at 11:59

3

You may need to adjust the COMMAND timeout.

See:

Set Command Timeout in entity framework 4.3

or

How to set CommandTimeout for DbContext?

EDIT

Command Timeout:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

Connection Timeout:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx

EDIT:

Another possible issue is "parameter sniffing".

http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx

So try one of the parameter sniffing workarounds:

ALTER PROCEDURE [Schema].[Database]
    @Page INT = 1,
    @PageSize INT = 10,
    @SortOrder NVARCHAR(100) = 'Id',
    @SortDirection VARCHAR(4) = 'ASC',
    @EventId INT = NULL,
    @DivisionId INT = NULL,
    @DivisionTeamId INT = NULL,
    @Date DATETIME = NULL,
    @SearchToken NVARCHAR(100) = NULL,
    @MemberId INT = NULL
AS

Declare @PageCopyOf int
Select @PageCopyOf = @Page


Declare @PageSizeCopyOf int
Select @PageSizeCopyOf = @PageSize

Declare @SortOrderCopyOf NVARCHAR(100)
Select @SortOrderCopyOf = @SortOrder

Declare @SortDirectionCopyOf VARCHAR(4) 
Select @SortDirectionCopyOf = @SortDirection

Declare @EventIdCopyOf int
Select @EventIdCopyOf = @EventId

Declare @DivisionIdCopyOf int
Select @DivisionIdCopyOf = @DivisionId 


Declare @DivisionTeamIdCopyOf int
Select @DivisionTeamIdCopyOf = @DivisionTeamId

Declare @DateCopyOf DATETIME
Select @DateCopyOf = @Date

Declare @SearchTokenCopyOf  NVARCHAR(100)
Select @SearchTokenCopyOf = @SearchToken

Declare @MemberIdCopyOf int
Select @MemberIdCopyOf = @MemberId

And then everything below this uses/consumes the @XXXXXCopyOf variable and NOT the original variable (name).

It's worth a try.

edited May 23 '17 at 11:59

Community

1
1

answered May 14 '13 at 19:17

granadaCoder

26,328
10
113
146

Does command timeout work the same way for a stored procedure? Look at code above. – Mike Flynn May 14 '13 at 19:31
In ADO.NET terms....SqlCommand.Timeout IS exactly how long it will wait to run stored procedure before it will timeout and "give up". – granadaCoder May 14 '13 at 19:32
Ok I found the command object and updated it. If this works Ill mark your answer instead since it makes more sense. – Mike Flynn May 14 '13 at 19:34
If that doesn't work, the please post the SIGNATURE of your stored procedure. and I'll follow up with a parameter sniffing suggestion. – granadaCoder May 14 '13 at 19:52
Still doing it, signature posted above. – Mike Flynn May 20 '13 at 17:10
If you set the SqlCommand.Timeout to 0 (zero), it is "infinite". Try that and put some timemarkers (or timespan objects) before and after the call and see how long one of them takes..just for kicks. – granadaCoder May 20 '13 at 17:46
Thanks, in layman terms what will the copyofparameters do above to help or sniff? – Mike Flynn May 20 '13 at 18:49
Its kinda like....you have this stored procedure. And the first time it runs, the system "remembers" how it used (for those original parameters) and says "future calls will probably look like this". Well, the future calls may have different parameter values, and the "remembered plan" is totally inefficient for the future calls. So your future calls get screw balled because of the way it was first called. That is probably half-wrong and half-right, but that's how I think about it. The tell tale is when you run it in SSMS and it runs in .333 milliseconds but the calling app, it runs in 5min – granadaCoder May 20 '13 at 19:18
Are you saying the parameter sniffing workaround addressed your issue? Or you just asking? – granadaCoder May 20 '13 at 19:18
I was asking what it will do. – Mike Flynn May 22 '13 at 05:59
I believe the timeout C# code changes worked. I havent seen any since, thanks! – Mike Flynn May 31 '13 at 20:16
Any way you could post exactly what you did (append/edit your original post) so future readers can learn from it? – granadaCoder Jul 31 '13 at 00:42

ExactaBox · Answer 2 · 2013-05-14T19:25:01.680

1

Just a guess, but the query runs fast (3 seconds) only when it is cached. Otherwise, it takes a lot longer and exceeds your server timeout setting. Since System.Data.SqlClient is raising the exception, it is likely that the default timeout is only 15 seconds.

MSDN: "The default value is 15 seconds"

Alternatively, try the CommandTimeout property of the SQLCommand object, which defaults to 30 seconds.

CommandTimeout

edited May 14 '13 at 19:25

answered May 14 '13 at 18:59

ExactaBox

3,235
16
27

I have 180 seconds in connection string, do you think that isnt the correct place? – Mike Flynn May 14 '13 at 19:00
Try ConnectTimeout without a spece between the words. From that same page: "You can set the amount of time a connection waits to time out by using the ConnectTimeout or Connection Timeout keywords" – ExactaBox May 14 '13 at 19:02
I see only examples with spaces, but I went with "Connection" instead of "Connect". Ill see if that fixes the issue and if so Ill mark this as correct. – Mike Flynn May 14 '13 at 19:08
Connect Timeout=180 is the time it will wait to "find and talk to sql server"... like if you try to connect to a SqlServer and put in an errant name for the server/Instance..it will try for this long to CONNECT....... That is NOT a Command Timeout. Command Timeout is for each cmd.SomeAction (ExecuteNonQuery for example) that you try. – granadaCoder May 14 '13 at 19:11
In the "Remarks" section of the linked page, in the first sentence, it says you can use ConnectTimeout or Connection Timeout. The first one does not have a space. – ExactaBox May 14 '13 at 19:12
I guess you're right. Ill see what Connection does. If I want to update command timeout, how do I go about doing that? – Mike Flynn May 14 '13 at 19:15
I updated with a link to the MSDN CommandTimeout page. You'll have to edit your code, I don't think you can make this adjustment inside the connection string. – ExactaBox May 14 '13 at 19:28

SQL Server Express 2008, Joins, and Timeout Expired Error Message

2 Answers2