Category: Query Store

Grant Fritchey wonders, if you force a plan using Query Store but the plan ages out of cache, do you still use the forced plan?

To start with, a small stored procedure that I use all the time for bad parameter sniffing demos that reliably gets different plans with different values due to statistics skew:

1 2 3 4 5 6 7 8 9 10 11 12

CREATE PROC dbo.spAddressByCity @City NVARCHAR(30) AS SELECT  a.AddressID,         a.AddressLine1,         a.AddressLine2,         a.City,         sp.Name AS StateProvinceName,         a.PostalCode FROM    Person.Address AS a JOIN    Person.StateProvince AS sp         ON a.StateProvinceID = sp.StateProvinceID WHERE   a.City = @City;

If this procedure is called for a value of ‘London’ it gets a plan with a Merge Join. For most other value it gets a plan with a Loops Join. Here’s an example of the ‘London’ plan:

It’s a good question with a good answer.

Erin Stellato talks about default query store settings:

For each database where I enable Query Store, I’d consider the workload and then look at the settings.  I tend to think that the default value of 100MB for MAX_STORAGE_SIZE_MB is really low, and I would be inclined to bump up STALE_QUERY_THRESHOLD_DAYS from 30 to something a bit higher.  I’d also probably drop  DATA_FLUSH_INTERVAL_SECONDS to something lower than 900 seconds (15 minutes) if my storage can support it.  This setting determines how often Query Store data is flushed to disk.  If it’s every 15 minutes, then I could potentially lose 15 minutes of Query Store data if my server happened to crash before it could be written to disk.  I’d also think about changing INTERVAL_LENGTH_MINUTES to a value smaller than 60 if I wanted to aggregate my query data over a smaller amount of time.  Sometimes interesting events happen within a 60 minute time frame, and they get can lost when data is aggregated across that window.  However, aggregating more frequently means I’m adding processing overhead to the system – there’s a trade-off there to figure out.

In our environment at least, 100 MB of query store data would last, oh, a couple hours?  Definitely tweak your settings and keep an eye on them early on.

Erin Stellato looks at Query Store storage options, specifically MAX_STORAGE_SIZE:

Now, there are catalog views that allow you to view the Query Store data.  You can copy that data into another database using SELECT INTO, and then do comparisons, but wouldn’t it be nice to have some kind of export option?  There’s a Connect item for that:

Export Query Store tables separately from the database tables: https://connect.microsoft.com/SQLServer/feedback/details/2620017/export-query-store-tables-separately-from-the-database-tables

If you think this is something that would be useful for Query Store, please up vote it!  Again, Query Store is available in ALL editions of SQL Server 2016, this is definitely a feature you CAN use and will want to use!  This potential option won’t make it into RTM, but with the change in how SQL Server is releasing CUs, the more important the SQL Server team sees this option (as a result of votes), the faster it might make it in a release.

Query Store is one of the most exciting features for database administrators to hit in quite a while.  There will be some V1 pains, but this feature is well worth the upgrade to 2016.

Grant Fritchey investigates the combination of using Query Store and turning on Optimize For Ad Hoc Workloads:

In short, the plan is stored in the query store, even though the plan isn’t stored in cache. Now, this has implications. I’m not saying they’re good and I’m not saying they’re bad, but there are implications. If you’re in a situation where you need to use Optimize For Ad Hoc to help manage your cache, now, you’re going to possibly see negative impacts on your Query Store since it’s going to capture all the plans that you avoided. There are mechanisms for managing Query Store behavior.

I’d consider this correct behavior.  I want to be able to see those one-off query plans.  A quick note on Query Store, though:  it chews up a lot of disk space in a busy environment, so if you’re planning on holding query store entries for a while, keep plenty of disk space available.

SQL Sasquatch has a question for you:

Looking around for a good way to export and import query store from SQL Server 2016.

If you know an easy way to export data from the Query Store, please let Sasquatch know.

Grant Fritchey shows us how to find a query in the Query Store:

The primary views you’ll want are sys.query_store_query and sys.query_store_query_text. They join together based on the query_text_id. Let’s take four scenarios and see if we can retrieve the correct query.

This is a little more complicated than I would have hoped, but as Grant notes, this is the CTP, so maybe there will be some tooling added to make life a little easier.