Sniffed Nulls and Magic Numbers

I Sniff Your Milkshake

Building off of A Simple Stored Procedure Pattern To Avoid, I wanted to talk about a similar one that I see quite often that is not nearly as clever as one would imagine.

I goes something like this: If this variable is passed in as NULL, substitute it with something else. It has a lot of variations.

They all have the desired effect: substituting a passed in NULL with a magic number.

They all have the same problem: the parameter value is sniffed as NULL, and a query plan is compiled with cardinality estimates for NULL.

Exam Room 1

Let’s work off of the simplest to understand version.

If I run the proc, actually passing in 11:

The query plan looks like this, with SQL Server estimating 117,369 rows.

Living The Good Life

But if I recompile, and run it passing in a NULL, things change.

The plan looks like this now, estimating 1 row, but returning all 117,369 rows for 11:

We Don’t Have To Dance

Stupid Query Tricks

In the bad old days, if you wanted to see which values a cached plan was compiled with, you had to open the plan, open the XML, and scroll aimlessly.

It sucked.

So a while back, I added a column to sp_BlitzCache that does it for you.


If you click on it, you’ll get the ANSI settings, and the cached parameter value(s).

Results may vary

*angry developer noises*

I KNOW! And a lot of familiar tricks don’t work to get the “right” plan for 11.

Even changing the stored procedure to set a default value won’t work, if someone passes in a NULL instead:

There’s a lot of stuff that seems like it should work that really doesn’t.

Things like this are unfortunate, because you often see them with comments like “to avoid parameter sniffing”, or “fixes performance issue”.

The behavior is a real surprise to most people, especially when they see that SQL Server is building plans for the value they were hoping to avoid.

Thanks for reading!

Brent says: when you’re troubleshooting parameter sniffing, and you’re trying different input values for a stored procedure, actively try null, too. See what happens when you explicitly pass a null in as the input parameter – just because it has a default value on the CREATE PROC doesn’t mean the default value overrides an explicit null parameter.

Previous Post
Comparison Review: Microsoft SSDT vs Redgate SQL Source Control
Next Post
Query Store And Cruel Defaults

8 Comments. Leave new

  • It’s such a bummer that MS hasn’t addressed this known issue, and I can recall how it FUBAR’d a seemingly reasonable set of report procedures with default date parameters when I was an MSSQL beginner coding at a brokerage back in *2007*.
    And perhaps it’s worth mentioning that the simplest workaround has always been to declare a *separate* variable inside the stored proc/function, and set that to ISNULL(@TheParameterVariable,’yourdefaultvaluehere’)… and then use that *separate* variable in your actual SELECT/WHERE or whatever SQL construct you’re calling. Thank you so much Brent et. al., this blog is a daily gift.

  • Are we saying the only fix is to address this outside of the SP?

    • Hm, no. Can you give an example of what you mean?

      • Well, if I’m understanding correctly, you’re saying that there isn’t a way to pass in a null without the query plan using the null no matter how we reassign it. Therefore, the only remedy would be to not pass in a null at all (i.e. have the calling app change the value beforehand). Perhaps I’m misunderstanding though and there’s another remedy


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.