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
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)
IF @r IS NULL
SET @r = 11;
FROM dbo.Users AS u
WHERE u.Reputation = @r
ORDER BY u.CreationDate DESC;
If I run the proc, actually passing in 11:
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;
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.
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:
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.