We are getting the following exception in an application:
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
So, I looked at the query and sure enough it was taking a really long time. So I tuned it up to take less than a second.
But I am still getting timeouts.
So I set up a Extended Events session for the Attention event to catch the timeouts and see what what going on (using this page as a guide).
The results were confusing. I see my queries in the list, but the duration logged in the trace is under a second!
Here are some example values I am getting in duration column for the timeout:
timeout |
---|
539 |
539 |
474 |
423 |
505 |
904 |
I am confused. Why am I getting an SQL Server timeout error, when the duration shows an average of a half a second?
NOTE: The output from the extended event shows some rows that are 30+ seconds. These are legit timeouts to my view and I am working on those. (I just point this out to show that the Attention extended event seems to be working for those queries.)
I thought it might be blocking, so I setup a Blocked Process Report extended event to see (following this guide). While there are blocks happening on the server, they are not on the databases that my query is running against.
The application is using a API called Dapper (a fairly popular Micro ORM that I hear is used to run Stack Exchange). I imagine it has decent connection management in it. But even if they misused its connections, as I understand it, that leads to resource leaks, not SQL timeouts.
The timeout of the query in the application is set to 120 seconds. That should be plenty of time to get most of the queries that I am seeing reported as timeouts.