Timeout settings in SQL Server

Part 1ArticleWe have summarized the timeout settings in Sharepoint. The article says that SQL-side options will not have any impact. Why?

I was dizzy, so I went to consult Peter, a Senior Technical Support Engineer of SQL and got the following answer.

1. SQL-related timeouts are initiated by the client.

For example, we write a C # smallProgramSqlcommand is used. commandtimeout attribute, specifying its value as 20 seconds. then, after the query is executed on the SQL side for 20 seconds, our C # applet will send a TDS tension packet to SQL Server, telling SQL server that I have timed out, you do not need to perform the query. therefore, the request of the corresponding SQL client breaks down the connection. the client reports an exception, indicating that the SQL server has been running for too long and exceeds our original time limit.

 

2. What are the two timeout options in SQL Server Management studio?

• A. Tools-> options-> query exection-> execution time-out.

• B. Right click SQL Server Node-> properties-> connections-> remote query timeout.

 

If we regard management studio as a self-written C # program, in this program, we only write down the statements to be executed and set timeout? The value specified by option A here is sqlcommand. commandtimeout. You can understand it. ^_^

 

Assume that our C # applet connects to SQL Server 1 and runs the stored procedure to fetch data. In this stored procedure, SQL Server 1 needs to fetch the raw data from SQL Server 2. if the query on SQL Server 2 is executed after 600 seconds (default value), SQL Server 1 will send it to SQL Server 2, telling it that this query is too long for me, you don't want to do it. so SQL Server 1 sends SQL Server 2 A data packet and tells it to stop. server 2 then breaks the connection between them.

 

It can be seen that in general, option B is not closely related to us.

 

When I studied these two options, I found that some netizens on stackoverflow.com asked related questions. The people who answered the questions often gave these two options. this is actually wrong. after the adjustment, the SQL running timeout issue will not be improved.

 

I would like to thank Peter Zhu for his wonderful explanation. I will share it with you here.

 

There is no end to technology. There are so many tricks behind such a small option.