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.
1 2 3 4 5 6 7 8 9 10 11 12 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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; |
If I run the proc, actually passing in 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.
1 2 3 |
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.

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:
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.
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.
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
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)!
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
Ah, okay, yeah, that would be one way to do it.
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.