Sometimes get Timeout expired exception with Linq to EF and after a restart of server, it works fine

Question

I have hosted my website at rackspace cloud server.

Sometimes I get the below exception while fetching records from database. The strange thing is that everything works fine for a few days after a restart and also I get this exception in only a few places and not everywhere.

Exception: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Stack Trace:


[SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +404
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +412
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1363
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +59
   System.Data.SqlClient.SqlDataReader.get_MetaData() +118
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6387937
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +6389506
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +538
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +28
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +256
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +19
   System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +553

[EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.]
   DataAccessLayer.dbCustomer.CompanyCustomerByLocationId(String sortExpression, Boolean asc, String nameSearchString, Int32 startRowIndex, Int32 maximumRows, Int32& TotalRecord, Int64 companyId) in D:\Dotnet Projects\FutureZoom\FutureZoom\DataAccessLayer\dbCustomer.cs:508
   BusinessLogic.Customer.CompanyCustomerByLocationId(String sortExpression, Boolean asc, String nameSearchString, Int32 startRowIndex, Int32 maximumRows, Int64 CompanyId) in D:\Dotnet Projects\FutureZoom\FutureZoom\BusinessLogic\Customer.cs:431
   FutureZoom.Areas.Admin.Controllers.CompanyController.CustomerPaging(Int32 StartIndex, Int32 PageSize, String SortExp, Boolean Asc, String SearchExp, String Where) in D:\Dotnet Projects\FutureZoom\FutureZoom\FutureZoom\Areas\Admin\Controllers\CompanyController.cs:552
   lambda_method(Closure , ControllerBase , Object[] ) +362
   System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +248
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +39
   System.Web.Mvc.<>c__DisplayClass15.<InvokeActionMethodWithFilters>b__12() +125
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +640
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +312
   System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +691
   System.Web.Mvc.Controller.ExecuteCore() +162
   System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +305
   System.Web.Mvc.<>c__DisplayClassb.<BeginProcessRequest>b__5() +62
   System.Web.Mvc.Async.<>c__DisplayClass1.<MakeVoidDelegate>b__0() +20
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +469
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +375

My Linq query is mentioned below

query = context.Customers.Include("Company").Include("Company.EmailCampaignerStatus")
                                           .OrderBy(m => m.EmailCampaignerStatus.FirstOrDefault().CreatedOn)                                           
                                           .Where(m => m.FirstName.StartsWith(name) || m.LastName.StartsWith(name) || m.Email.StartsWith(name) || (m.FirstName + " " + m.LastName).StartsWith(name))
                                           .Where(m => m.CompanyId == companyId)
                                           .Where(m => m.CompanyLocationId == locationId)
                                           .Skip(startRowIndex == 0 ? startRowIndex : startRowIndex - 1)
                                           .Take(maximumRows)
                                           .ToList();

Execution time for this query is not even 1 second on my local machine and also the same at server side after a restart but the execution time increases day by day and it fails to execute after few days (sometime fails and sometimes gives result).

I observed that it took 31.05sec and fails and it take 51.09 sec and fails. Sometimes it takes more than 2 mins and gives result.

I talked with rackspace people but they were unable to trace the actual cause for the same.

Edit 1: I have seen below queries in SQL Profiler against my paging query

SELECT 
[Project3].[Id1] AS [Id], 
[Project3].[C1] AS [C1], 
[Project3].[Id] AS [Id1], 
[Project3].[CompanyId] AS [CompanyId], 
[Project3].[CompanyLocationId] AS [CompanyLocationId], 
[Project3].[FirstName] AS [FirstName], 
[Project3].[LastName] AS [LastName], 
[Project3].[Email] AS [Email], 
[Project3].[Address1] AS [Address1], 
[Project3].[Address2] AS [Address2], 
[Project3].[City] AS [City], 
[Project3].[State] AS [State], 
[Project3].[Country] AS [Country], 
[Project3].[Zip] AS [Zip], 
[Project3].[Phone] AS [Phone], 
[Project3].[SaleDate] AS [SaleDate], 
[Project3].[Notes] AS [Notes], 
[Project3].[Cost] AS [Cost], 
[Project3].[CreatedOn] AS [CreatedOn], 
[Project3].[ModifiedOn] AS [ModifiedOn], 
[Project3].[PrimaryReviewSiteId] AS [PrimaryReviewSiteId], 
[Project3].[Id2] AS [Id2], 
[Project3].[SubscriptionTypeId] AS [SubscriptionTypeId], 
[Project3].[PlanFeeAmount] AS [PlanFeeAmount], 
[Project3].[LoginId] AS [LoginId], 
[Project3].[IndustryTypeId] AS [IndustryTypeId], 
[Project3].[CompanyName] AS [CompanyName], 
[Project3].[NumberOfLocations] AS [NumberOfLocations], 
[Project3].[LogoImageName] AS [LogoImageName], 
[Project3].[WebsiteUrl] AS [WebsiteUrl], 
[Project3].[blsActive] AS [blsActive], 
[Project3].[Pending] AS [Pending], 
[Project3].[ExpiryDate] AS [ExpiryDate], 
[Project3].[C2] AS [C2], 
[Project3].[Id3] AS [Id3], 
[Project3].[UploadTicketId] AS [UploadTicketId], 
[Project3].[UploadStatus] AS [UploadStatus], 
[Project3].[CompanyId1] AS [CompanyId1], 
[Project3].[CompanyLocationId1] AS [CompanyLocationId1], 
[Project3].[CustomerId] AS [CustomerId], 
[Project3].[CreatedOn2] AS [CreatedOn1]
FROM ( SELECT 
    [Limit2].[Id] AS [Id], 
    [Limit2].[CompanyId] AS [CompanyId], 
    [Limit2].[CompanyLocationId] AS [CompanyLocationId], 
    [Limit2].[FirstName] AS [FirstName], 
    [Limit2].[LastName] AS [LastName], 
    [Limit2].[Email] AS [Email], 
    [Limit2].[Address1] AS [Address1], 
    [Limit2].[Address2] AS [Address2], 
    [Limit2].[City] AS [City], 
    [Limit2].[State] AS [State], 
    [Limit2].[Country] AS [Country], 
    [Limit2].[Zip] AS [Zip], 
    [Limit2].[Phone] AS [Phone], 
    [Limit2].[SaleDate] AS [SaleDate], 
    [Limit2].[Notes] AS [Notes], 
    [Limit2].[Cost] AS [Cost], 
    [Limit2].[CreatedOn] AS [CreatedOn], 
    [Limit2].[ModifiedOn] AS [ModifiedOn], 
    [Limit2].[PrimaryReviewSiteId] AS [PrimaryReviewSiteId], 
    [Limit2].[Id1] AS [Id1], 
    [Limit2].[CreatedOn1] AS [CreatedOn1], 
    [Limit2].[Id2] AS [Id2], 
    [Limit2].[SubscriptionTypeId] AS [SubscriptionTypeId], 
    [Limit2].[PlanFeeAmount] AS [PlanFeeAmount], 
    [Limit2].[LoginId] AS [LoginId], 
    [Limit2].[IndustryTypeId] AS [IndustryTypeId], 
    [Limit2].[CompanyName] AS [CompanyName], 
    [Limit2].[NumberOfLocations] AS [NumberOfLocations], 
    [Limit2].[WebsiteUrl] AS [WebsiteUrl], 
    [Limit2].[blsActive] AS [blsActive], 
    [Limit2].[Pending] AS [Pending], 
    [Limit2].[ExpiryDate] AS [ExpiryDate], 
    [Limit2].[LogoImageName] AS [LogoImageName], 
    [Limit2].[C1] AS [C1], 
    [Extent4].[Id] AS [Id3], 
    [Extent4].[UploadTicketId] AS [UploadTicketId], 
    [Extent4].[UploadStatus] AS [UploadStatus], 
    [Extent4].[CompanyId] AS [CompanyId1], 
    [Extent4].[CompanyLocationId] AS [CompanyLocationId1], 
    [Extent4].[CustomerId] AS [CustomerId], 
    [Extent4].[CreatedOn] AS [CreatedOn2], 
    CASE WHEN ([Extent4].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
    FROM   (SELECT TOP (20) [Project2].[Id] AS [Id], [Project2].[CompanyId] AS [CompanyId], [Project2].[CompanyLocationId] AS [CompanyLocationId], [Project2].[FirstName] AS [FirstName], [Project2].[LastName] AS [LastName], [Project2].[Email] AS [Email], [Project2].[Address1] AS [Address1], [Project2].[Address2] AS [Address2], [Project2].[City] AS [City], [Project2].[State] AS [State], [Project2].[Country] AS [Country], [Project2].[Zip] AS [Zip], [Project2].[Phone] AS [Phone], [Project2].[SaleDate] AS [SaleDate], [Project2].[Notes] AS [Notes], [Project2].[Cost] AS [Cost], [Project2].[CreatedOn] AS [CreatedOn], [Project2].[ModifiedOn] AS [ModifiedOn], [Project2].[PrimaryReviewSiteId] AS [PrimaryReviewSiteId], [Project2].[Id1] AS [Id1], [Project2].[CreatedOn1] AS [CreatedOn1], [Project2].[Id2] AS [Id2], [Project2].[SubscriptionTypeId] AS [SubscriptionTypeId], [Project2].[PlanFeeAmount] AS [PlanFeeAmount], [Project2].[LoginId] AS [LoginId], [Project2].[IndustryTypeId] AS [IndustryTypeId], [Project2].[CompanyName] AS [CompanyName], [Project2].[NumberOfLocations] AS [NumberOfLocations], [Project2].[WebsiteUrl] AS [WebsiteUrl], [Project2].[blsActive] AS [blsActive], [Project2].[Pending] AS [Pending], [Project2].[ExpiryDate] AS [ExpiryDate], [Project2].[LogoImageName] AS [LogoImageName], [Project2].[C1] AS [C1]
        FROM ( SELECT [Project2].[Id] AS [Id], [Project2].[CompanyId] AS [CompanyId], [Project2].[CompanyLocationId] AS [CompanyLocationId], [Project2].[FirstName] AS [FirstName], [Project2].[LastName] AS [LastName], [Project2].[Email] AS [Email], [Project2].[Address1] AS [Address1], [Project2].[Address2] AS [Address2], [Project2].[City] AS [City], [Project2].[State] AS [State], [Project2].[Country] AS [Country], [Project2].[Zip] AS [Zip], [Project2].[Phone] AS [Phone], [Project2].[SaleDate] AS [SaleDate], [Project2].[Notes] AS [Notes], [Project2].[Cost] AS [Cost], [Project2].[CreatedOn] AS [CreatedOn], [Project2].[ModifiedOn] AS [ModifiedOn], [Project2].[PrimaryReviewSiteId] AS [PrimaryReviewSiteId], [Project2].[Id1] AS [Id1], [Project2].[CreatedOn1] AS [CreatedOn1], [Project2].[Id2] AS [Id2], [Project2].[SubscriptionTypeId] AS [SubscriptionTypeId], [Project2].[PlanFeeAmount] AS [PlanFeeAmount], [Project2].[LoginId] AS [LoginId], [Project2].[IndustryTypeId] AS [IndustryTypeId], [Project2].[CompanyName] AS [CompanyName], [Project2].[NumberOfLocations] AS [NumberOfLocations], [Project2].[WebsiteUrl] AS [WebsiteUrl], [Project2].[blsActive] AS [blsActive], [Project2].[Pending] AS [Pending], [Project2].[ExpiryDate] AS [ExpiryDate], [Project2].[LogoImageName] AS [LogoImageName], [Project2].[C1] AS [C1], row_number() OVER (ORDER BY [Project2].[CreatedOn1] ASC) AS [row_number]
            FROM ( SELECT 
                [Filter1].[Id] AS [Id], 
                [Filter1].[CompanyId] AS [CompanyId], 
                [Filter1].[CompanyLocationId] AS [CompanyLocationId], 
                [Filter1].[FirstName] AS [FirstName], 
                [Filter1].[LastName] AS [LastName], 
                [Filter1].[Email] AS [Email], 
                [Filter1].[Address1] AS [Address1], 
                [Filter1].[Address2] AS [Address2], 
                [Filter1].[City] AS [City], 
                [Filter1].[State] AS [State], 
                [Filter1].[Country] AS [Country], 
                [Filter1].[Zip] AS [Zip], 
                [Filter1].[Phone] AS [Phone], 
                [Filter1].[SaleDate] AS [SaleDate], 
                [Filter1].[Notes] AS [Notes], 
                [Filter1].[Cost] AS [Cost], 
                [Filter1].[CreatedOn] AS [CreatedOn], 
                [Filter1].[ModifiedOn] AS [ModifiedOn], 
                [Filter1].[PrimaryReviewSiteId] AS [PrimaryReviewSiteId], 
                [Limit1].[Id] AS [Id1], 
                [Limit1].[CreatedOn] AS [CreatedOn1], 
                [Extent3].[Id] AS [Id2], 
                [Extent3].[SubscriptionTypeId] AS [SubscriptionTypeId], 
                [Extent3].[PlanFeeAmount] AS [PlanFeeAmount], 
                [Extent3].[LoginId] AS [LoginId], 
                [Extent3].[IndustryTypeId] AS [IndustryTypeId], 
                [Extent3].[CompanyName] AS [CompanyName], 
                [Extent3].[NumberOfLocations] AS [NumberOfLocations], 
                [Extent3].[WebsiteUrl] AS [WebsiteUrl], 
                [Extent3].[blsActive] AS [blsActive], 
                [Extent3].[Pending] AS [Pending], 
                [Extent3].[ExpiryDate] AS [ExpiryDate], 
                [Extent3].[LogoImageName] AS [LogoImageName], 
                1 AS [C1]
                FROM    (SELECT [Extent1].[Id] AS [Id], [Extent1].[CompanyId] AS [CompanyId], [Extent1].[CompanyLocationId] AS [CompanyLocationId], [Extent1].[FirstName] AS [FirstName], [Extent1].[LastName] AS [LastName], [Extent1].[Email] AS [Email], [Extent1].[Address1] AS [Address1], [Extent1].[Address2] AS [Address2], [Extent1].[City] AS [City], [Extent1].[State] AS [State], [Extent1].[Country] AS [Country], [Extent1].[Zip] AS [Zip], [Extent1].[Phone] AS [Phone], [Extent1].[SaleDate] AS [SaleDate], [Extent1].[Notes] AS [Notes], [Extent1].[Cost] AS [Cost], [Extent1].[CreatedOn] AS [CreatedOn], [Extent1].[ModifiedOn] AS [ModifiedOn], [Extent1].[PrimaryReviewSiteId] AS [PrimaryReviewSiteId]
                    FROM [dbo].[Customer] AS [Extent1]
                    WHERE (([Extent1].[FirstName] LIKE @p__linq__0 ESCAPE N'~') OR ([Extent1].[LastName] LIKE @p__linq__1 ESCAPE N'~') OR ([Extent1].[Email] LIKE @p__linq__2 ESCAPE N'~') OR ([Extent1].[FirstName] + N' ' + [Extent1].[LastName] LIKE @p__linq__3 ESCAPE N'~')) AND ([Extent1].[CompanyId] = @p__linq__4) AND ([Extent1].[CompanyLocationId] = @p__linq__5) ) AS [Filter1]
                OUTER APPLY  (SELECT TOP (1) 
                    [Extent2].[Id] AS [Id], 
                    [Extent2].[CreatedOn] AS [CreatedOn]
                    FROM [dbo].[EmailCampaignerStatus] AS [Extent2]
                    WHERE [Filter1].[Id] = [Extent2].[CustomerId] ) AS [Limit1]
                LEFT OUTER JOIN [dbo].[Company] AS [Extent3] ON [Filter1].[CompanyId] = [Extent3].[Id]
            )  AS [Project2]
        )  AS [Project2]
        WHERE [Project2].[row_number] > 0
        ORDER BY [Project2].[CreatedOn1] ASC ) AS [Limit2]
    LEFT OUTER JOIN [dbo].[EmailCampaignerStatus] AS [Extent4] ON [Limit2].[CompanyId] = [Extent4].[CompanyId]
)  AS [Project3]
ORDER BY [Project3].[CreatedOn1] ASC, [Project3].[Id1] ASC, [Project3].[Id] ASC, [Project3].[Id2] ASC, [Project3].[C2] ASC

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[Customer] AS [Extent1]
    WHERE (([Extent1].[FirstName] LIKE @p__linq__0 ESCAPE N'~') OR ([Extent1].[LastName] LIKE @p__linq__1 ESCAPE N'~') OR ([Extent1].[Email] LIKE @p__linq__2 ESCAPE N'~') OR ([Extent1].[FirstName] + N' ' + [Extent1].[LastName] LIKE @p__linq__3 ESCAPE N'~')) AND ([Extent1].[CompanyId] = @p__linq__4) AND ([Extent1].[CompanyLocationId] = @p__linq__5)
)  AS [GroupBy1]


SELECT 
CASE WHEN ( EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[CompanyReviewSites] AS [Extent1]
    WHERE [Extent1].[CompanyLocationId] = @p__linq__0
)) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[CompanyReviewSites] AS [Extent2]
    WHERE [Extent2].[CompanyLocationId] = @p__linq__0
)) THEN cast(0 as bit) END AS [C1]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]

