SQL SERVER超时错误

wsjz_01

　　Some errors when encountered take most of us for a spin. In this category the error>　　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:

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