Live Query Plans and Blocking

You’d think this would be cool

After all, you’ve got a query plan being executed LIVE ON NATIONAL TELEVISION! But it’s not. It’s actually one of those weird reasons why you still need other ways of looking at DMVs for issues.

Demo Library

Let’s take a closer look!

First, we need an index. Indexes are good for things, I’m told.

I mean, maybe not this index. It’s kind of okay for about one query, and it isn’t this one.

Don’t worry, I’m only setting my own Reputation back to zero. No other Users were hurt during this demo.

Over in another window, I’m going to turn on Live Query Plans and then start running my magnificent select query.

Quick Rundown

So what’s happening here?

Our update is running in an open transaction and changing the Reputation to 0 for my Id.

Our select is running and trying to show the row of data for my Display Name.

In the query plan, we see our select query using the very narrow nonclustered index on Display Name to find the row we want, and then doing a Key Lookup back to the clustered index to fetch all the columns we need to return.

The nonclustered index returns the single row we’re interested in, but the Key Lookup is blocked by the update, so it can’t continue.

Intersection

What the live query plan doesn’t tell you about your query.

1. That it’s being blocked
2. What it’s waiting on

No seriously, nothing turns red or beeps or starts checking its watch, and there’s no Clippy asking you to hang on a moment. It just sits there.

And since the query hasn’t finished running yet, we don’t have any wait stats in the plan XML yet.

No, not Activity Monitor

If you run an activity snapshot tool like sp_WhoIsActive or sp_BlitzWho, you see the blocking quite easily.

All the lock waits and the blocking session Id is right up front. In sp_BlitzWho, we hit a DMV “new” to SQL Server 2016 called sys.dm_exec_session_wait_stats to return the top waits a session has encountered. Looking there, you can see just how long I was running demos for and writing this blog post.

Thanks for reading!

Brent says: I was kinda hoping there would be a red flashing X or something, showing the operator that was blocked. How cool would that be? Or maybe have the Soup Nazi pop up over the key lookup. As long as we’ve got an animated GIF the post, why not, right?