SQL SERVER超时错误

Some errors when encountered take most of us for a spin. In this category the error related to “Timeout” surely falls. If you are a web developer and receive the same there are a hundred combinations why this can possibly happen. The web, results can sometimes lead us in completely opposite directions because we have not analyzed the root cause for this. I sincerely urge everyone when working with generic errors, look for specific symptoms and then use the trick of eliminating one after the other before a final solution can be arrived.

This is one of the most common error which you would hear from web developers. Here is a typical error raised shown on the website.

[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) +1948826   
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747    
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194    
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392    
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33    
System.Data.SqlClient.SqlDataReader.get_MetaData() +83    
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297    
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954    
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() +12    
System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) +332

It’s very clear message that the timeout duration specified for the operation is completed but actual work didn’t finish. The error can come because of two kind of timeouts:

 

1. Query Timeout: The default value for web application or .NET application is generally 30 seconds. If any request is in progress and it couldn’t complete within the timeout period, we would again see an error.

2. Connection Timeout: The default value of connection timeout is generally 15 seconds. Within this time if the connection can’t be made, we would see the error.

The next obvious question is – what should we do if we see such error?

Query timeout:The reasons a command/query runs longer than expected is commonly due to blocking or the need for query/index tuning or both.  A quick way to check for blocking to to run sp_who2 while the query is running.  The BlkBy column will show the SPID of the blocking connection if the query is blocked.  For slow running query, you may want to check the execution plan to verify that the statement is touching the rows which are needed.  For example, if your intent is to Select a single row but you see a scan operator, that is a strong indication that you need to perform index or query tuning or need to update statistics.

Here is the over-simplified repro of query timeout done via SQL Server Management Studio (SSMS). The default value of query timeout in SSMS in 0 (which is infinite) Tools > Options in menu would open below window where the value can be set, if needed.For demo purpose, I have changed it to 10 seconds. In one query window we would run below command

USE tempdb
GO
CREATE TABLE foo (i INT)
BEGIN TRAN
INSERT INTO foo VALUES (1)
GO

In second query windows, we can run below command

SELECT * FROM tempdb..foo

and we can see below after 10 seconds.

It is important to note the error number. It is not an SQL Server error but error by client. The query waited for 10 seconds because we caused blocking. If we don’t set timeout value in SSMS, they query would run forever because default timeout is zero.

Connection timeout: This I can’t reproduce by management studio query easily. On a slow network, you can easily reproduce it by reducing the connection timeout to a lower value. In SSMS. You need to click on Options on login page as shown below:

 

And change the value in the Connection Properties tab.

On the same screen we can also set the query timeout (shown as execution time-out)

If you are seeing the same errors in your web application, then you need to check a configuration file (normally known as web. config file). The same parameters (query and connection timeout) can be set in the connection string of the application as well.

转载自：http://blog.sqlauthority.com/2016/01/26/sql-server-timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation-or-the-server-is-not-responding/