SQL Server 2022 Parameter Sensitive Plan Optimization: Indexing Matters

Starter Pistol


I started writing this post to answer a different question about if the Parameter Sensitive Plan (PSP) optimization worked with computed columns.

Of course it does! Even non-persisted computed columns have statistics generated on them. The only gatekeeping activity from there is whether or not your statistics qualify for the PSP optimization to kick in.

My excitement for the PSP optimization reminds me of the first time I drank Johnnie Walker Blue. I was all riled up on the hype around a $200 bottle of scotch.

Then it tasted like dessert syrup.

That’s a lot like what seeing this pop up in related Extended Events feels like, even for columns with massive amounts of skew in them.

SkewnessThresholdNotMet
how i didn’t meet your mother

Microsoft really need to introduce a hint that tells the optimizer to treat a parameter as being sensitive, and/or a way to tweak the threshold for Skewness not being met.

The heuristics are really falling short, here.

Hash And Rehash


Anyway, let’s use the example that I had started with here, to illustrate that the PSP optimization does work with a computed column, but… like any other column, indexes make all the difference.

I’m using the same example query over and over again, because a lot of the other great examples of parameter sensitivity that I have demo queries written for don’t seem to trigger it.

Here’s the computed column I started with:

ALTER TABLE 
    dbo.Posts 
ADD 
    WhatIsIt 
AS ISNULL
   (
       ParentId,
       0
   );

I’m not actually sure that there are NULL values in there, but the column is marked as NULLable so the ISNULL function doesn’t short-circuit.

Here’s the stored procedure we’ll be using:

CREATE OR ALTER PROCEDURE 
    dbo.FindMeSomePostsPlease 
(
    @ParentId int
)
AS 
SET NOCOUNT, XACT_ABORT ON;
BEGIN

    SELECT
        c = COUNT_BIG(*)
    FROM dbo.Posts AS p
    WHERE ISNULL(p.ParentId, 0) = @ParentId

END;

Bruise the lard that our optimizer can match expressions on its own.

No Index, No Cry


Using the calls that have worked to trigger the PSP optimization in the past, we do not get two important things:

  • Different cardinality estimates between executions
  • The option(plan per value... text added to the end of the plan
EXEC dbo.FindMeSomePostsPlease 
    @ParentId = 0;

EXEC dbo.FindMeSomePostsPlease 
    @ParentId = 184618;

The plans are expectedly disappointing:

SQL Server Query Plan
today, satan

Indexable


With the help of our trusty steed index, we get the PSP optimization:

CREATE INDEX 
    p
ON dbo.Posts
(
    WhatIsIt
)
WITH
(
    SORT_IN_TEMPDB = ON,
    DATA_COMPRESSION = PAGE
);

Granted, this is a toy example, but…

SQL Server Query Plan
go time

Bottom line: If you want cool new features to (sometimes) work, you’re gonna need to pay attention to indexes.

What Else About Indexes?


Well, two things. In the example above, PSP didn’t kick in until we had one, I think owing to the computed column not being materialized, but…

Sometimes you’ll get PSP without an index, but the plans won’t be much different aside from cardinality estimates

Here’s a quick example.

DECLARE
    @sql nvarchar(MAX) = N'',
    @po int = 2,
    @pa int = 184618
--184618
SELECT 
    @sql += N'  
SELECT TOP (5000)
    p.Id,
    p.OwnerUserId,
    p.Score
FROM dbo.Posts AS p
WHERE p.PostTypeId = @po
AND   p.ParentId = @pa
ORDER BY 
    p.Score DESC;
';

EXEC sys.sp_executesql
    @sql,
  N'@po int,
    @pa int',
    @po,
    @pa;

First, with no indexes:

SQL Server Query Plan
moving tops

Even though both queries have no choice but to scan the clustered index, we get slightly different plans for vastly different estimates. Sort of (ha ha ha) interestingly, if the Sort hadn’t spilled in the top plan, they would have around the same execution time.

Anyway, I’ve been experimenting with other things, but the results have been surprising. For instance, I thought key column order would push the optimizer towards marking one parameter or the other as sensitive, but that doesn’t happen.

Wild stuff.

Thanks for reading!

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 also available for consulting if you just don’t have time for that and need to solve performance problems quickly.



3 thoughts on “SQL Server 2022 Parameter Sensitive Plan Optimization: Indexing Matters

  1. It feels like they were gunshy after the adaptive memory grant mess, and they’re taking the opposite approach here: make the tiniest adjustments, and only under very, very limited circumstances.

    That’s okay, as long as they keep making the payments, but…

    1. It’s like ~I want to believe~ but I think this feature is going to be frustrating for kicking in when it doesn’t need to, not kicking in when it should, and causing a whole lot of confusion in the DMVs.

      At least so far. Who knows how aggressive they’ll be based on blogger feedback 😂

Comments are closed.