1

We're using a .Net 4.5 web application with SQL Server 2012 on the same machine. We will sporadically see a cluster of "Wait operation timed out" exceptions while using the site. Occasionally we will receive Deadlock exceptions as well. These are always on Select queries, not updates.

I have run sp_lock a few times and noticed there are sometimes as many as 4500 locks acquired by a single process. These are selects with multiple joins. Even though they are shared locks, could that be the source of our problems? Could adding nolock to these queries reduce blocking and timeouts and possible deadlocks? If that's not the right answer, is there a way to know what queries were being run when the timeouts occurred? I have not been able to catch it in action as it usually only lasts a few seconds before the database returns to normal funciton.

  • 1
    Did you check for blocking ? install sp_whoisactive and collect some diagnostic information . – Kin Shah Dec 31 '15 at 15:33
  • Well, you can look at things like sys.dm_exec_requests during these events, and see what the queries are waiting on. But outside of SQL Server and as a proactive measure I'd be looking at the network. (1) you can run continuous/frequent tests with iPerf (see this article) and check if any anomalies coincide with your app blips (2) you could make a copy of the web application and run it closer to the SQL Server (over fewer network hops or, temporarily, on the server itself) to see if you still have issues. – Aaron Bertrand Dec 31 '15 at 15:33
  • @AaronBertrand For reasons that are a constant annoyance to me, the database is running on the webserver, so network shouldn't be an issue. Any way to tell what was going on after the fact? The event itself is generally so short-lived that by the time I can look at d_exec_reuquests or sp_whoisactive, it's all over. – blackorchid Dec 31 '15 at 19:38
  • 1
    Look in the default extended events session for the deadlock graph dba.stackexchange.com/a/10646/3690 – Martin Smith Dec 31 '15 at 23:16
0

When the timeouts occur, do you see any blocking? You can run sp_who2 to determine that. It sounds like you are having intermittent performance issues. You need to look at your wait types to see if there is a bottleneck. Activity Monitor will show you the top resource waits. The waits will help you determine where to focus your attention. Check the SQL Errorlog, System & Application event log for any dumps or entries that provide clues into what is happening.

  • @stacylaray could you expound on what you mean by "look at your wait types?" We did discover that some of our exceptions are actually deadlock exceptions in SQL Server, though it's always on selects and not updates (and I wouldn't think shared locks would cause deadlocks, but I am a novice?). – blackorchid Dec 31 '15 at 21:11
0

I discovered the underlying issue by running sp_whoisactive repeatedly until the database hung again, at which point it became obvious that all the threads were waiting on "PREEMPTIVE_OS_WRITEFILEGATHER." It turns out that our transaction log had grown out of control and was taking three minutes to auto-grow, so connections were timing out in the meantime.

I have also tried running a deadlock trace in SQL Profiler, but I haven't caught anything yet.

Thanks to everyone for the helpful suggestions!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.