Query Store was first introduced in SQL Server 2016 and it is amazing how far it has come in such a short time. You can already use Management Studio and Azure Data Studio to get detailed resource consumption for all your queries, such as CPU, memory, and I/O, for Query Performance Insight to give you a quick and efficient way to determine impact. The automation and built-in knowledge that is used to make your job easier is impressive. So much of that comes from community feedback. Microsoft goes out of their way to connect with community to find out what they need, how they can improve features and make things better for customers. In my opinion, they have done a spectacular job with these improvements!
The SQL Server team has improved on Query Store for 2022 again and made some great improvements for SQL 2022. Query Performance was originally introduced as a flight recorder for your queries. It uses a system that gathers query performance data and gives you insights into your work loads over time. In 2022 it is being used to build and expand new capabilities in intelligent query processing. To allow this to work well and be accurate, I would recommend you enable Query Store by default for new databases. In addition to providing hinting support, it will facilitate the ability to build new intelligent query processing scenarios and improve performance.
Note: An additional feature for those using Always on availability groups is that Query Store will run on the secondary not just the primary as was the case previously.
There are a number of areas of improvement in the query processing in SQL Server 2022. Some of these improvements include:
- Memory Grant Feedback which was first introduced in 2017 and improved for row mode in 2019. It has been improved so that the learning feedback that SQL Server does will be persistent and does not have to begin again at restart.
- Cardinality Feedback (CF) is for when you are reaching the end of life of a version and moving to a newer version or newer cardinality version. Sometimes that workload may regress in terms of performance when moving to a different version, particularly when jumping versions. Over the years the SQL Server team has experienced working with customers who have tackled these situations so when they detect those types of workloads regress, the CF will try out different model assumptions to improve the performance and if it works, the changes will be persisted.
- Degree of Parallelism Feedback (DOP) is as simple as it sounds. The degree of parallelism for your server or database and the DOP is measured as the feedback adjusts the DOP from the default to the optimal level until it reaches the ideal value
- Parameter Sniffing or Parameter Sensitive Plan Optimization.
When you have skewed data that gives widely different plans based on histograms on the number of rows returned, the plan cache will now store 2 plans to accommodate the skew in the plan data. The Query hash is the same for each row in the query plan table but the query plan hash is different. There is no longer a need to deal with the wide differences in data distribution by doing re-compiles.
It is exciting to have more new feature to try out with the private preview of SQL Server 2022. You can download your copy today at: aka.ms/getsqlserver2022
