I am sure by this time you all are aware that Microsoft has announced SQL Server 2022. It will be released sometime next year. The release date is not announced.

There are lots of features announced for SQL Server 2022. However, I am interested in the features which are primarily released Performance Optimization.

Query Store and Intelligent Query Processing

One of the most interesting changes is that Query Store will be on by default. Currently, when you install a new SQL Server 2019 or an earlier version of SQL Server, it is off by default. Now it will be ON and also it is equipped with more features.

In SQL Server 2019, there will be more enhancements to Intelligent Query Processing. It will now start giving feedback on Cardinality Estimation (CE) as well as Max Degree of Parallelism (MAXDOP). This is indeed going to be extremely interesting as currently lots of my clients who hire me for Comprehensive Database Performance Health Check are suffering from these two issues.

It seems now Query Store will have the capability to give feedback about CE and MAXDOP. I personally can’t wait to see them in action.

As a consultant, I am extremely delighted that from now onwards during Health Check, I will not have to focus on the above issues and I would rather be able to focus on more important optimization issues.

One more thing which is interesting in Intelligent Query Processing is Parameter Sensitive Plan Optimization.

Parameter Sensitive Plan Optimization

If I have to say this in simple words, it will be a resolution to Parameter Sniffing related performance issue. Now do not think Parameter Sniffing is bad at all. In the real world, parameter sniffing has been fine the most of time. However, once in a while the situation arises when the query or stored procedure builds and caches the execution plan with the parameter which is not optimal. There have been many solutions to this issue but none is perfect.

The most interesting part is that from now onwards SQL Server will cache multiple plans for multiple parameters which have a different execution plan. It will be interesting to see how SQL Server does it internally and how it avoids creating the cache bloating issue. In any case, I am extremely excited that the Microsoft team has finally taken some good steps in this direction.

TempDB and Latch

When I am doing SQL Server Performance Tuning, 1 out of 10 clients is struggling with TempDB and Latch issues. There are multiple ways to resolve this issue, however, it would be nice to not have this issue from the beginning. Finally, in SQL Server 2022, there are a few enhancements are coming through for this area.

Well, I will keep you updated more about SQL Server 2022 as more information is available.

Let me summarize how you can stay in touch with me if you want my content every single day.

• Subscribe to YouTube Channel.
• Subscribe to Newsletter.
• Keep reading SQLAuthority.com

Reference: Pinal Dave (https://blog.sqlauthority.com)