SQL Server runs query by random timeout over .Net

Question

We have a program that connects to our database and runs some stored procedures to get some data.

The database is SQL Server 2008, and the program runs locally. The connection is through TCP / IP, but shared memory is enabled. And the connection string timeout is set to 45 seconds.

When we run them through SQL Server Management Studio, it takes 0-5 seconds to execute. When we run it through the code, it is randomly disabled.

To do some testing, we increased the timeout from 45 seconds to several minutes. In addition, to discard any locking problem, we verified that the stored procedures only “select” the data (without the insert or update instructions). And we tried several table modifiers for select statements, such as: nolock , readpast , ...

I also checked sp_who2 and dbcc opentran() , and nothing is blocked ... and SPID.Net is a running command ... For more information, while .Net is waiting for a DB response, through SQL Server Management Studio I can work with the same operator (stored procedure or Select) without any problems.

Any suggestion on what's going on?

Answer 1

The connection string timeout only affects the login timeout. It seems you will click on the command timeout, and this can only be changed by changing the CommandTimeout . The default value is 30 seconds, the recommended value is 0 (infinite timeout).

As for why your procedure gets into random slow execution, I recommend starting by reading Slow in the application, fast in SSMS? Understanding the Sacraments of Performance

By the way, your request is most likely not blocked. He is carrying out another plan that just takes so long to complete. Checking for last_wait_type in sys.dm_exec_requests will most likely trigger an IO wait (PAGEIOLATCH, after any CXPACKET with red gray scrolls down sys.dm_os_workers join ...). But it makes no sense to repeat the much more comprehensive and wonderful article by Erland Sommarskog, which I originally related.

Answer 2

There are two types of timeout: connection and request.

If your request expires, this is a problem with the request; but since you say you can run it through the SQL server management studio, I doubt this is a query.

If your connection time is running out, this is most likely a network problem. I see that you experimented (locks, hints, etc.), but you make the assumption that it is the request that causes this problem. Try thinking in terms of the web. I heard about activity monitors on the SQL server that can help you detect network problems during a connection.

Answer 3

if the timeout is with .net, then add these 2 parameters to the connection string.

Timeout = 3600000;

Maximum pool size = 360,000;