SQL Server Blocking queries are one of those things that can really bog down your database. You might have plenty of memory, and way more CPU than you need, but a single blocking query may bring your server to a halt.
Over the last several years at Stedman Solutions we have done lots of performance tuning assessments, and when we do those, there are usually 3 categories that slow down a SQL Server.
- Hardware and Server configuration.
- Things that can be configured at the server level to help with performance.
- Slow queries.
- These are those queries that just take a while to run due to large sets of data, missing indexes, out of date statistics, or just something wrong in the query.
- Blocking queries.
- These are the queries that block your SQL Server and hold a lock on something preventing other queries from running.
The third category is what I am focused on here, the blocking queries. With these you need to take a different approach than with the other 2 categories.
First you need to understand why the query is blocking. Is this an update or an insert? Is this a delete? Why is it so slow? Then once you understand why it is slow, you can work on making it faster.
A while back I worked on a system where when users logged in, an update would run that would update every row in the users table. This update would show the count of how many times a user had visited the system. This query was doing a JOIN from the users table to a log table with a history of all logins and updating the count of logins for all users.
This caused blocking because it was taking a while to do the JOIN to collect that data. It worked great in development with a small data set, but in production with hundreds of thousands of users it really slowed down.
The problem was that while this slow update was going it was locking the users table and causing problems with other users attempting to log in and to set their last login date.
How did we fix it? First, I added an index that helped with the JOIN and sped up the process a bit, but it was still locking for several seconds which was unacceptable.
Next, we looked at the logic. Why were we updating all users instead of just the one that logged in. I changed the query to just do the update (and JOIN) on the specific user logging in and the run time for that update then took about 120ms. Much better than the many seconds it was taking even after we added indexes.
Basically, I removed a whole bunch of work that was not needed, and only updated the user that had changed the data. The blocking went away and there has not been an issue in that code since.
One of the tools that I used to find this issue was the Blocking Query Monitor that I have built over the last several years. I was able to find the blockers and work to resolve them quickly based on the tracking results provided by the blocking query monitor.
I have created a bundle that includes the Blocking Query Monitor and 2 hours of coaching. With this we can get it set up and running for you in the first hour, then use the second hour to review that the blocking query monitor finds over the next few days to a week and determine a plan of action to resolve the blocking.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!