Identifying Timeout Causes Using SQL Server Profiler
We are seeing random timeouts in a SQL Server application (one ASP.Net and one WinForms). I had SQL Profiler running for an hour to see what might be causing the problem. Then I highlighted the time when the timeouts occurred.
There are a large number of Reads, but there is not much difference in reads when timeout errors occur and when not. There are practically no entries during this period (primarily because everyone gets timeouts and cannot write).
Example: Waiting time 11:37. On average, 1,500 transactions per minute exceed the timeout, with around 5,709,219 views.
It looks like there are a lot of exceptions between timeouts (ten minutes), just like there are transactions per minute. The reads increase slightly to timeout (jumping to over 6005708), but during the no-timeout period they reach 8251468. Timeouts occur in both applications.
The big problem here is that it just started last week and the app has been around for a few years now. So yes, Profiler gave us a lot of data to work with, but the current issue is timeouts.
Is there something else I should be looking for in Profiler, or should I go to Performance Monitor (or another tool) on the server?
One possible culprit could be the size of the database. The database is quite large (> 200GB), but the AutoGrow is set to 1MB. Could it be that SQL Server is resizing and that the transaction is not showing up in the profiler?
Many thanks
source to share
Thanks to the help here, I was able to identify a few bottlenecks, but I wanted to describe my process to help someone get through this.
-
Issue # 1 was found to be a large number of LOCK_MK_S records found from SQLDiag and other tools.
-
Run Profiler Profiler for two different time periods. Comparing the durations for similar methods led me to conclude that some UPDATE calls always took the same amount of time, more than 10 seconds.
Further investigation revealed that these updated UPDATEs were updating the table with a trigger that was taking too long. Since the trigger could lock the table during its completion, every other query was affected. (See the comments section - I incorrectly pointed out that a trigger always locks the table - in our case, the trigger was preventing the lock from being released)
Watch for triggers for major updates.
source to share