有时候，在Linq到EF的时候会有超时过期的异常，在服务器重新启动之后，它会正常工作

I have hosted my website at rackspace cloud server.

我在rackspace云服务器上托管了我的网站。

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

下面提到了我的Linq查询

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).

这个查询的执行时间在我的本地机器上甚至不到1秒，在重新启动后在服务器端也是一样的，但是执行时间每天都在增加，并且它在几天后无法执行(有时会失败，有时会产生结果)。

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.

我观察到它花了31,05sec然后失败了，它花了51.09 sec然后失败了。有时需要超过2分钟的时间才能产生结果。

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

我和rackspace的人谈过，但他们无法找到同样的原因。

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

编辑1:我在SQL分析器中看到了针对我的分页查询的查询

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

5 个解决方案

#1

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

是否能够获取SQL服务器日志、查看活动监视器或运行SQL分析器。从我的经验来看，像这样的问题很难找到，所以只能祝你好运

#2

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.

另一项是更改Windows虚拟机索引。

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

处理/使用的示例: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

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. Windows按键+ R(运行)
3. Powershell
4. Powershell

5. Then type this:

   然后输入:

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

   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

   对于colPageFiles中的每个objPageFile。InitialSize = 384 objPageFile。MaximumSize = 1152 objPageFile。Put_下

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.

以上为256mb ram;“微软最好的”。我们把最初的尺寸设为50;最大尺寸是384。我们的服务器在开始时确实标记了一个错误，表明我们把虚拟内存设置得太低了。然而;我们的服务器性能确实提高了，我们的应用程序也增加了。实际上，您可以监视它的内存使用/虚拟内存使用来帮助计算。

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

如果您也想编写应用程序，请为您监视和记录;或者想使用微软的内置控件:http://msdn.microsoft.com/en-us/library/system.diagnostics.process.virtualmemorysize64.aspx

1. Start
2. 开始
3. Administrative Tools (If not there, Control Panel --> Administrative Tools)
4. 管理工具(如果没有的话，控制面板——>管理工具)
5. System Monitor
6. 系统监控
7. PageFile, and other components to monitor. %Usage then Add
8. PageFile和其他要监视的组件。%使用然后添加
9. Should start tracking for you.
10. 应该开始追踪你。

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
• 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.

它们可以帮助清除一些与缓存相关的问题;重新启动SQL Server或Windows可以完成相同的任务。这就是为什么它可以解决你的问题。但再次发生;如果它太长了。

Here is a great article for SQL Performance Scripts:

这里有一篇关于SQL性能脚本的好文章:

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

http://sqlcat.com/sqlcat/b/toolbox/archive/2008/02/21/scripts -和-工具- -性能调优和故障排除- sql - server - 2005. - aspx

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

本文是一篇很好的文章，可以帮助您排除故障或发现性能问题: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:

或者如果您不想做这些事情，因为它正在工作，但是在几天的使用和大量使用之后会抛出异常。您可以简单地创建一个powershell脚本;:

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

脚本:“设置一个时间表”重新启动计算机-计算机名localhost

Then just go to your Registry:

然后去你的登记处:

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

1. Double Click DefaultUserName
2. 双击DefaultUserName
3. Input Server Login
4. 输入服务器登录
5. Double Click DefaultPassword
6. 双击DefaultPassword
7. Put your password in.
8. 把你的密码。
9. Change AutoAdminLogin value to 1
10. 将AutoAdminLogin值更改为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.

但在我看来，您的服务器内存似乎陷入了困境，数据太多，当机器陷入困境时，它无法解析或运行命令。我假设您使用的是Windows服务器，如果不是这些Powershell脚本命令的话。

#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.

如果有人来这里寻找这个问题的答案，我有同样的问题，我的问题是SQL Server缓存结果。

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

我必须在SQL查询的末尾添加选项(重新编译)。

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

我问了同样的问题，可以在我的问题的答案中找到解释:EntityCommandExecutionException超时有时只会过期

#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

我认为这是一个索引和刷新所有索引的临时数据的问题。这就是所谓的统计数据，当SQL server收集所有可用于缓存的posible变体时，它将它们用作索引服务的现金。因此，在SQL数据库中更新统计数据可能会有所帮助

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...

然后执行查询…

#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.

第一个查询可能只有200行，但是第三天的同一个查询必须搜索200万行，这会使事情慢一些。

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

当别人建议。看看如何改进db表上的索引(如果可以的话)。

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.

是否有可能重写Linq查询。编写它的方式可能是防止SQL在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.

服务器本身是否被备份?我有过这样的例子，不是基于云的服务器，当托管公司通过快照我们的dbase所在的虚拟机来影响工作时。