Query Store and Query Optimization on SQL Server 2017

Target Audience:

SQL Server administrators and developers.


In this session, I will talk about advantages of Query Store, features, configuration and query optimization using Query Store on SQL Server 2017. The session will also cover how one can monitor query performance, how Query Store gathers data and which are the best practices for using Query Store. During the session, there will be various demos.

Audio podcast:

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS
Leave us a review in iTunes

Session Transcript:

Brent Ozar: So alright everybody, welcome to GroupBy. The first session this morning, Dean, will be talking about query store and Pinal will be in with him asking questions. So take it away, Dean.

Introduction and Overview

Dean Savović: Thank you, Brent. Hi to everyone. My name is Dean Savović, I’m from Croatia, Zagreb, and I work for Comminus. This is some stuff that we do, and I’m head of data management department in Comminus company. I’ll be talking about query store and query optimization on SQL Server 2017.

Getting started

We will talk about what is the main purpose of the query store, what’s the usage of query store, we will go through the overview, data capture model, DMV model, configuration, and reports that come with query store, and we will have a demo at the end of the presentation where we will see some cool stuff about query store.

The main purpose of the query store when it came out in SQL Server 2016 was to find and fix a plan performance regressions in the SQL Server database. It’s very easy with query store to find and plan performance regressions with the plan performance regressions report, and you can fix the queries by forcing query plan right within the query store report. It is also used to determine the number of times the query was executed in a given time window. You can identify top band queries by whatever you want, execution time, memory consumption, et cetera. You can audit the history of the query plan, you can analyze the resources that are used by the SQL Server and your database, like CPU, input, output, memory, and so on. And you can identify usage patterns for a particular database that has query store enabled. You can also in SQL Server 2017 understand the wait nature for a particular query or plan because SQL Server 2017 introduced wait stats that are collected in the query store.

Using Query Store for A/B testing

A/B testing with Query Store

You can use query store for A/B testing. What is A/B testing? You have A situation and B situation, first of all, you produce A situation and let the query store collect the data. And then you go to the B situation and then compare the query store that collected the data and the B situation with the query store that collected the data in the A situation. This is A/B testing.

You can use it for keeping the performance stability during the upgrade to newer SQL Server, for example, which we will see in the next slide. And there is wait stats, as I mentioned in SQL Server 2017, and automatic tuning, which is also a feature of SQL Server 2017.

This is an example of A/B testing when upgrading from a lower version of SQL Server to the SQL Server 2016 or 2017. You first upgrade to SQL Server 2016 or 2017 and keep the source database compatibility level to the version that it was before the upgrade. Then you enable the query store on the database. Then you wait to collect the data on the workload. You will create a baseline basically. And then you set the database compatibility level to the latest, to the SQL Server 2016 or 2017. Then let the query store again, collect the data, and then using the query store you can quickly find and fix regressions by forcing last known good plan.

How Query Store works

Data capture

This is a data capture model for the query store. We all know that SQL Server first has to compile the SQL query that it wants to execute, and then he executes it. When the SQL Server query is compiled, the compiled message is sent to the query store and when it’s executed, the execute message is sent to the query store. The query store captures data about the query plan, the run time statistics, the wait statistics, and so on. This table, plan store, wait stats, and runtime stats are in-memory tables, and they are flushed to the disk, to the query store schema in the interval that is defined by the DATA_FLUSH_INTERNAL_SECONDS. And this write-back is asynchronous.

Query store and query execution looks like – the schema of the query execution looks like this.

Query execution

Firstly, enter a query that you want to execute. The SQL Server does a plan cache lookup. If the query is found in the cache, it is fetched from the cache, and if there is a forced plan for that query, it is applied. If the plan cache lookup doesn’t find a query, the SQL Server has to compile and optimize the query, but it also checks if there is a forced plan for that query. If there is a forced plan for that query, SQL Server applies that forced plan and executes the query with the forced plan. And when the execution is finished, SQL Server sends the execution to disk, later to the query store.

Querying the Query Store DMVs

These are the DMVs that are available to you when you enable query store on the database. The first one is sys.database_query_store_options, which only records all the configuration options that are defined on the database. Then there is sys.query_store_query, which has ANSI options. sys.query_store_query_text which has query text of the SQL statement, sys.query_context_settings that has a context settings for SQL statement that is executed, sys.query_store_plan, very interesting DMV that has execution plans for the related query.

