Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature

If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before. Up until now, you had to figure out a way around it. SQL Server 2022 solves this issue – almost completely – through a new feature called Parameter Sensitive Plan Optimization. Let’s have a look at it.

1. Parameter sniffing – what’s the issue

A very brief summary of what parameter sniffing actually is:

When executing a SQL Server statement, SQL creates a plan on how to execute your statement. This plan then gets cached in your server, and when you call the statement again, it will reuse that plan.

This has the big upside of saving you a lot of valuable CPU cycles, since it doesn’t have to recalculate a plan on every execution. On the other hand, when two different parameters require a completely different plan, you run into issues that we call “Parameter sniffing” which is a big downside.

2. Let’s give an example

In this example, I’ll be creating a new table to simulate data skew.

To start, we made sure we put our database on 2019 compatibility mode and create the needed objects for the demo.

/* Change Database name FunWithParameterSniffing to any database you want to work on */ 

ALTER DATABASE [FunWithParameterSniffing] SET COMPATIBILITY_LEVEL = 150 

GO 

 

/* Create the table we'll use in the demo */  

CREATE TABLE dbo.SnifSnif( Id int PRIMARY KEY IDENTITY(1,1), Reputation INT , WillItRun nvarchar(512))  

GO 

 

/* Fill the table with dummy data */  

 

INSERT INTO dbo.SnifSnif ( Reputation, WillItRun)  

SELECT TOP 10 0,'We will find out' 

FROM master.dbo.syscolumns SysCol CROSS JOIN master.dbo.syscolumns SysCol2 

 

INSERT INTO dbo.SnifSnif ( Reputation, WillItRun)  

SELECT TOP 1000000 1, 'Hopefully?' 

FROM master.dbo.syscolumns SysCol CROSS JOIN master.dbo.syscolumns SysCol2 

 

/* Create an index on the field we're going to work with */  

CREATE NONCLUSTERED INDEX NCI_Id ON dbo.SnifSnif(Reputation) 

GO

If we execute these statements adhoc this is what we can see:

SET STATISTICS TIME,IO ON 
SELECT id, WillItRun 
FROM dbo.SnifSnif 
WHERE Reputation = 0

SQL decided to look into the NCI we made, and then do a key lookup for the missing columns.

If we execute the same query, but we change the parameter to ‘Reputation = 1’

SET STATISTICS TIME,IO ON 

SELECT id, WillItRun 

 FROM dbo.SnifSnif 

 WHERE Reputation  = 1

We see the following:

SQL decided the best way to get the data is to just scan the entire table and return the data that way. The reason for this, in this case, is that an index seek on a couple of rows combined with a key lookup is a lot faster than just scanning the entire index.

At a certain point, the key lookup becomes too expensive and it’s just more efficient to scan the entire table, as seen in the second example.

3. Oh boy

Now, this gets interesting when you put this in a stored procedure, so let’s create one.

CREATE OR ALTER PROCEDURE dbo.SnifSnifByReputation 

  @Reputation int 

AS 

SELECT id, WillItRun 

FROM dbo.SnifSnif 

WHERE Reputation=@Reputation 

GO

It’s a very simple procedure that just executes the same query as we’ve done before, but we’re adding the Reputation input as a parameter of the stored procedure.

If we execute the following

/* DO NOT RUN THIS ON PRODUCTION */ 

DBCC FREEPROCCACHE 

GO 

EXEC dbo.SnifSnifByReputation @Reputation =0; 

GO 

EXEC dbo.SnifSnifByReputation @Reputation =1; 

GO

We can see the following query plans:

If we check the query cache, we can see that it reuses the same plan twice, for the different parameters.

/* Query cache */  

SELECT Cached_Plans.usecounts, sql_text.text, Query_Plans.query_plan 

-- SELECT *  

FROM sys.dm_exec_cached_plans Cached_Plans 

CROSS APPLY sys.dm_exec_sql_text(plan_handle) sql_text 

CROSS APPLY sys.dm_exec_query_plan(plan_handle) Query_Plans 

ORDER BY Cached_Plans.usecounts DESC

