SQL Server blocking is a pain in the posterior for plenty of admins, as while it is a necessary aspect of how the database operates to ensure integrity is preserved, it can be problematic from a performance perspective.
To address this, here are some handy tips for both pinpointing blocking when it occurs and remedying the issue after detection.
Image Source: Pixabay
Use the right tools
First and foremost, you can make your life much easier if you harness tools designed to automatically identify blocking in SQL Server and thus save you the hassle of having to do all of the hard work manually.
Modern server monitoring solutions will have the ability to streamline this entire process, generate alerts when issues are found and guide you towards the flawed queries and processes so you can implement a fix or escalate the issue if necessary.
Look into over-long wait times
Another way to root out blocking is to check in on the wait statistics that are generated by your server, as processes which are forced to stand in line to use a particular resource for longer than normal might suggest that some other process is taking precedence through locking, to a problematic extent.
As mentioned, SQL Server uses resource locking to give certain processes priority and also to ensure that changes are not made simultaneously, which might otherwise lead to data corruption.
Wait times of more than a few seconds could indicate any number of things, ranging from a sub-optimally composed query to an I/O bottleneck and everything in between. But the point here is to use your exploration of the wait stats as a way to guide your next steps.
Improving query efficiency
Once you have an idea of which queries may be prone to producing problematic blocking scenarios, the time has come to start looking for suitable solutions.
Your approach will need to be based on the specific circumstances you are faced with, but there are a few common query complaints that might be at the heart of the matter.
For example, you may have transactions which are composed in such a way that requires an exclusive lock is held on resources, even if a query in question does not necessarily need to maintain this. Ensuring that transactions are built in an efficient way, getting from A to B with the minimum number of steps in between, will set you on the right path.
Distinguishing deadlocking
The final factor to bear in mind with regards to SQL Server blocking is that you should also be on the lookout for deadlocking, a more pressing problem to fix in any management scenario.
Deadlocking will leave one of the competing processes terminated, requiring that it is rerun from the start, so unlike blocking it can create more significant disruption, even if again the aim is to ensure data integrity remains consistent.
Overall, it is critical to keep an eye on blocking and deadlocking, and to troubleshoot these issues sooner rather than later, as left unchecked they can be a persistent thorn in your side.