When Microsoft introduced Intelligent Query Processing in SQL Server 2019 and expanded it in SQL Server 2022 and 2025, the message was simple: upgrade, enable the right compatibility level, and the optimizer will quietly make your queries faster. Features like batch mode on rowstore, memory grant feedback, scalar UDF inlining, and parameter-sensitive plan (PSP) optimization all promise “automatic performance.”
But buried in Microsoft’s documentation is a reality worth understanding: Some IQP features can reduce or discontinue feedback when performance becomes unstable. This is intentional. IQP includes guard rails—safety mechanisms that change or stop certain feedback behaviors if they prove counterproductive.
This post explains why these guard rails exist and which features they affect. In Part 2, we will walk through exactly how to detect when a feature adjusts or stops applying its feedback.
Query processing feedback and stability
Microsoft documents scenarios where feedback-based features adjust their behavior when repeated regressions or oscillations occur. Query Processing Feedback governs several IQP behaviors, including memory grant feedback, degree-of-parallelism feedback, and cardinality estimation feedback.
From Microsoft’s documentation on Memory Grant Feedback:
- “Feedback persistence is temporarily disabled if there are too many consecutive performance regressions.”
- “For repeated oscillations, memory grant feedback stops applying adjustments.”
These are concrete examples of guard-rail behavior where SQL Server abandons or suspends feedback when it is no longer beneficial.
Memory grant feedback includes disabling logic
Memory grant feedback adjusts excessively large or small memory grants across executions. When SQL Server detects patterns such as repeated regression or oscillation, the feedback behavior may be suspended for stability.
Batch mode on rowstore is not guaranteed
Batch mode on rowstore (BMoR) is one of the most widely promoted IQP features. Microsoft documents the feature and its applicability, but does not guarantee that batch mode will always appear in a given execution plan. Its use depends on whether the optimizer determines that batch mode applies to the operators and plan shape.
Scalar UDF inlining has strict safety checks
Scalar UDF inlining can dramatically improve performance, but Microsoft lists many scenarios where inlining is prohibited—including cases added through cumulative updates to prevent incorrect behavior.
Microsoft states:
“The optimizer may decide not to inline a scalar UDF…”
“Inlining will be skipped if certain constructs are detected.”
PSP optimization is not always applied
Parameter-Sensitive Plan Optimization (PSP) solves many parameter sniffing problems—but only when queries meet the documented eligibility criteria.
Microsoft states:
“PSP optimization is only applied to eligible statements.”
Where to read the fine print
If you want to go deeper into the official details, here are the Microsoft documentation pages for each feature discussed:
-
Intelligent Query Processing (Overview)
https://learn.microsoft.com/.../intelligent-query-processing -
Memory Grant Feedback (Row Mode)
https://learn.microsoft.com/.../memory-grant-feedback-row-mode -
Memory Grant Feedback (Batch Mode)
https://learn.microsoft.com/.../memory-grant-feedback-batch-mode -
Scalar UDF Inlining
https://learn.microsoft.com/.../scalar-udf-inlining -
Parameter Sensitive Plan Optimization
https://learn.microsoft.com/.../parameter-sensitive-plan-optimization -
Batch Mode on Rowstore
https://learn.microsoft.com/.../batch-mode-on-rowstore
In Part 2, we look at how to detect when SQL Server has adjusted or suspended feedback behavior—using DMVs, extended events, showplan attributes, and more.
No comments:
Post a Comment