Reasons Your Query Is Slow Right Now: Blocking, Blocking, Blocking

Last Updated on May 16, 2022 by Erik Darling

Lockzilla


Blocking sucks. SQL Server should have used an optimistic isolation level by default.

Sure, writers would still block each other, but think of all the time people could have saved not explaining NOLOCK hints.

  • Yes, they still take locks
  • Yes, it’s the same as READ UNCOMMITTED
  • Yes, sometimes they make queries faster without blocking

Wait, what?

Moving Right Along


There are some interesting things to say about blocking, I suppose, aside from that it sucks.

For instance, it can look really weird when there’s parallelism. And read queries can block write queries.

There, I’ve said interesting things about blocking. It still sucks.

But how do you diagnose it?

First, close Activity Monitor.

Doctor, Doctor


My favorite tool for diagnosing blocking is sp_WhoIsActive, and I’m going to show you three different ways to look at it.

The safest way

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
EXEC sp_WhoIsActive @get_task_info = 2, @get_additional_info = 1;
EXEC sp_WhoIsActive @get_task_info = 2, @get_additional_info = 1;
EXEC sp_WhoIsActive @get_task_info = 2, @get_additional_info = 1;

This isn’t the way most people do it, but it is the least impactful.

You get back the normal set of results:

sp_WhoIsActive
peachy

Cool, you can see queries, lock waits, and blocking sessions. But a short scroll to the right also brings you to this additional_info clickable column:

sp_WhoIsActive
oily water

It’s not so helpful for the query doing the blocking, but it’ll tell you what the queries being blocked are stuck on.

The usual way

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
EXEC sp_WhoIsActive @get_locks = 1;
EXEC sp_WhoIsActive @get_locks = 1;
EXEC sp_WhoIsActive @get_locks = 1;

This is the way I normally demo looking for blocking with it, because it is more useful to see what the blocking query is doing.

But I’ve also had it be slow. Really, really slow.

That can happen when there is JUST SO MUCH BLOCKING that it takes a long time to enumerate all of it.

But you’ll get back this lovely sort of detail:

sp_WhoIsActive
say it loud

Page locks. 102. Great.

The smart way

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
EXEC sp_WhoIsActive @find_block_leaders = 1, @sort_order = '[blocked_session_count] DESC';
EXEC sp_WhoIsActive @find_block_leaders = 1, @sort_order = '[blocked_session_count] DESC';
EXEC sp_WhoIsActive @find_block_leaders = 1, @sort_order = '[blocked_session_count] DESC';

It’s not always obvious by runtime which session is causing blocking, so you can use this command to find blocking chains, and order output by who’s doing the most blocking.

sp_WhoIsActive
troubleshot

Fixins


When you need to troubleshoot live blocking, these are the ways I usually check in on things with sp_WhoIsActive. It’s hard to beat.

Of course, long term, a monitoring tool, or logging the blocked process report to a table can help you when you’re not there hitting F5 live and in-person.

We’ve peeled back a couple scenarios where oddball things can slow a server down. Tomorrow we’ll look at a new one!

What’ll it be?

Your guess is not as good as mine.

Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.



One thought on “Reasons Your Query Is Slow Right Now: Blocking, Blocking, Blocking

  1. I’ve got an Agent Alert that runs a SQL Agent Job with the write to table param to to log all active session when blocking goes above our threshold. That way I get the email for the alert, and can review the sp_whoisactive results during analysis & troubleshooting.

Comments are closed.