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?

 SELECT  desired_state_desc ,
        actual_state_desc ,
        current_storage_size_mb ,
        max_storage_size_mb ,
FROM    sys.database_query_store_options


You will need to manually enable it.


SELECT  desired_state_desc ,
        actual_state_desc ,
        current_storage_size_mb ,
        max_storage_size_mb ,
FROM    sys.database_query_store_options 


What are the default options set?


Bizarrely wait_stats_capture_code is set to on yet when I try to get some information:

Msg 104385, Level 16, State 1, Line 30 Catalog view ‘query_store_wait_stats’ is not supported in this version.

Another difference that I noticed was the query_capture_mode_desc being set to ALL. This is usually the case for local SQL Servers but Azure SQL DB usually is set to AUTO, for Azure SQL DW looks like it is set to ALL.

Point of reference of the meaning:


There are a couple of queries that Microsoft have posted for you to get some query information (

       q.query_id               [query_id]
       , t.query_sql_text       [command]
       , rs.avg_duration        [avg_duration]
       , rs.min_duration        [min_duration]
       , rs.max_duration        [max_duration]
       sys.query_store_query q
       JOIN sys.query_store_query_text t ON q.query_text_id = t.query_text_id
       JOIN sys.query_store_plan p ON p.query_id = q.query_id
       JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
q.query_id = 24
AND rs.count_executions > 10;


This is a very basic start to Query Store on Azure SQL DW. It is an easy way to see what is running. You will need to get creative with the solution though like running your own logging / time variance analysis. Ideally there will be GUI support for this feature because that is what makes Query Store so powerful, its accessibility via the GUI and not just TSQL to do those important things like forcing plans etc. I just miss those pretty colours.

Even without the presence of the GUI can we force a plan? The first query I use to extract the query ID and plan ID and then run the stored procedure.

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
JOIN sys.query_store_query AS Qry
    ON Pl.query_id = Qry.query_id
JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id ; 

EXEC sp_query_store_force_plan 7, 7;  

I guess not.


1 thought on “Azure SQL Data Warehouse Query Store

  1. Pingback: Last Week Reading (2019-03-10) | SQLPlayer

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s