If your SQL server is behaving sub-optimally, there could be a number of culprits to blame. Both blocking and deadlocking are possible problems that need investigation, although in spite of their similar-sounding names they are actually significantly distinct from one another.
To help you overcome whatever performance woes you are facing, here is an examination of the difference between these two events and what you can do to deal with them.
Basics of Blocking Explained
In the simplest terms, a block occurs when two or more processes are competing for the same slice of your server’s hardware resources simultaneously.
Because SQL server operates a lock-based concurrency system to prevent conflicts arising, the presence of blocking is perfectly normal and is indeed integral to ensuring that the server operates as it should.
Different processes can be given different priorities to ensure that the most important requests are dealt with first.
How do I Detect Problematic Blocking?
It is worth mentioning that not all blocking is ideal, as explained in detail here. You can work out whether bad blocking is occurring by looking at how long a given block lasts for; any longer than 5 seconds may be a sign that intervention is necessary.
Most modern monitoring solutions for SQL Server will be able to pinpoint blocking as and when it occurs and give you the ability to take action if this seems like a sensible strategy.
Deadlocks are similar to blocks on an SQL database, in the sense that they arise when a pair of processes want to exclusively make use of a given resource but do not have the ability to achieve this, creating a standoff that is not able to be resolved.
The good news is that deadlocks are dealt with automatically in many cases, since the software will note the presence of the conflict and kill one of the two processes to keep the wheels turning.
A deadlock may arise in a situation where one process is waiting to make the leap to another resource while occupying a separate resource which the process it needs to usurp is also aiming to switch to.
How do I address deadlocks?
The details of any deadlock that occur in the SQL Server environment should be logged as events, with trace flags letting you know exactly what happened and which processes and resources were involved.
With this information to hand, you should be able to find the right path to preventing these deadlocks occurring in the future.
For example, correctly organizing the order in which resources are accessed will minimize the likelihood of deadlocking, as will reducing the amount of time during which locks can be held over resources by the processes that require them.
The more familiar you become with monitoring and optimizing an SQL server, the better equipped you will be to deal with problematic blocking and deadlocks as and when they arise.
It is always better to be proactive with your database maintenance, since problems which go unresolved will only recur to compromise performance.