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
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
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.
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;
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.