SQL Server 2022 and its new intelligent query processing

SQL Server 2022 was announced last November. It brings several improvements to query processing, namely in what Microsoft calls Intelligent Query Processing (IQP). If you are maintaining existing databases, these changes are worth getting familiar with. You don't need to rewrite your (SQL) code or stored procedures to benefit from these; many of them activate automatically once your database is running under a suitable compatibility level.

SQL Server 2019 already introduced several performance improvements: a batch-mode on rowstores, deferred compilation of table variables and approximate counts on distinct. SQL Server 2022 continues the trend with improvements aimed at real-world workloads that suffer from parameter sensitivity, complex plans or varying datasets.

One of the biggest new features is Parameter Sensitive Plan (PSP) optimization. If your queries behave differently depending on parameter values (for example: small vs. large result sets), SQL Server can now maintain multiple cached execution plans instead of forcing a single "one size fits all" plan.

This alone can help a lot of systems without you touching a single line of T-SQL.

Other improvements include:

• More stable cardinality estimation (CE) in complex queries
• Better feedback loops for memory grants
• Degree of parallelism (DOP) feedback

If you are upgrading from SQL Server 2016 or 2017, these improvements can feel impactful without doing anything else than just an upgrade. I recommend enabling the latest compatibility level in a test environment and observing your slow queries. The IQP features often activate automatically.

Happy querying!