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 ,
        readonly_reason,
        current_storage_size_mb ,
        max_storage_size_mb ,
        max_plans_per_query
FROM    sys.database_query_store_options

defaultoff

You will need to manually enable it.

 ALTER DATABASE [MyDWH] SET QUERY_STORE = ON;

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

itson

What are the default options set?

table

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: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-query-store-options-transact-sql?view=sql-server-2017

capturemodes

There are a couple of queries that Microsoft have posted for you to get some query information (https://azure.microsoft.com/en-gb/blog/announcing-the-general-availability-of-query-store-for-azure-sql-data-warehouse/).


 SELECT
       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]
	   ,rs.count_executions
FROM
       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
WHERE
q.query_id = 24
AND rs.count_executions > 10;

output

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;  

nonex
I guess not.

Advertisements

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:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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