Blocking is a common occurrence in an SQL server context, but if you are new to the world of database management you might not know what this issue entails and perhaps even fear that it is a sign of serious underlying problems.
To allay your fears and clear up the mystery, here is a brief overview of blocking and the steps you can take to tackle it.
Image Source: Pixabay
SQL blocking explained
SQL blocking according to SentryOne is an offshoot of the way that concurrent databases operate. Because processes can be executed simultaneously, it is likely that at some point more than one process will need access to the same resource.
When a resource is being used by a process, it can be ‘locked’ to stop other processes jumping onboard and creating complications, with this secondary process being ‘blocked’ while they are queuing up in anticipation of the resource being vacated once the occupying process has run its course.
In other words, without blocking, it would be very tricky for databases to run processes concurrently, because data integrity could be compromised as soon as two or more processes were vying for a given server resource.
So there is no real issue with blocking and it is just one of the built-in design features of SQL servers, right? Well, while this is partly true, there are performance penalties which inevitably come with blocking, and if this crops up too frequently then the server will behave sub-optimally.
Troubleshooting options
Now that you know why blocking occurs, you can begin to take on a troubleshooting role to resolve any problematic blocks.
The easiest way to achieve this is to make use of database monitoring tools to automatically track and log blocks so that they can be flagged and scrutinized by the administrator. That way you can separate the infrequent blocks, which are not really an issue, from the commonplace conflicts that are more worthy of your time and attention.
Another way to home in on blocking is to check the wait statistics generated by the database. These will show the average amount of time it takes for processes to complete, with waits longer than 5 seconds being at the problematic end of the spectrum.
When you have worked out which blocks are creating delays, you can take action to optimize queries and address any glaring flaws in the way the database is orchestrated to restore harmony.
Dealing with deadlocks & summing up
It is also worth mentioning that SQL databases can also be afflicted by deadlocking, a process that, while necessary to preserve data integrity, is a little more troubling than standard blocking.
Deadlocks arise in the case that two processes with exclusive locks on resources are unable to change places, with the server choosing one as the victim and terminating it. An error notification will be created in this case, which makes deadlocks slightly easier to spot than blocks.
Ultimately you should always stay on top of any SQL database maintenance and monitoring if you want to avoid conflicts and troubleshoot issues in a timely manner.
