Blip In The Wall
Parameters get a bad name. It seems everything that gets sniffed does, whether it’s a stored procedure, a piece of anatomy, or an illicit substance.
Once you stop worshipping memes, you’ll find that things aren’t always black and white. End results are important.
What never seems to get a bad name, despite numerical supremacy in producing terrible results, are local variables.
In this particular scenario, I see developers use them to try to beat “parameter sniffing” to no avail.
A chorus of “it seemed to work at the time”, “I think it made things a little better”, “it worked on my machine”, and all that will ensue.
But we know the truth.
This general strategy is one that I see often enough to warrant a blog post to talk about how bad it is.
It’s a little different from yesterday, but equally misguided in its effect.
CREATE OR ALTER PROCEDURE dbo.counter_if ( @PostTypeId int = NULL, @VoteTypeId int = NULL, @CreationDate datetime = NULL ) AS SET NOCOUNT, XACT_ABORT ON; BEGIN IF @PostTypeId IS NOT NULL BEGIN DECLARE @PostTypeIdCopy int = @PostTypeId, @CreationDatePostCopy datetime = @CreationDate; SELECT c = COUNT_BIG(*) FROM dbo.Posts AS p JOIN dbo.Votes AS v ON p.Id = v.PostId WHERE p.PostTypeId = @PostTypeIdCopy AND p.CreationDate >= @CreationDatePostCopy; END; IF @VoteTypeId IS NOT NULL BEGIN DECLARE @VoteTypeIdCopy int = @VoteTypeId, @CreationDateVoteCopy datetime = @CreationDate; SELECT c = COUNT_BIG(*) FROM dbo.Posts AS p JOIN dbo.Votes AS v ON p.Id = v.PostId WHERE v.VoteTypeId = @VoteTypeIdCopy AND v.CreationDate >= @CreationDateVoteCopy; END; END;
In this (bad, threatening individual) display of transact SQL, we declare local copies of each parameter as a variable and then use those local copies as arguments in the queries.
There are other variations on this theme that I see from time to time, but I think that you get the point.
What I want to show you here is related to cardinality estimation issues with the above strategy.
DBCC FREEPROCCACHE GO EXEC dbo.counter_if @PostTypeId = 8, @VoteTypeId = NULL, @CreationDate = '20080101'; GO DBCC FREEPROCCACHE GO EXEC dbo.counter_if @PostTypeId = 2, @VoteTypeId = NULL, @CreationDate = '20080101'; GO DBCC FREEPROCCACHE GO EXEC dbo.counter_if @PostTypeId = NULL, @VoteTypeId = 4, @CreationDate = '20080101'; GO DBCC FREEPROCCACHE GO EXEC dbo.counter_if @PostTypeId = NULL, @VoteTypeId = 2, @CreationDate = '20080101'; GO
The point here is to execute each query with a cleared plan cache and show that cardinality estimation for both parameter values is the same value, and also completely wrong.
Here are the plans that hit the Posts table:
Here are query plans for the Votes table:
Regardless of how many rows may actually flow out of the table, SQL Server makes the same “density vector” guess, which sucks in these cases.
This isn’t parameter sniffing, or plan caching, because we’re wiping out the plan cache between executions. This is what happens when local variables get used, even when you copy parameter values over to them.
I’ve seen this work under rare circumstances where SQL Server’s estimates were bad regardless of the supplied parameters, but… I want to emphasize that it’s incredibly rare for this to be a good long-term solution.
So, we’ve seen the problem, and we’ve seen things that don’t work to fix the problem.
Tomorrow we’re going to see if stored procedures will give us better results.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
- SQL Server 2022 Parameter Sensitive Plan Optimization: How PSP Can Help Some Queries With IF Branches
- SQL Server IF Branches And Query Performance Part 5: Does Dynamic SQL Work?
- SQL Server IF Branches And Query Performance Part 4: Do Stored Procedures Work?
- SQL Server IF Branches And Query Performance Part 2: Trying To Fix Parameters Doesn’t Work