How Bad Cardinality Estimates Can Make Blocking Worse In SQL Server

Stay Sober


I recently came across a bad locking problem. It was one of those things that happened “suddenly” and “for no reason”.

When I looked at the stored procedure, the code was something like this:

CREATE PROCEDURE dbo.bad_news(@PostTypeId INT)
AS	
BEGIN

DECLARE @PTID INT
SELECT @PTID = CASE WHEN @PostTypeId < 1
                         OR @PostTypeId > 8
				    THEN 4
			   END
UPDATE p
SET p.Score += 1
FROM dbo.Posts AS p
WHERE p.PostTypeId = @PTID;

END

I mean, it would look like that if we were using Stack Overflow.

We weren’t, but I’ll leave the rest to your imagination.

Outside The Proc


We have this index:

CREATE INDEX ix_spanky ON dbo.Posts(PostTypeId);

Let’s run this code, and look at what happens.

BEGIN TRAN
UPDATE p
SET p.Score += 1
FROM dbo.Posts AS p
WHERE p.PostTypeId = 3;
ROLLBACK

The locks look like this:

<Lock resource_type="KEY" index_name="PK_Posts__Id" request_mode="X" request_status="GRANT" request_count="28" />
<Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
<Lock resource_type="PAGE" page_type="*" index_name="PK_Posts__Id" request_mode="IX" request_status="GRANT" request_count="17" />

Our nonclustered index helps us find the rows we want to update. We don’t need to lock it because it doesn’t have the Score column in it.

We do need to lock the clustered index, but we’re able to take a small number (28) of Key locks.

This is the best outcome!

The worst outcome happens when we do this, which is what was happening in the stored procedure.

DECLARE @PostTypeId INT = 3;
BEGIN TRAN
UPDATE p
SET p.Score += 1
FROM dbo.Posts AS p
WHERE p.PostTypeId = @PostTypeId;
ROLLBACK

The locks look like this:

<Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="1" />
<Lock resource_type="PAGE" page_type="*" index_name="PK_Posts__Id" request_mode="X" request_status="GRANT" request_count="17" />

We not only have exclusive locks on pages, but on the entire table now.

Overestimaters Anonymous


The execution plans for these two queries are much different.

SQL Server Query plan
umop

Likewise, the estimates are much different.

SQL Server Query Plan
upside

Oh So Loco


This is a fairly well-documented outcome of using a “declared variable”, or the optimize for unknown hint.

The optimizer makes a usually-generally-bad guess at the number of rows it’ll have to deal with.

In this case, the stored procedure had been modified to account for bad values passed in from the application.

The outcome was severe blocking because modification queries were taking far more intrusive locks than necessary.

So, you know, don’t do that.

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.



5 thoughts on “How Bad Cardinality Estimates Can Make Blocking Worse In SQL Server

  1. I feel like a noob, but how do you see the lock details – is it based on extended events? Thanks in advance.

  2. So let’s say someone did this; I see people use variables all the time. Would you take the input, build a temp table, and then join on the “good values” instead of using the variable in the query?

    1. Honestly — for a query this simple, depending on execution frequency, I’d be pretty likely to just stick a recompile hint on it so the declared variables are sniffed properly.

Comments are closed.