Then there are two DMVs with runtime statistics. First one is sys.query_store_runtime_stats, and the second one is sys.query_store_runtime_stats_interval. They have the runtime statistics based on the interval that is defined on the query store. The first one has it by query plan, and the second one has the overall runtime statistics on the data which is level and on the interval level. And there is sys.query_store_wait_stats DMV that has wait statistics for the query being executed and captured by the query store.

Enabling and configuring Query Store

How do you enable query store? It’s very easy. You can use ALTER DATABASE statement, name of the database and set query store on. You will enable the query store with the default options, and you can use the GUI of the SQL Management Studio, right click to database, click database properties and on the query store tab, change the operation mode from off to on. So it’s very easy.

These are the configuration options that are configurable by you. All of them are configurable by T-SQL options, and the only one that is not configurable by the GUI is MAX_PLANS_PER_QUERY, the last one. The default value for that is 200, so that means that there can’t be captured 200 plans per query, which is more than enough for standard database log.

The first one is OPERATION_MODE, which can be on, off, or read-only. I will come back to that in a second to explain the read-only mode. DATA_FLUSH_INTERVAL_SECONDS, we already explained. And there is INTERVAL_LENGTH_MINUTES, which defines the aggregation interval in the query store, which is used to aggregate the data in the query store. MAX_STORAGE_SIZE_MB, it’s a very important configuration option because you have to know that query store is stored in the primary filegroup. So it is very advisable for you to limit the storage of the query store and if the query store hits limit of the storage, it will automatically go to the read-only mode. So that means that no further data is captured in the query store.

QUERY_CAPTURE_MODE can be all or auto. If set to all, then all queries are captured in the query store. If set to auto, then only significant queries will be captured in the query store. What you probably are asking yourself, what are significant queries, well, books online says that those are queries that execute more than once and queries that use a significant amount of resources on the SQL Server. So queries that everyday execute only once or do not use a significant amount of resources will not be captured when QUERY_CAPTURE_MODE is set to out. Then you have SIZE_BASED_CLEANUP_MODE, which if set to on, it can be set to on or off, and it should be set to on if you use MAX_STORAGE_SIZE_MB. The query store will automatically clean up the oldest queries and plans from the query store when it hits the 80% of the max storage size in megabytes. Then you have CLEANUP_POLICY, which cleans up all the queries, all within a given number of days. Now you most probably ask yourself, is there a performance impact on the SQL Server and the database that has the query store set to on? Well, there is, and books online says that there is a performance impact of 3% to 5% on memory, on all resources. CPU memory, input, output, and so on.

Query store helps itself to minimize the performance input with asynchronous writes. We already mentioned that there are some in-memory tables that have the fresh data of the query store captured, and this data is asynchronously written to the disk-based tables in the query store. Also, when you enable query store, you have query store performance counters, query store extended events, and query store wait statistics that can help you identify how much the query store is hurting your server or database when set to on.

Reporting on Query Store data

There are some reports that are included in the SQL Server Management Studio. SQL Server 2016 and 2017. There are six of them in Management Studio 17.3 and four of them in the SQL Server Management Studio 2016. The first of them is regressed queries report, which shows you regressed queries. So the queries that ran okay for some time and then regressed and performed badly in a way that duration went up or something like that. You can very easily with this report find these queries, and then if you wish you can force a last known good plan for that query. We’ll show that in a bit.

There is an overall resource consumption report, which tells you the resource consumption of your database, which has the query store set to one. This data is aggregated on the interval level, and you can get an overview of the – how your database is performing in a specific time interval. Then you have top resource consuming queries, where you can very easily get the queries that are consuming most resources. Resources like CPU, duration, memory, input, output, logical reads, logical writes, and so on.

And then you have tracked queries report, which gives you details about a particular query. You have to know the ID of the query for which you want to run this report, and this ID can be identified by the top resource consuming queries, or regress queries report. Then you have queries with forced plans, so when you force a plan for some query, this query will show up in this report, and at the end you have queries with high variation, which means that all the unstable queries on your database and SQL Server will show up here.

Plan forcing and tuning

