排除超时 SqlExceptions

2022-01-23 00:00:00 sql-server sqlexception ado.net

我有一些奇怪的行为，我无法弄清楚为什么会发生.我看到间歇性超时异常.我很确定它与体积有关，因为它在我们的开发环境中不可重现.作为一个创可贴的解决方案，我尝试将 sql 命令超时时间提高到 60 秒，但正如我发现的那样，这似乎没有帮助.这是奇怪的部分，当我检查失败的进程的日志时，这里是开始和结束时间:

I have some curious behavior that I'm having trouble figuring out why is occurring. I'm seeing intermittent timeout exceptions. I'm pretty sure it's related to volume because it's not reproducible in our development environment. As a bandaid solution, I tried upping the sql command timeout to sixty seconds, but as I've found, this doesn't seem to help. Here's the strange part, when I check my logs on the process that is failing, here are the start and end times:

  • 2008 年 9 月 16 日 16:21:49
  • 2008 年 9 月 16 日 16:22:19

那我把命令超时设置为六十秒，怎么可能三十秒就超时了??

So how could it be that it's timing out in thirty seconds when I've set the command timeout to sixty??

仅供参考，抛出的异常如下:

Just for reference, here's the exception being thrown:

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   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)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   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.ExecuteReader()
   at SetClear.DataAccess.SqlHelper.ExecuteReader(CommandType commandType, String commandText, SqlParameter[] commandArgs)

推荐答案

SQL 命令超时，因为您正在使用的查询需要更长的时间才能执行.在 Query Analyzer 或 Management Studio 中执行它，在数据库中有代表性的数据量，然后查看执行计划以找出慢的原因.

SQL commands time out because the query you're using takes longer than that to execute. Execute it in Query Analyzer or Management Studio, with a representative amount of data in the database, and look at the execution plan to find out what's slow.

如果某项操作占用了大量时间并被描述为表扫描"或聚集索引扫描"，请查看是否可以创建一个索引，将该操作转换为键查找(索引查找或聚集索引查找).

If something is taking a large percentage of the time and is described as a 'table scan' or 'clustered index scan', look at whether you can create an index that would turn that operation into a key lookup (an index seek or clustered index seek).

相关文章