At Least For Now
SQL Server 2022 has a new feature in it to help with parameter sensitive query plans.
That is great. Parameter sensitivity, sometimes just called parameter sniffing, can be a real bear to track down, reproduce, and fix.
In a lot of the client work I do, I end up using dynamic SQL like this to get things to behave:
But with this new feature, you get some of the same fixes without having to interfere with the query at all.
How It Works
You can read the full documentation here. But you don’t read the documentation, and the docs are missing some details at the moment anyway.
- It only works on equality predicates right now
- It only works on one predicate per query
- It only gives you three query plan choices, based on stats buckets
There’s also some additional notes in the docs that I’m going to reproduce here, because this is where you’re gonna get tripped up, if your scripts associate statements in the case with calling stored procedures, or using object identifiers from Query Store.
For each query variant mapping to a given dispatcher:
query_plan_hashis unique. This column is available in
sys.dm_exec_query_stats, and other Dynamic Management Views and catalog tables.
plan_handleis unique. This column is available in
sys.dm_exec_cached_plans, and in other Dynamic Management Views and Functions, and catalog tables.
query_hashis common to other variants mapping to the same dispatcher, so it’s possible to determine aggregate resource usage for queries that differ only by input parameter values. This column is available in
sys.query_store_query, and other Dynamic Management Views and catalog tables.
sql_handleis unique due to special PSP optimization identifiers being added to the query text during compilation. This column is available in
sys.dm_exec_cached_plans, and in other Dynamic Management Views and Functions, and catalog tables. The same handle information is available in the Query Store as the
last_compile_batch_sql_handlecolumn in the
query_idis unique in the Query Store. This column is available in
sys.query_store_query, and other Query Store catalog tables.
The problem is that, sort of like dynamic SQL, this makes each different plan/statement impossible to tie back to the procedure.
What I’ve Tried
Here’s a proc that is eligible for parameter sensitivity training:
CREATE OR ALTER PROCEDURE dbo.SQL2022 ( @ParentId int ) AS BEGIN SET NOCOUNT, XACT_ABORT ON; SELECT TOP (10) u.DisplayName, p.* FROM dbo.Posts AS p JOIN dbo.Users AS u ON p.OwnerUserId = u.Id WHERE p.ParentId = @ParentId ORDER BY u.Reputation DESC; END; GO
Here’s the cool part! If I run this stored procedure back to back like so, I’ll get two different query plans without recompiling or writing dynamic SQL, or anything else:
EXEC dbo.SQL2022 @ParentId = 184618; GO EXEC dbo.SQL2022 @ParentId = 0; GO
It happens because the queries look like this under the covers:
SELECT TOP (10) u.DisplayName, p.* FROM dbo.Posts AS p JOIN dbo.Users AS u ON p.OwnerUserId = u.Id WHERE p.ParentId = @ParentId ORDER BY u.Reputation DESC OPTION (PLAN PER VALUE(QueryVariantID = 1, predicate_range([StackOverflow2010].[dbo].[Posts].[ParentId] = @ParentId, 100.0, 1000000.0))) SELECT TOP (10) u.DisplayName, p.* FROM dbo.Posts AS p JOIN dbo.Users AS u ON p.OwnerUserId = u.Id WHERE p.ParentId = @ParentId ORDER BY u.Reputation DESC OPTION (PLAN PER VALUE(QueryVariantID = 3, predicate_range([StackOverflow2010].[dbo].[Posts].[ParentId] = @ParentId, 100.0, 1000000.0)))
Where Things Break Down
Normally, sp_BlitzCache will go through whatever statements it picks up and associate them with the parent object:
EXEC sp_BlitzCache @DatabaseName = 'StackOverflow2010';
But it doesn’t do that here, it just says that they’re regular ol’ statements:
The way that it attempts to identify queries belonging to objects is like so:
RAISERROR(N'Attempting to get stored procedure name for individual statements', 0, 1) WITH NOWAIT; UPDATE p SET QueryType = QueryType + ' (parent ' + + QUOTENAME(OBJECT_SCHEMA_NAME(s.object_id, s.database_id)) + '.' + QUOTENAME(OBJECT_NAME(s.object_id, s.database_id)) + ')' FROM ##BlitzCacheProcs p JOIN sys.dm_exec_procedure_stats s ON p.SqlHandle = s.sql_handle WHERE QueryType = 'Statement' AND SPID = @@SPID OPTION (RECOMPILE);
Since SQL handles no longer match, we’re screwed. I also looked into doing something like this, but there’s nothing here!
SELECT p.plan_handle, pa.attribute, object_name = OBJECT_NAME(CONVERT(int, pa.value)), pa.value FROM ( SELECT 0x05000600B7F6C349E0824C498D02000001000000000000000000000000000000000000000000000000000000 --Proc plan handle UNION ALL SELECT 0x060006005859A71BB0304D498D02000001000000000000000000000000000000000000000000000000000000 --Query plan handle UNION ALL SELECT 0x06000600DCB1FC11A0224D498D02000001000000000000000000000000000000000000000000000000000000 --Query plan handle ) AS p (plan_handle) CROSS APPLY sys.dm_exec_plan_attributes (p.plan_handle) AS pa WHERE pa.attribute = 'objectid';
The object identifiers are all amok:
Only the stored procedure has the correct one.
The same thing happens in Query Store, too:
EXEC sp_QuickieStore @debug = 1;
The object identifiers are 0 for these two queries.
One Giant Leap
This isn’t a complaint as much as it is a warning. If you’re a monitoring tool vendor, script writer, or script relier, this is gonna make things harder for you.
Perhaps it’s something that can or will be fixed in a future build, but I have no idea at all what’s going to happen with it.
Maybe we’ll have to figure out a different way to do the association, but stored procedures don’t get query hashes or query plan hashes, only the queries inside it do.
This is gonna be a tough one!
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 Parameter Sensitive Plan Optimization: The Problem With Sniffed Parameter Sensitivity
- How SQL Server 2022’s Parameter Sensitive Plan Feature Can Make Query Store Confusing
- SQL Server 2022 Parameter Sensitive Plan Optimization: Does PSP Work With Dynamic SQL?
- Some Notes on sp_prepare And SQL Server 2022