5

We are experiencing seemingly random timeouts on a two app (one ASP.Net and one WinForms) SQL Server application. I had SQL Profiler run during an hour block to see what might be causing the problem. I then isolated the times when the timeouts were occurring.

There are a large number of Reads but there is no large difference in the reads when the timeout errors occur and when they don't. There are virtually no writes during this period (primarily because everyone is getting time outs and can't write).

Example: Timeout occurs 11:37. There are an average of 1500 transactions a minute leading up to the timeout, with about 5709219 reads.

That seems high EXCEPT that during a period in between timeouts (over a ten minute span), there are just as many transactions per minute and the reads are just as high. The reads do spike a little before the timeout (jumping up to over 6005708) but during the non-timeout period, they go as high as 8251468. The timeouts are occurring in both applications.

The bigger problem here is that this only started occurring in the past week and the application has been up and running for several years. So yes, the Profiler has given us a lot of data to work with but the current issue is the timeouts.

Is there something else that I should be possibly looking for in the Profiler or should I move to Performance Monitor (or another tool) over on the server?

One possible culprit might be the Database Size. The database is fairly large (>200 GB) but the AutoGrow setting was set to 1MB. Could it be that SQL Server is resizing itself and that transaction doesn't show itself in the profiler?

Many thanks

Andrew MacNeill
  • 390
  • 3
  • 8
  • Whats the actual timeout error? Is it a SQL Server error? This link http://stackoverflow.com/questions/279073/how-to-find-timed-out-statements-in-sql-2005-profiler has some suggestions on how to identify the query causing the timeout – Nick.Mc Oct 29 '14 at 11:15
  • This article will teach you what to look at: [How to analyse SQL Server performance](http://rusanu.com/2014/02/24/how-to-analyse-sql-server-performance/) – Remus Rusanu Oct 29 '14 at 11:17
  • Nick, The timeouts are logged in our exception log as "Timeout period elapsed prior to completion of the operation". When looking in the logs at this time, we can see queries that take over 30 seconds- The identical queries take less than 3 seconds in other periods which is why I am thinking it is something else. – Andrew MacNeill Oct 29 '14 at 11:26
  • Remus, thanks - reading now. I will try some of these when the timeouts start again – Andrew MacNeill Oct 29 '14 at 11:29

1 Answers1

2

Thanks to the assistance here, I was able to identify a few bottlenecks but I wanted to outline my process to possibly help anyone going through this.

  1. The #1 problem was found to be a high number of LOCK_MK_S entries found from the SQLDiag and other tools.

  2. Run the Trace Profiler over two different periods of time. Comparing durations for similar methods led me to find that certain UPDATE calls were always taking the same amount of time, over 10 seconds.

Further investigation found that these UPDATE stored procs were updating a table with a trigger that was taking too much time. Since a trigger may lock the table while it completes, it was affecting every other query. ( See the comment section - I was incorrectly stating that the trigger would always lock the table - in our case, the trigger was preventing the lock from being released)

Watch the use of Triggers for doing major updates.

Andrew MacNeill
  • 390
  • 3
  • 8
  • Actually that is a fake answer. Triggers do NOT lock the table per se. I would suggest doing an in depth analysis regarding transaction isolation levels (which may result in the table being locked for the UPDATE, which then is kept up while the trigger runs) and the reson why the trigger takes so long... But no, triggers do NOT lock the table per se at all. False analysis. – TomTom Nov 06 '14 at 16:39
  • really Tom? I found a number of places that noted that. http://stackoverflow.com/questions/2606226/sql-server-2008-running-trigger-after-insert-update-locks-original-table I agree that the transaction isolation levels should be reviewed - I have updated the response accordingly. Thanks – Andrew MacNeill Nov 07 '14 at 12:24
  • Yes, really. Triggers are SQL and keep existing locks - but they will not in themselves issue a table lock. UNLESS something IN them does so OR unless it already is in place (which can be just the standard tx isolation level). As such "triggers lock the table" is wrong, if anything because being extremely simplistic and saying actually that a trigger issues an exclusive table lock, even if doing nothing. – TomTom Nov 07 '14 at 12:30