Press "Enter" to skip to content

Category: Query Store

Query Store Capture Modes

Published 2017-10-25 by Kevin Feasel

Arun Sirpal notes an important difference in the default Query Store settings for SQL Server 2017 versus Azure SQL Database:

So just remember the only difference when analyzing settings is the difference in Query Store Capture Mode. For Azure it is set to AUTO whereas with local installed SQL Servers it is set to ALL.

What does this mean? ALL means that it is set to capture all queries but AUTO means infrequent queries and queries with insignificant cost are ignored. Thresholds for execution count, compile and runtime duration are internally determined.

Read on to learn more, including how to change these settings.

Comments closed

Forced Plans And Query Text

Published 2017-09-26 by Kevin Feasel

Erin Stellato warns us about changing objects with which we use Query Store’s forced plans:

There are different query_id values in sys.query_store_query, as well as different entries for each query in sys.query_store_query_text. Both queries are still tied to the stored procedure (object_id 1483152329) which can be confusing if you don’t understand what occurred. In order to confirm which query has executed most recently, you can use the query below, which joins both system views (replace the query_ids as appropriate)

This is a good thing to keep in mind if you perform formatting changes on procedures.

Comments closed

Query Store And Backups

Published 2017-08-24 by Kevin Feasel

Kendra Little walks us through Query Store portability via database backups:

Query Store was designed to be clever, and to minimize its impact on your performance. Query Store only flushes its data from memory to disk periodically. You get to control this by setting the data flush interval in the Query Store settings for a database. (Read more about this in Microsoft Documentation here.)

The default value for Query Store data flush is 15 minutes. That means that in the case of a crash, you might lose up to around 15 minutes of activity.

Even if you’ve lowered this, you might want to make sure that a backup contains the very latest activity, particularly if you’re taking the backup to get Query Store data for someone to look at.

Read the whole thing.

Comments closed

SQL Server 2017 Finds Plan Regressions

Published 2017-07-21 by Kevin Feasel

Jovan Popovic shows off some automatic tuning functionality in SQL Server 2017:

Plan change regression happens when SQL Database changes a plan for some T-SQL query, and the new plan has the worse performance than the previous one. SQL Server 2017 has Automatic Tuning feature that enables you to easily find plan change regressions and fix them. In this post you will see the demo script that you can use to cause plan change regression and manually fix it using new sys.dm_db_tuning_recommendations view.

If you are not familiar with plan regressions and new tuning recommendations in SQL Server 2017, I would recommend to read these two posts:

This would be enough to understand steps in this demo.

Our experience with plan regression recommendations has been uniformly positive so far.  Those tests have been in dev and QA environments, but so far, there hasn’t been a terrible recommendation.

Comments closed

Wait Stats In Query Store

Published 2017-07-05 by Kevin Feasel

Andrejs Antjufejevs has great news if you’re using Query Store:

Starting today in Azure SQL Database and from CTP 2.0 of SQL Server 2017 wait stats per query are available in Query Store. Now you can exactly identify why and how much every plan waited for some resource. Information about wait times are persisted so you can also analyze through the history what was the problems and why queries waited for resources

This is a welcome improvement for query tuners on 2017.

Comments closed

Open Query Store

Published 2017-06-30 by Kevin Feasel

William Durkin announces Open Query Store, a Query Store-like solution for pre-2016 SQL Server instances:

The first release was published at the end of June 2017 and provides a background collection of query execution statistics in a database of your choice. The v1.0 release supports SQL Server from 2008 to 2014 and all editions (Express, Standard, Enterprise). There is also a PowerShell installer for those that are so inclined, which will grab the code from GitHub and install OQS into a specified database.

There is also a custom report which can be installed in SSMS (2016 and 2017), which will display information that OQS has collected.

This is a really cool community solution if you’re stuck on an older version of SQL Server for a while.

Comments closed

Multi-Database Query Store Data Retrieval

Published 2017-06-05 by Kevin Feasel

Tracy Boggiano wants to get Query Store data for a large number of databases on the same instance and has written a Powershell script to this effect:

In SQL Server 2016, Microsoft introduced to us the Query Store.  This has been proven to be a very beneficial feature especially at my job, saved the day on Christmas Eve. One of the limitations if see is when you have server with several databases on it trying to find the one that has the problem. So until Microsoft gives use a better tool or someone builds one I wrote a quick PowerShell function to query across all the databases and return data to a grid so you can sort and find that troublesome query.

To call it simply provide the server, which metric you want, the top number of queries to return, who many hours to look back, and the minimum execution count.

This is one of two issues I have with Query Store, the other being that its data gets saved to the primary filegroup without any recourse.  Click through for the script.

Comments closed

Trace Flags Used With Query Store

Published 2017-06-01 by Kevin Feasel

Erin Stellato describes two Query Store trace flags:

Microsoft maintains a list of supported trace flags and I noticed that there are two new ones related to Query Store: 7745 and 7752.  The descriptions for these Query Store Trace Flags are pretty straight-forward, but for those of you not familiar with Query Store, I thought I’d provide some context and details.

Click through for the descriptions of these two trace flags.

Comments closed

Comparing Query Store Plans

Published 2017-05-08 by Kevin Feasel

Arun Sirpal explains how to compare two plans in the Query Store:

A small but nice little feature I have been using recently can be found within Query Store.

Let’s say you have 2 Plan IDs for a query, naturally you want to view the execution plan for the different plans. In the past I did it a manual way, by that I mean by individually clicking on the Plan ID to see the plan then moving on to the next one.

It is much easier to explain with some images.

Click through to see those images.

Comments closed

How Query Store And Plan Guides Interact

Published 2017-04-26 by Kevin Feasel

Grant Fritchey shows that query metadata gets a little weird when you have a plan guide trying to use one particular query and Query Store is forcing a different plan:

If we rerun the query and then take a look at the first operator in the execution plan, we can see that the Plan Guide is in use… and that the query hash has changed. It no longer matches the original query. Now it matches the query that included the query hint. This actually makes perfect sense. The Plan Guide is basically changing the query from the first example above, into the second.

Now, what happens when we toss in the Query Store

The query behavior is exactly what you want, but some of the metadata is no longer correct.

Comments closed