Parameter Sensitive Plan (PSP) optimization, new in SQL Server 2022 (compatibility level 160+)
as part of Intelligent Query Processing. It’s Microsoft’s built-in fix for exactly the scenario you built: ParentId in Posts is wildly skewed (a handful of parent posts have thousands of answers, most have very few), so one cached plan can never be right for both a rare value and a common one.
Before 2022, a single cached plan was reused for every execution regardless of the parameter value, so you’d either get a nested-loop plan optimized for a small @ParentId that fell over when a huge one came through, or vice versa — the classic “it’s fast when I run it alone, slow from the app” problem.
The usual workarounds were:
a) OPTION (RECOMPILE);
b) OPTIMIZE FOR UNKNOWN plan guides;
c) manually branching the proc with IF (I like this one use an index hint);
d) dynamic sql.
PSP instead lets the optimizer keep several plans cached for the same statement and pick between them at execution time. On first compile, it looks at the histogram for the sniffed predicate (Posts.ParentId),
and if it sees a skewed distribution it builds a small “dispatcher” plan plus up to three query variants, bucketed into low/medium/high cardinality ranges. At runtime, the dispatcher evaluates which bucket the actual parameter value falls into and routes execution to the matching variant — compiling it fresh
the first time that bucket is hit, then reusing it.
That’s what is showing: ParentId = 184618 and ParentId = 0 landed in different cardinality buckets (0 is presumably a huge bucket since it likely means “no parent,” i.e. top-level posts — extremely common — while 184618 is a normal, rare parent id), so each got its own QueryVariantID and its own plan optimized for that range. The predicate_range(…, 100.0, 1000000.0) and PLAN PER VALUE(…) bits are PSP-generated hints you can’t write by hand; they just record which cardinality range that particular variant is valid for.
This does not work (4 and 10) , but you could alter the statistics of the column so the buckets change.
create or alter proc dbo.pcl_ParameterRelated_Loops2 @ParentId int
as
begin
select top(10) u.DisplayName, p.*
from dbo.Posts p
join dbo.Users u
on p.OwnerUserId = u.id
where p.ParentId = @ParentId
order by u.Reputation desc
option (PLAN PER VALUE(ObjectID = 1634104863
, QueryVariantID = 4
, predicate_range([StackOverflow2013].[dbo].[Posts].[ParentId] = @ParentId, 10.0, 100.0, 1000000.0)))
end
Keep updating your statistics!
use StackOverflow2013
go
exec dbo.DropIndexes;
dbcc freeproccache;
create index ix_posts on dbo.Posts (ParentId, OwnerUserId);
create or alter proc dbo.pcl_ParameterRelated_Loops @ParentId int
as
begin
select top(10) u.DisplayName, p.*
from dbo.Posts p
join dbo.Users u
on p.OwnerUserId = u.id
where p.ParentId = @ParentId
order by u.Reputation desc
--option (use hint('DISABLE_PARAMETER_SENSITIVE_PLAN'));
end
exec dbo.pcl_ParameterRelated_Loops @ParentId = 184618;
exec dbo.pcl_ParameterRelated_Loops @ParentId = 0;
exec sp_BlitzCache @StoredProcName = 'pcl_ParameterRelated_Loops'
create or alter proc dbo.pcl_ParameterRelated_Loops2 @ParentId int
as
begin
select top(10) u.DisplayName, p.*
from dbo.Posts p
join dbo.Users u
on p.OwnerUserId = u.id
where p.ParentId = @ParentId
order by u.Reputation desc
option (PLAN PER VALUE(ObjectID = 1634104863
, QueryVariantID = 4
, predicate_range([StackOverflow2013].[dbo].[Posts].[ParentId] = @ParentId, 10.0, 100.0, 1000000.0)))
end
/*
Query 1: Query cost (relative to the batch): 100%
select top(10) u.DisplayName, p.*
from dbo.Posts p
join dbo.Users u
on p.OwnerUserId = u.id
where p.ParentId = @ParentId
order by u.Reputation desc
option (PLAN PER VALUE(ObjectID = 1634104862
, QueryVariantID = 3
, predicate_range([StackOverflow2013].[dbo].[Posts].[ParentId] = @ParentId, 100.0, 1000000.0))
)
*/