Azure SQL Data Warehouse Query Store

I personally think that query store has been a fantastic feature. I find myself using it for query performance troubleshooting (plan regressions is a big one). This has always been available since SQL Server 2016 and even Azure SQL Database but now Microsoft have made it available for Azure SQL Data Warehouse (DW).

If you connect to the Azure SQL DW via SQL Server Management Studio (SSMS) there is no Query Store Node (for the version, I was checking 17.9.1). I was even wondering if it was on by default?

Continue reading


The world of Automatic Tuning – SQL Server 2017

Times are changing, 10 years ago I would never have thought that self-tuning databases would be available as a packaged product. I was testing out SQL Server 2017 Automatic Tuning recently and I ended up with the following situation. Below shows an image from the query store.

Continue reading

SQL Server 2017 – Interleaved Execution

I worked on testing interleaved execution with Microsoft back in January, I didn’t do much, just tested the functionality against some in-house code we had. (If you need a detailed primer on the subject, please see

Continue reading

Query Store – SQL Server 2017 vs Azure SQL Database

I love the query store, it is powerful (can be dangerous) , easy to use and packed full of information. I use it frequently across my local SQL Servers and Azure SQL Database.

Continue reading

Query Store – Quick Tour

The query store, Borko Novakovic Senior Program Manager from Microsoft calls this feature “Similar to an airplane’s flight data recorder”.

To get this setup there are some pre-Reqs needed. First you need the latest version of SSMS – download it and install it.  This blog post is just a whirl wind / high level post on query store, you could write a book on it.


Anyways, next comes the fun part – enabling it.


Why I said you need the latest version of SSMS is because it exposes the functionality via the GUI. You will see the query store node.


Once enabled you have a plethora of information at your finger tips – and I am really impressed with it. Now there is no real need to dig into the plan cache to get the information, or a need to run XML to parse data. It’s all here ready for use.

Beware of some default configuration options:

  • The number of days to retain data in the query store. The default value is 30.
  • The default value is 900 seconds (that is when data written to the query store is persisted to disk).
  • MAX_STORAGE_SIZE_MB limit is 100MB
  • Time interval at which runtime execution statistics data is aggregated into the query store. The default value is 60.
  • SIZE_BASED_CLEANUP_MODE – this is whether or not the cleanup process will be automatically activated, auto is the default option.
  • QUERY_CAPTURE_MODE for Azure the default is AUTO meaning ignore infrequent and queries with insignificant compile and execution duration

You can change these settings via SSMS under database properties.

Please Note: Query store data is stored within the host database.


Let’s have a look at “Top Resource consuming queries” option. This is just 1 area of analysis, as mentioned before there is alot of information within query store that you could analyse.

You have the ability to analyse the following metrics:


For this example I was looking at CPU (AVG).

You have 3 windows presented (for this example)

  1. This is a bar chart showing you query id’s – usually you select one of interest
  2. Shows you the plan summary for that specfic query
  3. Will give you the execution plan for the above plan id.


It’s so powerful you even have the option to FORCE a plan, something that you may want to consider before actually pressing that button.  Saying that, I have the below situation – you know what I am going to do right?