Press "Enter" to skip to content

Category: Query Store

Query Store Space Allocation

Published 2017-02-09 by Kevin Feasel

Grant Fritchey demonstrates how Query Store allocates disk space:

I love the questions I get while I’m presenting because they force me to think and learn. The question in the title is one I received recently. The answer, now that I’m not standing in front of people, is easy. Of course the space is not pre-allocated. Query Store tables are just system tables. They have a limit on how big they can grow (100mb by default), but that space isn’t going to be pre-allocated in any way. The space will just get used as and when it’s needed, just like any other system table. However, don’t take my word for it, let’s prove that.

Read on for the proof.

Comments closed

Compatibility Level And Forced Plans

Published 2017-02-03 by Kevin Feasel

Erin Stellato has an experiment with Query Store plan forcing:

A question came up recently about plan guides and compatibility mode, and it got me thinking about forced plans in Query Store and compatibility mode.  Imagine you upgraded to SQL Server 2016 and kept the compatibility mode for your database at 110 to use the legacy Cardinality Estimator.  At some point, you have a plan that you force for a specific query, and that works great.  As time goes on, you do testing with the new CE and eventually are ready to make the switch to compatibility mode 130.  When you do that, does the forced plan continue to use compatibility mode 110?  I had a guess at the answer but thought it was worth testing.

There are some interesting results here.

Comments closed

Query Store Connect Items

Published 2017-01-12 by Kevin Feasel

Erin Stellato link to two feature requests for Query Store:

Two of these requests have Connect items, which I’m listing below.  The first allows you to change the location of Query Store data to reside somewhere in the user database besides the PRIMARY filegroup:

Option to store query store data in a filegroup other than PRIMARY

The other request is related to exporting that data, which is technically possible now, but it’s not a supported method so it’s not something I really want to implement in a client environment.  I’ve had many people describe their process for testing which includes restoring database nightly.  If they’re using Query Store as part of testing, that data is lost every night by the restore.

Export Query Store tables separately from the database tables

Click through for more and vote up those items relevant to you.

Comments closed

Memory-Optimized Tables In Query Store

Published 2017-01-05 by Kevin Feasel

Enrico van de Laar looks at how Query Store treats memory-optimized tables and natively compiled stored procedures:

Interestingly enough, no runtime statistics are returned for our natively stored procedure. As it turns out, we need to specifically enable the collection of statistics for natively compiled stored procedures through the sp_xtp_control_query_exec_stats stored procedure. Keep in mind, though, that enabling the collection of statistics for natively-compiled stored procedure can cause performance degradation. Thankfully we can very easily control the collection of statistics by enabling it for only specific natively compiled stored procedures and turn it on and off directly.

Read the whole thing.  Long story short:  they work together nicely.

Comments closed

Introducing Query Store

Published 2016-12-28 by Kevin Feasel

Thomas LeBlanc takes a brief look at Query Store:

Here, you will see the four default reports that come with this Option.

  1. Regressed Queries – shows query history and changes in statistics

  2. Overall Resource Consumption – history of resources used in the database

  3. Top resource Consuming Queries – Top x of queries using the most resources

  4. Tracked Queries – enables you to see multiple query plans for a T-SQL statement and compare the plans or force a plan

For DBAs, this is one of the biggest reasons to upgrade to 2016.

Comments closed

Cardinality Estimator Regressions

Published 2016-11-30 by Kevin Feasel

SQL Scotsman has a great post on figuring out which of your queries have become worse as a result of the SQL Server cardinality estimator changes in 2014:

Instantly it is apparent that the most resource intensive query was the same query across both workload tests and note that the query hash is consistent too.  It is also apparent that this query performs worse under the new cardinality estimator model version 120.  To investigate and understand why this particular query behaves differently under the different cardinality estimators we’ll need to look at the actual query and the execution plans.

Looking at the information in #TempCEStats and the execution plans, the problematic query below belongs to the SLEV stored procedure.

There’s also a discussion of Query Store in there, but it’s important to understand how to figure this out even if you’re on 2014 and don’t have access to Query Store.

Comments closed

Modifying The Query Store

Published 2016-11-22 by Kevin Feasel

Grant Fritchey answers a query store question:

When I was presenting on this topic at the PASS Summit a few weeks ago, one great question came up (great question = answer is “I don’t know”), well, I defaulted to an “I don’t know” answer, but my guess was, “No.” The question was: can you take a plan from one server, let’s say a test server, export it in some way, and then import it to production? In this manner, you ensure that a plan you like gets into production without having to clear the plan from cache & generate a plan by running the query.

Great idea.

Read on for the answer, as well as ways to manipulate query store data.

Comments closed

Query Store Filegroups

Published 2016-11-02 by Kevin Feasel

Kendra Little links to a Connect item:

Can you change the filegroup where Query Store keeps its data?

I thought there might be a trick to use a different filegroup for Query Store by using the default filegroup setting in SQL Server before enabling it, but NOPE!

Please vote for this to be improved in this Connect Item.

I concur; Query Store can grow to be pretty large on busy systems, so diligent DBAs who want to keep PRIMARY as small as possible will suddenly find a multi-gigabyte Query Store slowing down those PRIMARY filegroup restores.

Comments closed

Query Store Isn’t A Forensics Engine

Published 2016-09-27 by Kevin Feasel

Grant Fritchey shows that Query Store has a limited capability of finding “ill-behaving” queries at a point in time:

Here’s a great question I received: We had a problem at 9:02 AM this morning, but we’re not sure what happened. Can Query Store tell us?

My first blush response is, no. Not really. Query Store keeps aggregate performance metrics about the queries on the database where Query Store is enabled. Aggregation means that we can’t tell you what happened with an individual call at 9:02 AM…

Well, not entirely true.

Query Store isn’t a total solution for “Why was the system slow at XX:XX?” types of questions.  This does not diminish its value as long as you do not try to treat it as your only monitoring solution.

Comments closed

Why Force Query Store Plans

Published 2016-09-20 by Kevin Feasel

Grant Fritchey explains the wherefore behind query store plan forcing:

But, what else does Force Plan do for you? What if you never experience bad parameter sniffing (you do, but I’m not going to argue the point)? Is there something else that Force Plan can do for you? Heck yes! The whole point of creating the Query Store was in order to address Plan Regression. What the heck is plan regression? When Microsoft makes any change to the Query Optimizer, and those changes come all the time, it’s possible that you might see a change in your execution plans. Most of the time, it’s going to be a positive change. That’s why they’re changing the Optimizer after all, to improve it. However, sometimes, you’re benefiting from the old behavior of the Optimizer and that new plan doesn’t work as well as the old plan. This is plan regression. When Microsoft changed the Cardinality Estimation engine in SQL Server 2014, more than a few people experienced the new estimator giving row estimates that resulted in a different execution plan that didn’t perform as well as the old plan. This is plan regression. What to do?

This is a good read.

Comments closed