Parameter Sensitive Plan optimization (Intelligent Query Processing) – SQL Server 2022

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!

Memory Grant Feedback (Intelligent Query Processing) – SQL Server 2019

Row Mode Memory Grant Feedback was a new feature introduced in SQL Server 2019, it expands on the existing Batch Mode Memory Grant Feedback which was introduced in SQL Server 2017.

Okay so what is it?

After a query executes, the query’s post-execution plan includes ideal memory grant size to have all rows fit in memory. Performance suffers when memory grant sizes are incorrectly sized. Excessive grants result in wasted memory and reduced concurrency. Insufficient memory grants cause expensive spills to disk.

With Memory Grant Feedback; Consecutive executions of a query will result in SQL Server adjusting the memory grant to better allocate it’s resources, reducing excessive grants, or preventing spills to disk.

There are now two new query plan attributes will be shown for actual post-execution plans IsMemoryGrantFeedbackAdjusted and LastRequestedMemory.

LastRequestedMemory shows the granted memory in Kilobytes (KB) from the prior query execution. IsMemoryGrantFeedbackAdjusted attribute allows you to check the state of memory grant feedback for the statement within an actual query execution plan. Values surfaced in this attribute are as follows:

IsMemoryGrantFeedbackAdjusted Value	Description
No: First Execution	Memory grant feedback does not adjust memory for the first compile and associated execution.
No: Accurate Grant	If there is no spill to disk and the statement uses at least 50% of the granted memory, then memory grant feedback is not triggered.
No: Feedback disabled	If memory grant feedback is continually triggered and fluctuates between memory-increase and memory-decrease operations, we will disable memory grant feedback for the statement.
Yes: Adjusting	Memory grant feedback has been applied and may be further adjusted for the next execution.
Yes: Stable	Memory grant feedback has been applied and granted memory is now stable, meaning that what was last granted for the previous execution is what was granted for the current execution.

This sounds like a great feature!

It is, for the most part. There are some things to be careful with; with parameterised query’s the the result set can change dramatically. resulting in quite large discrepancies for memory grants between executions. now as you can see “If memory grant feedback is continually triggered and fluctuates between memory-increase and memory-decrease operations, we will disable memory grant feedback for the statement” This can cause some performance issues while the the database engine is figuring this part out, so there will be some executions where memory grants are either too large, or there are large spills to disk.

Then, you need to be careful for clearing your plan cache, once you clear your plan cache, SQL will forget about all the hard work it’s done for this parametrised query, resulting in poor performance for a short time.

Cant we just disable it and save us the trouble?

You can, but it’s my view that this feature, for the moment is giving us more help than hindrance. I haven’t seen any cases yet where disabling this option would be recommended for a server… I guess there are some edge cases, where absolutely every query you have is parametrised and your result sets are fluctuating consistently… then yes this feature wouldn’t be useful! but then you would be back to regular parameter sniffing issues.

Row mode memory grant feedback can be disabled at the database or statement scope while still maintaining database compatibility level 150 and higher. To disable row mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

To re-enable row mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;