Sniffed Nulls and Magic Numbers

Last Updated 4 years ago

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.

    IF @r IS NULL
        SET @r = 11;

    SET @r = ISNULL(@r, 11);

    WHERE u.Reputation = ISNULL(@r, 11);


    WHERE u.Reputation = CASE WHEN @r IS NULL 
                              THEN 11
                              ELSE @r
                         END;

IF @r IS NULL

SET @r = 11;

SET @r = ISNULL(@r, 11);

WHERE u.Reputation = ISNULL(@r, 11);

WHERE u.Reputation = CASE WHEN @r IS NULL

THEN 11

ELSE @r

END;

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.

CREATE OR ALTER PROCEDURE dbo.var_func (@r INT)
AS
BEGIN

    IF @r IS NULL
        SET @r = 11;

    SELECT *
    FROM dbo.Users AS u
    WHERE u.Reputation = @r
    ORDER BY u.CreationDate DESC;

END;

CREATE OR ALTER PROCEDURE dbo.var_func (@r INT)

BEGIN

IF @r IS NULL

SET @r = 11;

SELECT *

FROM dbo.Users AS u

WHERE u.Reputation = @r

ORDER BY u.CreationDate DESC;

END;

If I run the proc, actually passing in 11:

EXEC dbo.var_func @r = 11;

1	EXEC dbo.var_func @r = 11;

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

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

EXEC sys.sp_recompile @objname = N'dbo.var_func';

EXEC dbo.var_func @r = NULL;

EXEC sys.sp_recompile @objname = N'dbo.var_func';

EXEC dbo.var_func @r = NULL;

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

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.

I’M MAGIC

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

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:

CREATE OR ALTER PROCEDURE dbo.var_func (@r INT = 11)

1	CREATE OR ALTER PROCEDURE dbo.var_func (@r INT = 11)

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.

Comparison Review: Microsoft SSDT vs Redgate SQL Source Control

Query Store And Cruel Defaults

8 Comments. Leave new

Ed M.
December 18, 2018 11:27 am

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.

Reply
- Erik Darling
  December 18, 2018 11:55 am
  
  Ed — bad news! When you declare a separate variable inside the procedure, and then use that, you get the magic number/density vector estimate, which is often pretty terrible.
  
  Thanks,
  Erik
  
  Reply
  - Ed M.
    December 18, 2018 1:52 pm
    
    In a twisted way, I’m glad I said it — that will prevent some folks from using my misinformation, file it under common misconceptions (ouch)!
    
    Reply
D. S.
December 18, 2018 3:32 pm

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

Reply
- Erik Darling
  December 18, 2018 3:34 pm
  
  Hm, no. Can you give an example of what you mean?
  
  Reply
  - D. S.
    December 18, 2018 3:52 pm
    
    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
    
    Reply
    - Erik Darling
      December 18, 2018 6:12 pm
      
      Ah, okay, yeah, that would be one way to do it.
      
      Reply
    - Henrik Staun Poulsen
      January 9, 2019 2:03 am
      
      hi D.S.
      Another way to fix this, (I think), is to change the code such that you call one of two other stored procedures, depending on the value of you input parameter. Downside is that you get a lot of “sub”-stored-procedures. See http://sommarskog.se/dyn-search.html#fineprint
      The third way is to use OPTION RECOMPILE, but read sommarskog.se before you go overboard.
      
      Reply