I’ve spent a bit of time talking about how IF branches can break query performance really badly in SQL Server.
While the Parameter Sensitive Plan (PSP) optimization won’t fix every problem with this lazy coding habit, it can fix some of them in very specific circumstances, assuming:
- The parameter is eligible for PSP
- The parameter is present across IF branches
We’re going to use a simple one parameter example to illustrate the potential utility here.
After all, if I make these things too complicated, someone might leave a comment question.
Here’s the procedure we’re using. The point is to execute one branch if
@Reputation parameter is equal to one, and another branch if it equals something else.
In the bad old days, both queries would get a plan optimized at compile time, and neither one would get the performance boost that you hoped for.
In the good news days that you’ll probably get to experience around 2025, things are different!
CREATE OR ALTER PROCEDURE dbo.IFTTT ( @Reputation int ) AS BEGIN SET NOCOUNT, XACT_ABORT ON; SET STATISTICS XML ON; IF @Reputation = 1 BEGIN SELECT u.Id, u.DisplayName, u.Reputation, u.CreationDate FROM dbo.Users AS u WHERE u.Reputation = @Reputation; END; IF @Reputation > 1 BEGIN SELECT u.Id, u.DisplayName, u.Reputation, u.CreationDate FROM dbo.Users AS u WHERE u.Reputation = @Reputation; END; SET STATISTICS XML OFF; END; GO
Johnson & Johnson
If we execute these queries back to back, each one gets a new plan:
EXEC dbo.IFTTT @Reputation = 1; GO EXEC dbo.IFTTT @Reputation = 2; GO
Optimize For You
The reason why is in the resulting queries, as usual. The Reputation column has enough skew present to trigger the PSP optimization, so executions with differently-bucketed parameter values end up with different plans.
option (PLAN PER VALUE(QueryVariantID = 3, predicate_range([StackOverflow2013].[dbo].[Users].[Reputation] = @Reputation, 100.0, 1000000.0))) option (PLAN PER VALUE(QueryVariantID = 2, predicate_range([StackOverflow2013].[dbo].[Users].[Reputation] = @Reputation, 100.0, 1000000.0)))
And of course, each plan has different compile and runtime values:
If I were to run this demo in a compatibility level under 160, this would all look totally different.
This is one change I’m sort of interested to see the play-out on.
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 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: Does Not Care To Fix Your Local Variable Problems
- SQL Server 2022 Parameter Sensitive Plan Optimization: Sometimes There’s Nothing To Fix
- SQL Server 2022 Parameter Sensitive Plan Optimization: The Problem With Sniffed Parameter Sensitivity