Blocking is an entirely normal part of the way that SQL Server works, although there is a distinction between necessary and problematic blocks that you need to appreciate if you are to ensure performance is optimal.
There are also other issues related to blocking that need to be taken into account, so here is a look at how you can identify when blocks occur and what you should do to deal with them.
The basics of SQL Server blocking
When blocked processes crop up in an SQL Server context, this is a result of a conflict over specific resources. Because all databases have finite access to the available hardware resources on which they operate, the software needs to be able to orchestrate the allocation of this access according to a locking system.
By blocking processes from using a resource until the process which currently has a lock on it has been completed, a server can keep chugging along while the integrity of the information stored on it is maintained.
In short, without process blocking, SQL Server would quickly descend into chaos, creating performance problems as well as leading to data corruption.
Detecting blocks
There are a few ways to pinpoint SQL Server blocking, the most common of which is to turn to the Activity Monitor that is built into Microsoft’s ever-evolving software itself.
This provides you with insights on various aspects of performance, ranging from which processes are running to the potentially problematic queries which may be hogging resources.
From here you can see when blocking occurs and also identify exactly how long the block persists for in the event that it arises.
As mentioned earlier, blocking is not inherently problematic, but if blocks last for too long then they will of course be a bit of a spanner in the works, so to speak.
There are no hard and fast rules for working out whether a block is problematic based on duration alone, although anything lasting more than five seconds is definitely worthy of your attention.
You can also use dedicated SQL Server monitoring software to achieve this, which may be a good move if you want to automate certain aspects of database maintenance and also receive alerts when issues like blocking arise.
Dealing with blocking
There are a number of reasons why a block might be problematic, and you will need to troubleshoot this to determine the correct course of action.
Perhaps a transaction has erroneously been allowed to hold a lock over a resource for longer than necessary even after its completion. Perhaps a query has an over-aggressive locking profile that gives it undue priority over other processes.
Whatever the case, once you have been able to identify the problem process you should be in a position to perform the necessary maintenance to ameliorate the situation.
You should also be on the lookout for deadlocks, which are another more extreme form of resource access imperfection that database administrators will encounter. Vigilance and a proactive approach to SQL server upkeep will see you in good stead.