If we do the same, but in reverse order, we get a very expected result

DBCC FREEPROCCACHE 

GO 

EXEC dbo.SnifSnifByReputation @Reputation =1; 

GO 

EXEC dbo.SnifSnifByReputation @Reputation =0; 

GO

And the cache:

/* Query cache */  

SELECT Cached_Plans.usecounts, sql_text.text, Query_Plans.query_plan 

-- SELECT *  

FROM sys.dm_exec_cached_plans AS Cached_Plans 

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS sql_text 

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS Query_Plans 

ORDER BY Cached_Plans.usecounts DESC

This means that the first execution of your stored procedure is very important, as this will decide what plan will be used for all the executions of that stored procedure after that point.

Common fixes for this right now are often to add OPTION (RECOMPILE) to your query as a hint or even force the plans through the likes of query store to make sure your data skew doesn’t kill your performance. However, both require manual interventions by your DBA and both have their downsides as well.

This is where SQL 2022 steps in with a new feature called “Parameter Sensitive Plan Optimization”.

4. Parameter Sensitive Plan Optimization

Let’s change the compatibility of our database to 160 (SQL 2022), and let’s make sure this feature is enabled:

/* Turn on correct database settings needed */  

/* Change Database name FunWithParameterSniffing to any database you want to work on */ 

ALTER DATABASE [FunWithParameterSniffing] SET COMPATIBILITY_LEVEL = 160 

GO 

 

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON

And let’s execute the same code again:

/* DO NOT RUN THIS ON PRODUCTION */ 

DBCC FREEPROCCACHE 

GO 

EXEC dbo.SnifSnifByReputation @Reputation =0; 

GO 

EXEC dbo.SnifSnifByReputation @Reputation =1;

SQL used two different plans to execute the stored procedures! If we investigate the query cache, we can also see the following:

/* Query cache */  

SELECT Cached_Plans.usecounts, sql_text.text, Query_Plans.query_plan 

-- SELECT *  

FROM sys.dm_exec_cached_plans AS Cached_Plans 

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS sql_text 

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS Query_Plans 

ORDER BY Cached_Plans.usecounts DESC

Two different plans got saved, which means that each parameter will run as it should without making any changes. Now if we would execute this stored procedure more frequently, this is what we can see in the plan cache:

EXEC dbo.SnifSnifByReputation @Reputation =0; 

GO 

EXEC dbo.SnifSnifByReputation @Reputation =0; 

GO 

EXEC dbo.SnifSnifByReputation @Reputation =0; 

GO 

EXEC dbo.SnifSnifByReputation @Reputation =1; 

GO 

EXEC dbo.SnifSnifByReputation @Reputation =1; 

GO 

EXEC dbo.SnifSnifByReputation @Reputation =1; 

GO 

EXEC dbo.SnifSnifByReputation @Reputation =1; 

GO 

EXEC dbo.SnifSnifByReputation @Reputation =1; 

GO 

EXEC dbo.SnifSnifByReputation @Reputation =1; 

GO 

EXEC dbo.SnifSnifByReputation @Reputation =1; 

GO 

EXEC dbo.SnifSnifByReputation @Reputation =1; 

GO

SQL manages to reuse the plans that are stored in the cache. Now I’m sure all of you readers have noticed that we can see three different plans in the cache for this query, instead of the two that I highlighted, this is something new in the 2022 version, called the Dispatcher plan.

If we look at the query plan of that statement, we see the following:

How this works is that SQL will call the Dispatcher plan, then based on the parameter you’ve passed, it will choose the correct plan to give you the best execution for your parameter.

5. Conclusion

Parameter sniffing is something a lot of Microsoft SQL DBAs have had issues with in the past. It’s a very common issue we see with different clients. With this update, Microsoft is trying to fix issues that a lot of people are struggling with which is nice to see.

While playing around with this feature, however, I did notice that SQL is working with thresholds here. So, if your table is too small or your data skew is not bad enough, this feature might not even trigger. This is a good thing as it could bloat your query cache a lot if it stored multiple plans for every single query.

This is one of the features I look forward to a lot to seeing how we can use this in production workloads.