Query Store. This is my choice in relation to “What feature release made you say WOW!”, the subject of this SQL Tuesday.
Query Store: The first release
Query Store was first released in SQL Server 2016, it’s getting closer to 10 years old, but we barely notice.
The starting idea was something simple, a common requirement and yet capable of making everyone say “WOW”.
Query tunning before query store was based on the current query activity, or the query plan cache.
If the query was not running at the moment of the analysis and it was also removed from the plan cache, it was very difficult to identify a problem. The nightmare of every DBA was when the user complained “the query was slower but got better” or “the query is intermittently slower”.
Query store changed everything: It keeps a history of query plans and execution results of the plans, allowing the DBA to identify what’s called a Query Plan Regression.
Query Plan Regression is when a query gets a new query plan, but the new query plan is worse than the previous one.
The capability to analyze the entire history of query plans used by the same query and how they perform along the time opened a world of possibilities.
Query Store Evolution
Each SQL Server version introduced new features, evolving the query tunning capabilities.
SQL Server 2017
Query Store became capable to capture query statistics together the history
In the UI, a Regressed Queries dashboard was introduced
SQL Server 2019
The automatic plan correction was present since the beginning, but it got way better in SQL Server 2019.
In this version, a new system table was introduced, query plan feedback. This table registers all possible plan regressions and is capable of automatically deciding to force a plan to fix it.
Azure SQL
Azure SQL introduced the concept of query store hints. We could create query hints and link them with queries inside a query store. In this way, we could change the behavior of queries in production apps without changing the apps.
From Monitoring to Action
Monitoring was just the beginning. The next step in the journey was to take actions based on the monitoring information.
This also continued to evolve on each version of SQL Server.
SQL Server 2017
We got the Memory Grant Feedback.
SQL Server became capable to identify when a query plan was using too much memory or not enough memory and change the recorded plan to use a different amount of memory.
SQL Server 2019
In this version the Memory Grant Feedback was expanded to work with Row Mode query plans and not only Batch Mode query plans anymore.
SQL Server 2022
This version brough many query optimization improvements and we can easily notice how query story was the root of their creation
DOP Feedback
Max Degree of Parallelism feedback. This feature depends on the query store to work. Based on the analysis of the query history.
This feature applies a query hint to the query, changing the query DOP, according to this analysis of the history in query store.
CE Feedback
Cardinality Estimator is the responsible to calculate the estimated number of rows for a query plan. There are many methods of estimation according to the details of the query.
The CE feedback feature analyses 3 methods and can apply a query hint as a result of this analysis. Of course, the analysis uses the query store history.
Feedback in Query Store
New DMVs were created to hold information about the feedback applied to the queries. This can be considered as part of the query store.
How to follow this progress
Articles
Query Store and Parameterization Problems
SQL Server 2017: New ‘Queries with high variation’ Graph
Query Store and hints: More Powerful than ever
Online Conference Playlist
I organized the Malta SQL Server 2022 Query Optimization conference. It was a planned sequence of sessions talking about the evolution of query optimization since SQL Server 2016 up to SQL Server 2022.
Each session was delivered by a different speaker, many MVPs.
Videos
Achieving Wonders with Query Store
Solving Big Problems with Query Store Hints
Summary
Query Store age, close to 10 years, is not so important. It became the core of very recent query optimization techniques and it’s important to understand the evolution since SQL Server 2016 to better understand the internals of these features.
SQL Tuesday #181
This post is part of SQL Tuesday #181, replying to the invitation.
This is the first SQL Tuesday happening together the Festive Tech Calendar.
This year the Festive Tech Calendar is making a fundraising effort for Beatson Cancer Charity
Load comments