–To Fix Parameter Sniffing
There are some code comments you see that really set the stage for how tuning a query is going to go.
Usually one misgiving about how SQL Server works gives way to a whole levee-breaking bevy of other ones and three days later you can’t feel your legs but dammit it’s done.
Okay, maybe it was three hours, but it felt like three days. Something about the gravitation pull of these black hole queries.
One fix I’ve been wishing for, or wish I’ve been fixing for, is a cure for local variables. I’d even be cool if Forced Parameterization was that cure, but you know…
Time will tell.
Let’s say we’ve got this stored procedure, which does something similar to the “I’m gonna fix parameter sniffing with a local variable hey why is everything around me turning to brimstone before my very eyes?” idea, but with… less of an end-of-times vibe.
CREATE OR ALTER PROCEDURE dbo.IndexTuningMaster ( @OwnerUserId int, @ParentId int, @PostTypeId int ) AS BEGIN SET NOCOUNT, XACT_ABORT ON; /*Someone passed in bad data and we got a bad query plan, and we have to make sure that doesn't happen again*/ DECLARE @ParentIdFix int = CASE WHEN @ParentId < 0 THEN 0 ELSE @ParentId END; SELECT TOP (1) p.* FROM dbo.Posts AS p WHERE p.ParentId = @ParentIdFix AND p.PostTypeId = @PostTypeId AND p.OwnerUserId = @OwnerUserId ORDER BY p.Score DESC, p.Id DESC; END;
How bad could a top 1 query be, anyway?
When we run this query like so and so:
EXEC dbo.IndexTuningMaster @OwnerUserId = 22656, @ParentId = 0, @PostTypeId = 1; EXEC dbo.IndexTuningMaster @OwnerUserId = 22656, @ParentId = 184618, @PostTypeId = 2;
We come up with zip zero zilch none nada:
We get a super low guess for both. obviously that guess hurts a large set of matched data far worse than a small one, but the important thing here is that both queries receive the same bad guess.
This is a direct side effect of the local variable’s poor estimate, which PSP isn’t quite yet ready to go up against.
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 CTP 2.1 Improvements To Parameter Sensitive Plan Optimization
- SQL Server 2022 Parameter Sensitive Plan Optimization: Sometimes There’s Nothing To Fix
- SQL Server 2022 Parameter Sensitive Plan Optimization: How PSP Can Help Some Queries With IF Branches
- SQL Server 2022 Parameter Sensitive Plan Optimization: Does PSP Work With Dynamic SQL?