I have a stored procedure imported into EF4, and when I call it with certain parameters after 30 seconds it throws a time out error. In SQL Server profiler I can see the stored procedure call with the proper parameters taking just over 30 seconds, which is the timeout on my application.
我有一个导入到EF4的存储过程,当我在30秒后用某些参数调用它时会抛出超时错误。在SQL Server探查器中,我可以看到存储过程调用使用适当的参数只需要超过30秒,这是我的应用程序的超时。
HOWEVER when I execute the same SQL sent to the profiler in Query Analyzer it executes sub-second. What could cause this discrepancy between being called from EF and being called from SQL Server Management Studio?
但是,当我执行在查询分析器中发送到探查器的相同SQL时,它会执行亚秒级。什么可能导致从EF调用和从SQL Server Management Studio调用之间的这种差异?
Full stack trace of .NET error bellow.
下面是.NET错误的完整堆栈跟踪。
[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) +2073486
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5064444
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
System.Data.SqlClient.SqlDataReader.get_MetaData() +86
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +311
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10 System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +443[SqlException(0x80131904):超时已过期。操作完成之前经过的超时时间或服务器没有响应。] System.Data.SqlClient.SqlConnection.OnError(SqlException exception,Boolean breakConnection)+2073486 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,Boolean breakConnection)+5064444 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()+234 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,SqlCommand cmdHandler,SqlDataReader dataStream,BulkCopySimpleResultSet bulkCopyHandler,TdsParserStateObject stateObj)+2275 System.Data.SqlClient。 SqlDataReader.ConsumeMetaData()+ 33 System.Data.SqlClient.SqlDataReader.get_MetaData()+86 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,RunBehavior runBehavior,String resetOptionsString)+311 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds (CommandBehavior cmdBehavior,RunBehavior runBehavior,Boolean returnStream,Boolean async)+987 System.Data.SqlClient.SqlCommand.RunExecuteReade r(CommandBehavior cmdBehavior,RunBehavior runBehavior,Boolean returnStream,String方法,DbAsyncResult结果)+162 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,RunBehavior runBehavior,Boolean returnStream,String method)+32 System.Data.SqlClient.SqlCommand .ExecuteReader(CommandBehavior behavior,String method)+141 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)+12 System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)+10 System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands (EntityCommand entityCommand,CommandBehavior behavior)+443
[EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.]
System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +479
System.Data.Objects.ObjectContext.CreateFunctionObjectResult(EntityCommand entityCommand, EntitySet entitySet, EdmType edmType, MergeOption mergeOption) +182
System.Data.Objects.ObjectContext.ExecuteFunction(String functionName, MergeOption mergeOption, ObjectParameter[] parameters) +218
System.Data.Objects.ObjectContext.ExecuteFunction(String functionName, ObjectParameter[] parameters) +53
MetaView.DAL.MFCMData.MFCMDATAEntities.GetTradingOpenPositionCounterParty(Nullable1 positionDT, Nullable1 tradingAccountID) in C:\Projects\CASH\web\MetaView\MetaView.DAL.MFCMData\MFCMData.Designer.cs:7064 MetaView.BusinessLayer.Shared.Accounts.CounterParties.GetCounterParties(Int32 tradingAccountID) in C:\Projects\CASH\web\MetaView\MetaView.BusinessLayer\Shared\Accounts\CounterParties.cs:161[EntityCommandExecutionException:执行命令定义时发生错误。有关详细信息,请参阅内部异常。] System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand,CommandBehavior behavior)+479 System.Data.Objects.ObjectContext.CreateFunctionObjectResult(EntityCommand entityCommand,EntitySet entitySet,EdmType edmType,MergeOption mergeOption)+182 System.Data.Objects.ObjectContext.ExecuteFunction(String functionName,MergeOption mergeOption,ObjectParameter [] parameters)+218 System.Data.Objects.ObjectContext.ExecuteFunction(String functionName,ObjectParameter [] parameters)+53 MetaView.DAL.MFCMData.MFCMDATAEntities .GetTradingOpenPositionCounterParty(Nullable1 positionDT,Nullable1 tradingAccountID)位于C:\ Projects \ CASH \ web \ MetaView \ MetaView.DAL.MFCMData \ MFCMData.Designer.cs:7064 MetaView.BusinessLayer.Shared.Accounts.CounterParties.GetCounterParties(Int32 tradingAccountID)in C:\项目\ CASH \网络\ MetaView \ MetaView.BusinessLayer \共享\帐户\ CounterParties.cs:161
1 个解决方案
#1
23
So I had a similar issue a couple of weeks ago, which was explained to me by one of our DBAs thusly (paraphrased and dumbed down to my level of course):
所以几个星期前我遇到了一个类似的问题,我们的一位DBA就这样解释了这个问题(当然我已经解释并愚弄了):
When a SQL Server stored procedure is called, the server creates and caches an execution plan per stored procedure per object_id. Sometimes a bad execution plan can be created by SQL Server depending on the parameter value that is passed in (in our case it was null for a nullable parameter). When this happens, the quick fix is to run sp_recompile 'Schema.Procedure' in SQL Server Management Studio (or whatever DB management tool you might be using). All this does is clear the plan cache for that stored procedure. If the next callee of the proc passes in the "bad" parameter value again you'll be stuck in the same situation so the real fix is to give the query a hint using the OPTIMIZE FOR syntax (see http://msdn.microsoft.com/en-gb/library/ms181714.aspx).
调用SQL Server存储过程时,服务器会根据object_id为每个存储过程创建并缓存执行计划。有时,SQL Server可以创建一个错误的执行计划,具体取决于传入的参数值(在我们的例子中,对于可为空的参数,它是null)。发生这种情况时,快速解决方法是在SQL Server Management Studio(或您可能使用的任何数据库管理工具)中运行sp_recompile'Desche.Procedure'。所有这一切都清楚了该存储过程的计划缓存。如果proc的下一个被调用者再次传入“bad”参数值,那么你将陷入相同的情况,所以真正的解决方法是使用OPTIMIZE FOR语法为查询提供一个提示(请参阅http://msdn.microsoft .COM / EN-GB /库/ ms181714.aspx)。
In short if you add OPTION (OPTIMIZE FOR (@myParameter = 'Some value that gives you a GOOD execution plan')) after your WHERE and/or ORDER BY clauses it should fix the issue.
简而言之,如果你在WHERE和/或ORDER BY子句之后添加OPTION(OPTIMIZE FOR(@myParameter ='某些给你一个GOOD执行计划的值)),它应该解决问题。
Also, if you're wondering why when executing the same SQL in SSMS you always get fast results it's because SSMS has a default setting of ON for an option called ARITHABORT (SET ARITHABORT ON) which all other apps have set to OFF by default, but how it works and its implications are beyond my experience and I haven't bothered to read up on it. I have been told that I shouldn't use it though. I'm sure a real DBA can do a better job of explaining why.
此外,如果您想知道为什么在SSMS中执行相同的SQL时总是会得到快速的结果,因为SSMS默认设置为ON,这个选项名为ARITHABORT(SET ARITHABORT ON),默认情况下所有其他应用都设置为OFF,但它是如何运作的,其影响超出了我的经验,我也没有费心去阅读它。我被告知我不应该使用它。我确信真正的DBA可以更好地解释原因。
#1
23
So I had a similar issue a couple of weeks ago, which was explained to me by one of our DBAs thusly (paraphrased and dumbed down to my level of course):
所以几个星期前我遇到了一个类似的问题,我们的一位DBA就这样解释了这个问题(当然我已经解释并愚弄了):
When a SQL Server stored procedure is called, the server creates and caches an execution plan per stored procedure per object_id. Sometimes a bad execution plan can be created by SQL Server depending on the parameter value that is passed in (in our case it was null for a nullable parameter). When this happens, the quick fix is to run sp_recompile 'Schema.Procedure' in SQL Server Management Studio (or whatever DB management tool you might be using). All this does is clear the plan cache for that stored procedure. If the next callee of the proc passes in the "bad" parameter value again you'll be stuck in the same situation so the real fix is to give the query a hint using the OPTIMIZE FOR syntax (see http://msdn.microsoft.com/en-gb/library/ms181714.aspx).
调用SQL Server存储过程时,服务器会根据object_id为每个存储过程创建并缓存执行计划。有时,SQL Server可以创建一个错误的执行计划,具体取决于传入的参数值(在我们的例子中,对于可为空的参数,它是null)。发生这种情况时,快速解决方法是在SQL Server Management Studio(或您可能使用的任何数据库管理工具)中运行sp_recompile'Desche.Procedure'。所有这一切都清楚了该存储过程的计划缓存。如果proc的下一个被调用者再次传入“bad”参数值,那么你将陷入相同的情况,所以真正的解决方法是使用OPTIMIZE FOR语法为查询提供一个提示(请参阅http://msdn.microsoft .COM / EN-GB /库/ ms181714.aspx)。
In short if you add OPTION (OPTIMIZE FOR (@myParameter = 'Some value that gives you a GOOD execution plan')) after your WHERE and/or ORDER BY clauses it should fix the issue.
简而言之,如果你在WHERE和/或ORDER BY子句之后添加OPTION(OPTIMIZE FOR(@myParameter ='某些给你一个GOOD执行计划的值)),它应该解决问题。
Also, if you're wondering why when executing the same SQL in SSMS you always get fast results it's because SSMS has a default setting of ON for an option called ARITHABORT (SET ARITHABORT ON) which all other apps have set to OFF by default, but how it works and its implications are beyond my experience and I haven't bothered to read up on it. I have been told that I shouldn't use it though. I'm sure a real DBA can do a better job of explaining why.
此外,如果您想知道为什么在SSMS中执行相同的SQL时总是会得到快速的结果,因为SSMS默认设置为ON,这个选项名为ARITHABORT(SET ARITHABORT ON),默认情况下所有其他应用都设置为OFF,但它是如何运作的,其影响超出了我的经验,我也没有费心去阅读它。我被告知我不应该使用它。我确信真正的DBA可以更好地解释原因。