I already mentioned that you can force a plan using the query store. On this slide, you can see the query store report, part of it, and the confirmation message on the right, which says, “Do you want to force plan 146 for query 136?” If you say yes, you will force a plan for a query, and from now on this query will have this plan. You will most probably know that before query store, if you wanted to force a plan, you had to use the stored procedure sp_force_plan. Sp_create_plan, sorry, and you had to provide the exact query text and exact query plan, in XML format, which you want to force. It was not so easy to get that plan because this is a plan that is not being compiled by SQL Server, and you had to find a way to produce that plan. So it was not so easy. With the query store, forcing a plan is very, very easy. You just click one button, force plan, and the plan is forced.

Automatic tuning was also mentioned in the first slide of this presentation. On SQL Server 2017, on-premise, you can use alter database, name of the database, set automatic tuning and force last good plan set to on. If you do that, then the SQL Server will automatically, if he finds the reverse query, he will automatically force the last good plan of that query.

In the Azure, not on SQL Server 2017 on-premise, you have a feature that is called automatic index management, which means that SQL Server is automatically creating indexes that he thinks that will be useful for your data log.

Trace flags to control Query Store behavior

There are some trace flags I would like to mention to you, about the query store. These trace flags are 7745 and 7752. I already mentioned that we have a data flush interval seconds configuration parameter that asynchronously writes data from in-memory query store tables to disk-based tables. When you shut down the SQL Server, by default, if this trace flag is not set to one, the SQL Server will wait until this interval in seconds is finished to shut down. If you set this trace flag to on, then SQL Server will not wait. So you will lose some data that is in-memory query store tables, but your SQL Server will be faster in his shut down.

Also if you have a query store trace flag 7752, which means if you set to on, when you start SQL Server, SQL Server will not wait for memory-optimized tables to be loaded. So SQL Server runs startup if query store is set to on, load its memory-optimized tables of the query store from the disk-based tables. If you set this trace flag to on, then this wait will not happen.

Questions about Query Store

Pinal Dave: Before you move forward, there are a few questions I just thought I would just ask you, because it’s right in the middle of the session, you’re almost 21 minutes. … So a question – I think two questions are related to slide number 16, and one of them is, I think he’s either making fun of us are I think it’s a genuine question, we do not know. So we’ll still try to answer it. The question is that when you enable force last good plan on, what happens if there is no plan available before?

Dean Savović: If there is no plan available before then the SQL Server cannot conclude that there is a regression in query plans. So…

Pinal Dave: So – yes, he’s making fun of us. Let’s go – okay, we are anyway having fun, and the second question about automatic index management. I think one person, he’s just saying, “Isn’t it also available in SQL Server 2017 and not only in Azure?”

Dean Savović: No, this feature is for now only available in Azure.

Pinal Dave: I see. Okay. So any plan – so this is a question from me. If that is only available in Azure, so do you think it will eventually roll into the premises or we don’t have any idea?

Dean Savović: I don’t have an idea, but it was the practice of Microsoft to first release features in Azure and then put it on the SQL Server on-premise. So I think that this feature will also be included in some of the future releases of SQL Server, but I don’t know.

Pinal Dave: No worries, and what about the slide number 17, there is a last and final question, I think we can get back to the presentation in about line number – the trace flag about 7752. It says, “On starting SQL Server, do not wait for memory-optimized data to be loaded”, so on starting it does not, but does it ever do in a future course or it never does it?

Dean Savović: It loads the memory optimized table later, but it doesn’t wait to start operating.

Pinal Dave: Alright, so that’s fantastic answer. And one just while we were discussing this, one just showed up in our Slack is the final question. Has any known gotcha from automatic tuning?

Dean Savović: Any known sorry?

Pinal Dave: Any known troublemakers or anything we should be aware if we are just enabling automatic tuning? Like is there any negative effect of it, or we just enable it and suddenly everything gets slow maybe because we do not know something or we didn’t take care of something before we enable it.

Dean Savović: Well, I have to say that this is very generic and general question. I don’t know how to answer it.

Pinal Dave: So maybe we can say that it’s too new so even we figure we do not know yet because it’s too new feature and we haven’t even practiced it yet most probably that’s the case, right?

Well, we didn’t use it on any of our systems because we don’t have query store on SQL Server 2017.

