SQL SERVER 2022 Features for Performance Optimization

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.

SQL SERVER 2022 Features for Performance Optimization sqlserver2022-800x278

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.

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

, , , ,
Previous Post
SQL SERVER – Measure Index Performance

Related Posts

2 Comments. Leave new

  • simplesqltutorials
    November 8, 2021 10:51 pm

    Nice to see some positive changes are coming out. Will be interesting to see what they do with TempDB specifically. Maybe it will be quicker access time to TempDB? I know anytime a query needs to access TempDB, that query will perform more slowly than if it didn’t. We’ll see!

    Reply
  • I’d love to know the details of the T-SQL changes/updates/enhancements/deprecations, which I can’t seem to find anywhere.

    Reply

Leave a Reply

Menu