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.
Let’s take a closer look!
First, we need an index. Indexes are good for things, I’m told.
CREATE INDEX ix_displayname ON dbo.Users (DisplayName);
I mean, maybe not this index. It’s kind of okay for about one query, and it isn’t this one.
UPDATE dbo.Users SET Reputation = 0 WHERE Id = 3187747;
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.
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.
What the live query plan doesn’t tell you about your query.
- That it’s being blocked
- 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?
This is very interesting. Somewhere along the way I missed the fact that Clustered Indexes could be locked. Thanks!
Hmm, I think “Clustered Indexes could be locked” == “Tables could be locked”…
I also believe:
1) Clustered Indexes *are* Tables.
2) Tables can be: Clustered Indexes (aka. ordered rows) OR Heaps (unordered rows)
This is a nice way to demonstrate the various isolation levels. If you were running as read uncommitted there would have been no blocking.
And if he would run an alter table instead of an update, it would be blocked regardless of READ UNCOMMITTED.
The point is not to avoid blocking, but the fact that the live execution plan does not show if an operation is blocked .
[…] BrentO Post 2 […]
[…] They don’t show blocking. […]