Pinal Dave: I see, yes, so I think let’s pass on it and let’s get back to our original presentation. I think we took care of all the questions on Slack, Twitter, as well as what we got in our question and answer. So thank you Dean.

Queries for the Query Store DMVs

Dean Savović: Okay. So let’s get back to the presentation. This is an example of the query using DMVs that we discussed earlier. There is a sys.query_store_plan DMV, the first DMV after the from clause, that has the query store plan. Then we have sys.query_store_query, sys.query_store_text, sys.query_store_runtime_stats to get all the data we need to produce this query. This query produces the query text, so the text of the query, the execution plan of the query, and the average duration and source the data by average duration descending, and displays top ten queries that it finds. So it is very easy to write a query that gets you the top ten slowest queries in your system and if you have query store enabled.

The next query is exactly the same but it in the where clause has last_dop greater than one. Last_dop is degree of parallelism, and it gives us the queries that run in parallel. So it’s very easy to get queries that run in parallel and then if I don’t get the query text then the execution plan will get the queries.

Demoing Query Store reports

And now it’s time for demo. Just a second, let me connect to our test system where we have enabled the query store, and it’s collecting data for some time. So we have database Poliklinica that has enabled query store on it, and we have query store plan when we enabled query store, and the reports that I talked about during the session.

Let’s take a look at the regressed queries report. We have some queries inside with the query plans, which we can see at the bottom of the screen. You can configure this report to check regression by duration, CPU time, logical reads and so on, based on average, max, min, standard deviation, or total. You can take a look at the last hour, last 12 hours, last day, last month, or custom. And you can define the history for the report to find the duration against also.

You can configure every one of these reports, so take a look at – let’s take a look at the overall resource consumption report, where you can see the usage pattern of the duration, execution count, CPU time, logical reads, and so on. We see here that this system is not used all day long, it has some periods where it’s not used. We can also configure it.

Now let’s go to top resource consuming queries, which you can see by duration, execution count, CPU time. Let’s see it ordered by CPU time. So we have query text here – sorry, text, and query plans for this query. Queries with forced plans, we don’t have any queries with forced plans on this system. Queries with high variation, we have that here, queries that variate highly, that have high variation, sorry.

And at the end, you have tracked queries report, for which you have to know the query ID. Let’s see if query ID one exists on this system. No. Let’s go back to regressed queries and take the query ID 69. Okay, we have the query plans for that query, when it’s executed, the query plan for that query and so on.

Demoing finding bad queries

Now let’s go to the second part of the demo, which is on my local machine. I will connect to my SQL Server 2016 machine and open a very simple starting demo that uses database query store. Enables the query store on the database with the alter database statement, and configures the parameters. Operation mode with write, data flush interval seconds, interval length in minutes, and so on. So this is very simple and very easy.

Now I will prepare the AdventureWorks database for a second demo in which I will show you the regressed queries situation and how to force a plan with the query store. So I will set the query store to on, on AdventureWorks 2012 database. I will clear the query store without the database, name of the database, set query store clear, now let’s run a few queries. Select top 100 from person, address, and select top 100 from sales, sales order detail, just to show you how easily you can get the data from the query store about these queries. This query that you are seeing right now on screen gets the data from the query plan cache and from the query store. And I will – just want to show you that you can get the exact same data from the query plan cache. You use to before the query store was introduced in SQL Server 2016 and from the query store.

It’s also very easy to get the execution plan of a query. It’s in XML format so you can click on it, and get the estimated execution plan in your Management Studio. And here is the query we had on the slides, which select top ten queries sorted by average duration descending. And these are our queries, the query top 100 select from person, address, is the most expensive query by duration in this database.

Now, let’s go to the more interesting part of the demo, which will show you – which I will show you how to force a plan using your query store. I will use AdventureWorks 2012, clear the query store, then we have two queries. You have to believe me, these two queries are exactly the same. This one, and this one. The only difference is with this parameter. In first query, I am passing parameter 461, and in the second query I am passing parameter 712. This parameter influences how many records will the SQL Server return, and which plan will it use. Let me include the actual execution plan. I am clearing the PROCCACHE before executing the query so I don’t get parameter sniffing. This is not important for the first query, but for the second query it is because the queries are the same.

