Can You Get Parameter Sniffing on Updates and Deletes?

Execution Plans
8 Comments

Sure you can – parameter sniffing is everywhere. Anytime that you have one piece of code that needs to handle a varying number of rows, you’re probably gonna have to worry about parameter sniffing.

I’ll start with any Stack Overflow database and run a couple of non-parameterized update statements. I’m using literals here, not parameters:

These two statements have two very different actual execution plans. The first plan, updating just Brent, is very simple. Reading from right to left, we scan the clustered index to find all the Brents, and then we update the clustered index:

 

Except…the simplicity is a vicious lie. We’re updating LastAccessDate, and before running this update, I’d already created half a dozen indexes:

Every one of ’em includes LastAccessDate. SQL Server doesn’t have the concept of asynchronous indexes, so all of the indexes have to be updated whenever we change their contents. Hover your mouse over the clustered index operator to see the truth: SQL Server’s updating those, too.

The second plan is different because there are a lot of Alexes at Stack Overflow – Alex is one of the most common names in the database. Read the second plan right to left, and the first thing SQL Server does is find & update all of the Alexes in the clustered index:

But since we’re updating a lot of rows, SQL Server changes the way it approaches updating all of these nonclustered indexes. After the clustered index operator, keep reading…

For every index, SQL Server sorts all of the Alexes in that order so we can find ’em faster in the index, and then updates the index. It also does this process one index at a time, serially, in order, for Halloween protection.

These are called narrow and wide plans.

Paul White is both narrow and tall, but his Query Buck is wide and short

Brent’s plan, which does all of the updates in a single Clustered Index Update operator, is called a narrow plan.

Alex’s plan, which breaks out the work for each index and does them one at a time, is called a wide plan.

No, that doesn’t make any sense to me either. They should be called short and tall plans, because frankly, Alex’s plan is the wide one. I don’t make the rules, though, Paul White does. Take it up with the Kiwi. Even when his rules are wrong, they’re still right. Or maybe they just feel that way.

Put this in a stored proc, and you’ve got parameter sniffing.

Here’s our stored procedure to handle updates:

And now depending on which parameter runs first – Brent Ozar or Alex – we’ll get a different execution plan.

When Brent runs first, we get the narrow plan, so:

  • Brent does 143,688 logical reads and has no worktable
  • Alex does 352,260 logical reads, has no worktable, gets a 72KB memory grant, and the clustered index scan is single-threaded

And when Alex runs first, we get the wide plan, so:

  • Alex does 553,530 logical reads – more than 3 times the number of pages in the entire table! – plus another 41,339 on a worktable, gets a 5MB memory grant, and the clustered index scan goes parallel (although the rest of the plan is serial because that’s how SQL Server does it)
  • Brent does 143,730 logical reads, plus 17 on the worktable

The differences in this case aren’t all that bad, but keep in mind that I’m dealing with just a 1GB table – the smallest one in the Stack Overflow database. The larger your data becomes, the more you have to watch out for this kind of thing.

That’s why I teach my Fundamentals of Parameter Sniffing and Mastering Parameter Sniffing classes. Folks with a Live Class Season Pass can start watching the Instant Replays whenever they want, or drop in live. I’m teaching a bunch of classes over the next few weeks:

See you in class!

Previous Post
“But Surely NOLOCK Is Okay If No One’s Changing Data, Right?”
Next Post
My Live Streaming Setup for 2021: Iceland Edition

8 Comments. Leave new

  • Awesome explanation, thanks Brent!

    Reply
  • Is there something I’m missing here, or… usually parameter sniffing is about getting worse performance when you use a plan that isn’t formed for that particular parameter. But in this case, both Brent and Alex seems to be doing better if it had gotten the Brent parameter first. This just looks like a SQL Server did a poor job of forming a plan for Alex in the first place.

    Reply
    • Mqx – you’re on the right track! So now, what might happen if someone rebuilt an index, Alex ran first, and SQL Server sniffed his parameters? Would folks think that suddenly performance had gotten worse out of nowhere? That’s what parameter sniffing is.

      Reply
      • Yeah, definitely 😛 it just felt like a different approach to things, where the “optimized” version was actually the bad one in any situation… in my experience it’s at least optimized for its own parameter in that case! Informative 🙂

        Reply
  • So what if you were to implement a “wrapper” sproc “usp_TouchUser_counter” which determines how many rows will be updated. Based on those results, it then calls one of a number of identical sprocs such as “usp_TouchUser_100_or_fewer”, “usp_TouchUser_101_to_1000”, “usp_TouchUser_1001_to_10000”, etc?

    It’s convoluted, but each sproc would only have to deal with row counts in a relatively narrow range, which should minimize the effect of parameter sniffing. An individual sproc wouldn’t have to deal with counts several orders of magnitude higher or lower than its “first call” count.

    I would think having 5 times the number of stored procedures in the DB would less detrimental to performance than using “option recompile” in situations like these.

    Reply

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu