Ignite 2021 Microsoft announced the release of SQL Server 2022, I just want to touch on one of the features I’m excited about in this release
Parameter Sensitive Plan optimization
With SQL Server 2022’s Parameter Sensitive Plan optimization feature, SQL Server automatically enables the generation of multiple active cached plans for a single parameterized statement. These cached execution plans will accommodate different data sizes based on the provided runtime parameter values.
BugBear
Parameter sniffing has been a bane to DBA’s for years. In a nutshell; a parameterized query gets executed, the parameter it’s executed with needs to bring back lots of results, so the query optimizer says “Yeah, let’s do an index scan” and rightly so. Then the same query gets executed with a different parameter, this parameter only returns a few bits of data, so an index seek would be more appropriate, however, in the plan cache this query has already been told to do an index scan, so it does… a massive waste of resources.
Previously, the main ways of resolving issues with parameter sniffing were through indexing or adding the OPTION(RECOMPILE) to the SQL Query/Stored Procedure. These were often not ideal resolutions, as you can imagine, having to recompile your query for every execution is going to have its own overhead with performance.
Big Old Bloat
I’m really hoping Microsoft gets this right, it’s going to be interesting to see this in action when SQL Server 2022 is available for testing. however, I’ve got a sneaking suspicion that with SQL Server caching multiple plans for the same query we might start seeing plan cache bloat. for instance, if SQL Server is caching a new plan for each individual parameter, depending on how many variations of parameters there are you could end up with 100’s of plans for a single parameterized query. hopefully, this won’t be the case, and there would be some communication with the cardinality estimator to store the parameters in ranges or groups (or something).
Fingers crossed Microsoft. let’s hope this is the last we see of parameter sniffing!
Luke Salter 