SELECT 
1 AS [C1], 
[Extent1].[Id] AS [Id], 
[Extent1].[RiviewSiteId] AS [RiviewSiteId], 
[Extent1].[URL] AS [URL], 
[Extent1].[CompanyId] AS [CompanyId], 
[Extent1].[CompanyLocationId] AS [CompanyLocationId], 
[Extent2].[Id] AS [Id1], 
[Extent2].[ReviewSiteName] AS [ReviewSiteName], 
[Extent2].[ImageName] AS [ImageName]
FROM  [dbo].[CompanyReviewSites] AS [Extent1]
INNER JOIN [dbo].[ReviewSites] AS [Extent2] ON [Extent1].[RiviewSiteId] = [Extent2].[Id]
WHERE [Extent1].[CompanyLocationId] = @p__linq__0

Answer 1

When I encountered a similar problem; our server was virtually indexing every single query after. Also after large amounts of usage from the entire company it randomly had a similar issue. I solved it by:

Disposing of temporary objects / Wrapped in a using statement. This allowed me to keep any objects, queries, or any data stored in memory to get dumped.

Another issue; was one of our developers kept opening / closing the connection throughout the application. I tried to create the usage; open it once and then do all the desired task at once. It may force some redesign; but it keeps it more robust. Even not opening / closing in each individual class; but when your ready submit it and then add those desired classes while the class is open may help also.

