问题描述
我有一个导入 EF4 的存储过程，当我在 30 秒后使用某些参数调用它时，它会引发超时错误.在 SQL Server 探查器中，我可以看到带有正确参数的存储过程调用仅用了 30 多秒，这是我的应用程序的超时时间.
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.
但是，当我在查询分析器中执行发送到探查器的相同 SQL 时，它会在亚秒内执行.什么可能导致从 EF 调用和从 SQL Server Management Studio 调用之间出现这种差异?
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?
.NET 错误的完整堆栈跟踪如下.
Full stack trace of .NET error bellow.
[SqlException (0x80131904): 超时.超时时间在操作完成之前已经过去，或者服务器没有回应.]
System.Data.SqlClient.SqlConnection.OnError(SqlException 异常，布尔中断连接)+2073486
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException异常，布尔型 breakConnection) +5064444
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior，SqlCommand cmdHandler、SqlDataReader 数据流、BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObjectstateObj) +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(CommandBehaviorcmdBehavior、RunBehavior runBehavior、布尔 returnStream、布尔异步)+987
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehaviorcmdBehavior、RunBehavior、runBehavior、布尔返回流、字符串方法，DbAsyncResult 结果)+162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehaviorcmdBehavior、RunBehavior、runBehavior、布尔返回流、字符串方法)+32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior行为，字符串方法)+141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior行为)+12
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior 行为)+10 System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommandentityCommand，CommandBehavior 行为)+443
[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
[EntityCommandExecutionException: 执行时出错命令定义.有关详细信息，请参阅内部异常.]
System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommandentityCommand，CommandBehavior 行为)+479
System.Data.Objects.ObjectContext.CreateFunctionObjectResult(EntityCommandentityCommand、EntitySet entitySet、EdmType edmType、MergeOption合并选项)+182
System.Data.Objects.ObjectContext.ExecuteFunction(字符串函数名，MergeOption mergeOption, ObjectParameter[] 参数)+218
System.Data.Objects.ObjectContext.ExecuteFunction(字符串函数名，ObjectParameter[] 参数)+53
MetaView.DAL.MFCMData.MFCMDATAEntities.GetTradingOpenPositionCounterParty(Nullable1positionDT, Nullable1 tradingAccountID) inC:ProjectsCASHwebMetaViewMetaView.DAL.MFCMDataMFCMData.Designer.cs:7064MetaView.BusinessLayer.Shared.Accounts.CounterParties.GetCounterParties(Int32tradingAccountID) 在C:ProjectsCASHwebMetaViewMetaView.BusinessLayerSharedAccountsCounterParties.cs:161
[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:ProjectsCASHwebMetaViewMetaView.DAL.MFCMDataMFCMData.Designer.cs:7064
MetaView.BusinessLayer.Shared.Accounts.CounterParties.GetCounterParties(Int32
tradingAccountID) in
C:ProjectsCASHwebMetaViewMetaView.BusinessLayerSharedAccountsCounterParties.cs:161
推荐答案
所以几周前我遇到了一个类似的问题，我们的一位 DBA 向我解释了这个问题(当然是转述并简化为我的水平):
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):
当调用 SQL Server 存储过程时，服务器会为每个存储过程每个 object_id 创建并缓存一个执行计划.有时，SQL Server 可能会根据传入的参数值创建错误的执行计划(在我们的例子中，可空参数为空).发生这种情况时，快速解决方法是在 SQL Server Management Studio(或您可能使用的任何数据库管理工具)中运行 sp_recompile 'Schema.Procedure'.所有这些都是清除该存储过程的计划缓存.如果 proc 的下一个被调用者再次传入bad"参数值，您将陷入同样的情况所以真正的解决方法是使用 OPTIMIZE FOR 给查询一个提示 语法(参见 http://msdn.microsoft.com/en-gb/library/ms181714.aspx).
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).
简而言之，如果您在 WHERE 和/或 WHERE 之后添加 OPTION (OPTIMIZE FOR (@myParameter = 'Some value that give you a GOOD execution plan'))code>ORDER BY 子句应该可以解决问题.
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.
另外，如果您想知道为什么在 SSMS 中执行 same SQL 时总是能快速获得结果，这是因为 SSMS 有一个名为 ARITHABORT 的选项的默认设置为 ON(SET ARITHABORTON)，所有其他应用程序都默认设置为 OFF，但它的工作原理及其影响超出了我的经验，我没有费心去阅读它.我被告知我不应该使用它.我确信真正的 DBA 可以更好地解释原因.
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.
这篇关于Entity Framework 4 存储过程调用超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！