So this first one returns only one row, and it goes into index seek and index seek. The second index seek is by the AdventureWorks production transaction history archive table, by the index seek, transaction history archive product ID. The second query, I’m again clearing the PROCCACHE, is returning 1034 records and the execution plan is different. It has index seek and index scan by the second table.

Now let’s go to the query store. View regressed queries report. And we don’t see that query in this report because the history upon which the query store is calculating this is too far in the history so we don’t see it, but we can use the greet report in which we will see this query. This is our query, and we have two plans. The plan with the seek and scan, which took 250 and some more milliseconds, and we have a plan with seek and seek. Let’s say that we’ve decided this second plan is more better for this query, and we want to force it. So it’s very easy, just like I said, you can force a plan right within the query store report with force plan button. “Are you sure you want to force a plan?” Yes, and the plan is forced. If you want to unforce it, just select it and press unforce plan button.

Now let’s go back to the query window and execute the first query again, and see the execution plan. The execution plan is the same as it was the first time, so no changes here, but the second query with 712 as a parameter, now goes to index seek with index seek. So the plan changed from the index seek and index scan to index seek and index seek on the transaction history archive table.

Now what happens if we drop the index supporting to index seek on the second table? This index seek, let’s see what happens. Let’s drop this index, okay, now the question is, will this query fail or will it execute? This query did not fail, but there is no index, so it goes in index scan on the clustered index because there is no supporting index – no clustered index seek. The second query will also execute, let’s see that. And it also goes to the index seek and index scan. There is a very useful DMV, query store plan, where you can see all the queries that are forced, or all the query plans that are forced by including a reverse statement, is force plan equals to true, and you have a force failure count, which indicates how many times a query wanted to force a plan but it couldn’t, and here is the number two, because for two times we executed the queries and the query couldn’t force a plan that was forced in the query store.

Now, what do we have to do to go back to the forced plan? Well, we just have to recreate the dropped non-clustered index, which I will do now, and now our queries are going to seek with seek and the second one also – and the second one is going to seek with seek. So we just have to repeat the index with no intervention from the query store, the query store is again forcing the plan. If you take a look in the query store plan DMV, the forced fail count is still at two because these two-second executions managed to force a query plan for the query. Let’s go back to the presentation.

Questions, continued

Pinal Dave: Hey Dean, so a couple of questions have flows in while – in the last 20 minutes, so one of the questions is, “Is there any way to view only the queries executed by my application in a query store report?”

Dean Savović: Is there any way to – I didn’t hear that, sorry.

Pinal Dave: Is there any way to view only the queries executed by any particular application in a query store reports? Like additional filters, like if we can say, hey – we name our application or maybe from our .NET or from any Management Studio coming through or somehow is there any way we can figure it out?

Dean Savović: Well, that would mean that more than one application is using the database, and if this is true, then I don’t think so that you can do that. I don’t think so that query store shares some kind of parameter that captures application name from the connection string or something like that. I’m not sure, but I think not.

Pinal Dave: Alright, that’s fine. And one more question I think, which is that how will you make this script available for people to download?

Dean Savović: Well, I assume it’s Brent, I don’t know, but I can upload the scripts, SQL scripts and the presentation to the GroupBy site.

Pinal Dave: Sure.

Best practices for configuring Query Store

Dean Savović: So after the demo, there are some best practices I would like to share with you. So the first one is use the latest SQL Server Management Studio. The latest one is 17.3 because the query store is a very new feature and it is upgraded with all new releases, so use the latest.

Keep query store adjusted to your workload because it’s not the same to monitor data warehouse or some transaction-intensive database. Verify that query store is collecting query data continuously. You can use the sys.database_query_store_options DMV to see in which state is your query store, to see if it is in read-only mode maybe.

Then set the optimal query capture mode. You can use all or auto as I mentioned earlier. Then keep the most relevant data in query store. Limit to ten historical data. You can configure time-based policy to activate auto cleanup. Filter out non-relevant queries, and delete less relevant when maximum size is reached. These are all the configuration options here you can use when you enable the query store and set it to on.

Then avoid drop and create pattern because when you drop an object and again create it you will change the ID of the object and you will not have the history in the query store. And check the status of the forced plans regularly, as I showed you in the demo. You can use the query plan DMV and see if there are failure counts for the forced plans. If there are, then you have to do something.

