Bad Estimates And Locking

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:

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:

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

The locks look like this:

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.

The locks look like this:

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.

umop

Likewise, the estimates are much different.

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!

5 thoughts on “Bad Estimates And Locking”

  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.

Leave a Reply

Your email address will not be published. Required fields are marked *