Although life would be much easier for every DBA if deadlocks never happened, we all realize that they do happen. There is a great deal of information out there on the causes of and solutions for deadlocks. The focus of this post is more about how often does SQL Server check to see if a deadlock is happening.
Prior to getting into how SQL Server detects deadlocks, let’s take a few minutes to quickly go over exactly what a deadlock it.
Microsoft states this on their website:
“A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock”
Because deadlocks happen when two task permanently block each other, without a deadlock, both process will simply block forever. Of course this could never be good in a production system. It is important that these situations be identified and dealt with in some manner. This is where SQL Server database engine steps in, it is frequently searching the lock manager looking for deadlocks.
Initially the interval the deadlock check takes place is every 5 seconds. This interval may decrease depending on if it finds a deadlock. If a deadlock is not found, the check will remain at every 5 seconds. As mentioned, 5 seconds is the default interval, it is also the maximum number of seconds between checks. If SQL Server finds deadlock, then the interval will be decreased and checks will happen more frequently. The minimum interval is 100 ms. Finally, if deadlocks were found at one point and they longer exist the interval will return to 5 seconds.
While there are many options that can be changed in SQL Server, the default frequency for deadlock checks is not among the list. This option is not configurable. These checks are very light weight and should not cause any issues on the system. If SQL Server is checking more frequently than 5 seconds, that means it is finding deadlocks and your time and energy should be focused on addressing that.
In addition to the interval for the check of deadlocks, there are a few other items related to deadlocks that I feel are important to know as a DBA. The most obvious is what are some of the causes of deadlocks. If you Google what causes deadlocks you will find a ton of information. I also feel that it is important to know how to get information about the deadlock as well as how SQL Server determines which process well be the victim. I have a blog post on one of the methods SQL Server uses to determine to victim, it can be found here. This post is about the DeadLock_priority option. SQL Server will also use the cost to rollback as one of the criteria to determine of the victim.
A logical question would be how do I capture information about deadlocks. There are a number of ways to do this within SQL Server. I like to use Extended Events. I have a blog post on how to use Extended Events to capture the deadlock graph. It can be found here. You can either create your own Extended Event session or use the System_health session that already exists. You can also use traceflags 1222 and 1204. In older versions of SQL Server, the trace flags were the only way to capture this information.
You can also use a third part monitoring tool such as the SQL Monitor by Red-Gate. This is a great tool! Here is a link to a demo.
Here are some really good links on deadlocks
How to Fix SQL Server deadlocks – Grant Fritchey at Red-Gate
Overview of Deadlocks – www.sqlshack.com
Analyzing Deadlocks – Microsoft
I will also be doing future blog posts on deadlocks from time to time. Thank you for visiting my blog!!!