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.
Best Answer
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.