Press "Enter" to skip to content

Category: Query Store

Query Store Storage Options

Published 2016-05-12 by Kevin Feasel

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.

Comments closed

Optimize For Ad Hoc With Querystore

Published 2016-03-29 by Kevin Feasel

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.

Comments closed

Query Store And Recompile

Published 2016-02-25 by Kevin Feasel

Grant Fritchey shows that Query Store commands kinda-sorta overpower recompilation hints:

Now, no matter what value I pass it, the RECOMPILE hint is effectively ignored. I have the same plan every time (in my case, the London plan). Ah, but is the RECOMPILE hint ignored? In fact, no, it isn’t. If I capture the extended event sql_statement_recompile, I can see every single time I execute this procedure that it’s going through a full recompile… which is then tossed and the plan I chose is forced upon the optimizer. Every time.

This tells me that if you were using OPTION(RECOMPILE) to get around nasty parameter sniffing problems before, and if you go to Query Store, and if you force a particular plan to get around said nasty parameter sniffing problems, then you probably want to update the query to get rid of that OPTION(RECOMPILE) in order to prevent unnecessary plan compilation work.

Comments closed

Finding A Query In Query Store

Published 2016-01-27 by Kevin Feasel

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.

Comments closed

Clear The Query Store

Published 2016-01-19 by Kevin Feasel

Grant Fritchey shows how to clear the Query Store in SQL Server 2016:

While setting up example code for my presentation at SQL Cruise (which is going to be a fantastic event), I realized I wanted to purge all the data from my Query Store, just for testing. I did a series of searches to try to track down the information and it just wasn’t there. So, I did what anyone who can phrase a question in less than 140 characters should do, I posted a question to Twitter using the #sqlhelp hash tag.

You can also call EXEC sp_query_store_remove_query to remove a specific query from the Query Store.

2 Comments

Query Store Works With Recompile

Published 2015-11-30 by Kevin Feasel

Kendra Little shows us something awesome about Query Store and OPTION(RECOMPILE):

YAY! For all my queries that were run with RECOMPILE hints, I can see information about how many times they were run, execution stats, their query text and plan, and even information about compilation.

And yes, I have the execution plans, too — the “CAST(qsp.query_plan AS XML) AS mah_query_plan” totally works.

This is great news.  Query Store is going to be a big feature for DBAs.

Comments closed