Hop On Your Good Foot
Several weeks back, I blogged about a missed opportunity with the new parameter sensitive plan feature. At the time, I thought that there was indeed sufficient skewness available to trigger the additional plan variants, …
Category: Parameter Sniffing
How SQL Server 2022’s Parameter Sensitive Plan Feature Can Make Query Store Confusing
Psssssp
Thanks for watching!
Going Further
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 …
SQL Server 2022 Parameter Sensitive Plan Optimization: Does Not Care To Fix Your Local Variable Problems
–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 …
SQL Server 2022 Parameter Sensitive Plan Optimization: How PSP Can Help Some Queries With IF Branches
Time Served
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 …
SQL Server 2022 Parameter Sensitive Plan Optimization: Sometimes There’s Nothing To Fix
Best Intentions
After seeing places where the Parameter Sensitive Plan (PSP) optimization quite stubbornly refuses to kick in, it’s somewhat amusing to see it kick in where it can’t possibly have any positive impact.
Even though some parameters are responsible …
SQL Server 2022 Parameter Sensitive Plan Optimization: Does PSP Work With Dynamic SQL?
No, Really
When I talk to clients about using dynamic SQL, they’re usually under the misconception that those plans can’t get reused.
That may be true under some circumstances when:
- It’s not properly parameterized
- You use EXEC only and not
SQL Server 2022 Parameter Sensitive Plan Optimization: A Missed Opportunity For PSP To Make A Query Go Faster
Farce
The index and proc creation script for this are a bit on the long side, so I’m going to stick them in a GitHub gist to keep the post concise, since we have some other ground to cover here.…
SQL Server 2022 Parameter Sensitive Plan Optimization: When PSP Can Help Your Queries Go Faster
Errant Hairs
I’ve used this proc as an example in the past. It’s a great parameter sniffing demo.
Why is it great? Because there’s exactly one value in the Posts table that causes an issue. It causes that issue because …
SQL Server 2022 Parameter Sensitive Plan Optimization: Extended Events Related To PSP
Respect Wood
There isn’t a lot interesting in these Extended Events. I was pretty disappointed with them.
The ones I looked at are in this session definition:
CREATE EVENT SESSION
psp
ON
SERVER
ADD EVENT
sqlserver.parameter_sensitive_plan_optimization
(
ACTION(sqlserver.sql_text)
),
ADD … SQL Server 2022 Parameter Sensitive Plan Optimization: How PSP Works To Solve Some Parameter Sensitivity Issues
Classico
In the bad old days, which are still your days (but at least you have better days to look forward to), SQL Server’s cost-based optimizer would take a parametrized query, come up with a query plan, and then reuse …