Questions about Query Store

Pinal Dave: There is one question which we just received. Are you frequently using query store in production database? So I was almost typing down saying I started to use in 2017 and in 2016 I was in a little bit experimenting more, so I was not using it so much, so what do you say Dean, about that?

Dean Savović: I only have one client that is using SQL Server 2016, and we have query store enabled for it. I showed you the test database of that client. So yes, we are using it in production, and it’s very useful.

Pinal Dave: For sure.

Brent Ozar: Very cool. Anybody else if you have any other questions, thanks a lot, Dean, for hanging out with us today. I am going to put a slide up so that you all can – J.D. Walker says he has one. I’m going to put a slide up so that you all can see how to give Dean feedback. Just a second here, let me go switch that on, and Pinal, you can ask the question that J.D. says over in Slack while I get my act together and put my slide up.

Pinal Dave: Absolutely. I think J.D. Walker asked a very fundamental but very important question which people who might have joined a little bit late might have the same question is, why query store? What does it achieve? Why not any other way we can – what is the real purpose behind query store? What’s the heart of the query store?

Well, why query store? Because before query store we had to rely on the query plan cache, and we had to store the data from the query store plan cache if you want to persist this data and analyze it later. Query store gives us a chance to have historical data about our queries and query plans at one place for the database that we enable the query store.

What is Query Store designed to solve?

Pinal Dave: Right, and I think maybe his question also I can totally see because a lot of user people ask me the same one. It’s like, what was one thing it is trying to solve, which did not exist in all earlier solution, which I learned and mastered, like query cache. I mastered it, right, I know it’s not convenient probably, I mastered it, I use sp_Blitz, and a lot of other tools. We all figure it out and mastered, now which is one thing that the query store solves which was not available anywhere in any other one?

Brent Ozar: And all three of us are consultants, too. Like you guys know when a client calls you in, they always want to know what happened yesterday. You know, they always want to know, “Can you tell me what happened yesterday?” And half the time they have small servers too, they don’t have enough plan cache that we can see enough history of what was happening at a given time. Query store is such a cool tool for consultants.

Pinal Dave: Yes, it stores all the data, and most important thing, I think – I personally believe the reason I was not so much using it in 2016 because I just felt it just telling me few things and I can just force the plan, but I think as soon as the 2017 came in, I can see how it is just revolving around this adoptive thing and regressed query and alter tuning, I think it’s just taking it’s own shape and soon it will be the feature which we cannot live without, so I think it’s good to learn now when it is starting otherwise after ten years we might miss the boat and like you know what – like, there are a few things I missed also in my past. So I think query store right now look back in the past and it’s evolving, so that’s the reason we want to learn. That’s what I tell user people. That may be the answer maybe J.D. you might be looking for.

Brent Ozar: Rich Benner has an interesting point, he says, “Yes, this only works if they have it turned on. It should be switched on as standard.” Well, in Azure they do. Azure SQL DB, they turn it on by default now, and I have this weird philosophy about this. Microsoft is paying for it, because it’s not like they charge you more when query store is turned on. Microsoft is paying for the performance overhead when they turn it on across thousands of databases or millions of databases. They believe so much in it that they’re willing to eat the cost on it. They eat the hardware cost and CPU and memory cost because it helps them troubleshoot as well.

Well thanks Dean very much for hanging out with us today!

The following two tabs change content below.

Dean Savović

Head of Data Management Department at Comminus d.o.o.
Dean is Senior DBA practicing SQL Server since version 7.0 and main areas of interest are performance tuning and troubleshooting SQL Server database. He speaks at Microsoft Windays Croatia, Advanced Technology Days Croatia, SSWUG, KulenDayz Croatia, SQL Saturday events, etc... Primary focus is on OLTP databases with occasional adventures with Business Intelligence. He finished Faculty of Electrical Engineering and Computing and MBA on Faculty of Economics in Zagreb. His premise was that it is very useful to combine technical knowledge with management process knowledge that help us in our everyday work. He works in Comminus which is specialized in data management field on Microsoft SQL Server. When he is free he reads, and thinks about SQL Server.

Latest posts by Dean Savović (see all)

Previous Post
SQL Injection Attacks: Is Your Data Secure?
Next Post
Elegant T-SQL Solutions for Real World Problems

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.