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.
Comments closedCategory: Query Store
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 closedGrant 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 CommentsPaul White expands upon NOEXPAND:
There is another consequence of not using the
NOEXPANDhint, which I mentioned in passing a couple of years ago in my article, Optimizer Limitations with Filtered Indexes:The
NOEXPANDhints are needed even in Enterprise Edition to ensure the uniqueness guarantee provided by the view indexes is used by the optimizer.
If you use indexed views in your environment, read this article.
Comments closedKendra 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