The next dilemma, is verify all the cached items get cleared. So your not constantly storing it.

The other item was to change our Windows Virtual machine index.

An example for Dispose / Using: http://msdn.microsoft.com/en-us/library/fs2xkftw.aspx

public void SqlTransactionHere()
{
       SqlTransaction tran = myConnection.BeginTransaction();
       tran.Dispose();
}

An example to help maintain the connection; then do our calls we utilized a pattern similar to:

http://msdn.microsoft.com/en-us/magazine/cc947917.aspx

Some tweaks we did too our server; as our server started paging and storing too much data on memory which bogged down as well.

You can monitor your server; by doing these steps:

1. Windows Key + R (Run)
2. Powershell

3. Then type this:

   strComputer = "-" Set objWmiService = GetObject("winmgmts:\" & strComputer & "\root\cimv2") Set colPageFiles = objWMIService.ExecQuery("Select * from Win32_PageFileSetting")

   For Each objPageFile in colPageFiles objPageFile.InitialSize = 384 objPageFile.MaximumSize = 1152 objPageFile.Put_ Next

The above is for 256 mb ram; "Microsoft's Best". We actually put ours down to initial size of 50; and a maximum size of 384. Our server does flag an error at start stating we set our virtual memory too low. However; our servers performance did increase as well as our application. You can actually monitor it's memory usage / virtual memory usage to help calculate.

If you'd like to write an application too monitor and log for you; or want to use Microsoft's Built-In Controls: http://msdn.microsoft.com/en-us/library/system.diagnostics.process.virtualmemorysize64.aspx

1. Start
2. Administrative Tools (If not there, Control Panel --> Administrative Tools)
3. System Monitor
4. PageFile, and other components to monitor. %Usage then Add
5. Should start tracking for you.

You can also change it in the Registry:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management

You can also clear your cache. Which can be done by utilizing:

• DBCC DROPCLEANBUFFERS
• CHECKPOINT

They can help clear some cache related issues; restarting SQL Server or Windows accomplishes the same thing. Which is why it may fix your issue. But occurs again; if it's too long.

Here is a great article for SQL Performance Scripts:

http://sqlcat.com/sqlcat/b/toolbox/archive/2008/02/21/scripts-and-tools-for-performance-tuning-and-troubleshooting-sql-server-2005.aspx

This article is a nice one to help troubleshoot or find performance issues: http://support.microsoft.com/kb/298475

Or if you don't feel like doing any of that, since it is working but does throw an exception after several days of use and bulk usage. You can simply create a powershell script; with:

Script: "Set on a Time-Schedule" Restart-Computer -ComputerName localhost

Then just go to your Registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon

1. Double Click DefaultUserName
2. Input Server Login
3. Double Click DefaultPassword
4. Put your password in.
5. Change AutoAdminLogin value to 1

You can edit, or add the string value as well. If it isn't present.

Hopefully those help you out; those were some things we had to do in order to fix our issue. You may only have to do one, or none. But I do hope that helps or gives the community some ideas to go off of to help better solve your problem.

But to me it sounds like your servers memory is being bogged down, too much data and it isn't able to parse or run commands as the machine is bogged down. I'm assuming your using Windows Server, if not those Powershell script commands are useless.

Answer 2

There could be many causes for this:

• A lock from a failed transaction
• An indexing issue
• If it's a shared server, then a competion for resource from another application
• Backup not clearing the transaction log
• Related disk space problems

Are you able to get the SQL server logs, view the activity monitor or run the SQL profiler. From my experience problems like this are very hard to track down so can only wish you good luck

Answer 3

In case someone else comes here looking for the answer for this issue, I had the same exact problem and my issue was that SQL Server was caching results.

I had to add OPTION (RECOMPILE) to the end of my SQL query.

I asked the same question and the explanation can be found in the answer to my question here: EntityCommandExecutionException Timeout Expired Only Sometimes

Answer 4

I think it's a matter of indexing and refreshing all the indexed temp data.. this is called statistical data, when SQL server collects all posible variations for cacheing, so then it uses them as cash for indexing services. so updating statistical data in SQL database might help here

DECLARE @SQL VARCHAR(1000)  
DECLARE @DB sysname  

DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR  
   SELECT [name]  
   FROM master..sysdatabases 
   WHERE [name] NOT IN ('model', 'tempdb') 
   ORDER BY [name] 

OPEN curDB  
FETCH NEXT FROM curDB INTO @DB  
WHILE @@FETCH_STATUS = 0  
   BEGIN  
       SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'EXEC sp_updatestats' + CHAR(13)  
       PRINT @SQL  
       FETCH NEXT FROM curDB INTO @DB  
   END  

CLOSE curDB  
DEALLOCATE curDB

then execute that query...

Answer 5

A few things spring to mind. Have you ran load tests? It might work fine in isolation both on your machine and the server but after a few days with a couple of thousand hits then it might be different matter. This seems to be the behaviour you've mentioned.

Perhaps the first query has only 200 rows, but that same query on day three has to search two million, that would slow things up a bit.

As others have suggested. Have a look at improving the indexes on your db tables (if that's an option).

Is it possible to re-write your Linq query. Perhaps the way it is written is preventing SQL from cacheing the query execution plan in RAM.

Is the server itself being backed up? I've had instances, not with cloud-based servers, when the hosting firm has affected jobs by taking snapshots of the Virtual Machines our dbases are on.