How can I narrow down the cause of a deadlock after the fact?

Question

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.

Answer 1

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.

Answer 2

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!