T-SQL Tuesday #181: Query Store and its evolution

Comments 0

Share to social media

Query Store. This is my choice in relation to “What feature release made you say WOW!”, the subject of this SQL Tuesday.

A calendar with ornaments and snowflakes

Description automatically generated

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.

A diagram of a process

Description automatically generated

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

A screenshot of a computer

Description automatically generated

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.

A screenshot of a computer

Description automatically generated

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

Managing Query Store Hints

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

Article tags

Load comments

About the author

Dennes Torres

See Profile

Dennes Torres is a Data Platform MVP and Software Architect living in Malta who loves SQL Server and software development and has more than 20 years of experience. Dennes can improve Data Platform Architectures and transform data in knowledge. He moved to Malta after more than 10 years leading devSQL PASS Chapter in Rio de Janeiro and now is a member of the leadership team of MMDPUG PASS Chapter in Malta organizing meetings, events, and webcasts about SQL Server. He is an MCT, MCSE in Data Platforms and BI, with more titles in software development. You can get in touch on his blog https://dennestorres.com or at his work https://dtowersoftware.com