Announcing the general availability of Query Store for Azure SQL Data Warehouse

Since our preview announcement, hundreds of customers have been enabling Query Store to provide insight on query performance. We’re excited to share the general availability of Query Store worldwide for Azure SQL Data Warehouse.

Query Store automatically captures a history of queries, plans, and runtime statistics and retains them for your review when monitoring your data warehouse. Query Store separates data by time windows so you can see database usage patterns and understand when plan changes happen.

Top three reasons to use Query Store right now

1. Find the full text of any query: Using the sys.query_store_query and sys.query_store_query_text catalog views, you can see the full text of queries executed against your data warehouse over the last 7 days.

SELECT
     q.query_id
     , t.query_sql_text
FROM
     sys.query_store_query q
     JOIN sys.query_store_query_text t ON q.query_text_id = t.query_text_id;

2. Finding your top executing queries: Query Store tracks all query executions for your review. On a busy data warehouse, you may have thousands or millions of queries executed daily. Using the Query Store catalog views, you can get the top executing queries for further analysis:

SELECT TOP 10
       q.query_id                    [query_id]
       , t.query_sql_text            [command]
       , SUM(rs.count_executions)    [execution_count]
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
GROUP BY
       q.query_id , t.query_sql_text ORDER BY 3 DESC;

3. Finding the execution times for a query: Query also gathers runtime query statistics to help you focus on queries with variance in execution. The variance could be for a variety of reasons such as loading a bunch of new data.

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]
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 = 10
       AND rs.avg_duration > 0;

Get started now

Query Store is available in all regions for all generations of SQL Data Warehouse with no additional charges. You can enable Query Store by running the ALTER DATABASE SET QUERY_STORE = ON; command.

To get started, you can read the monitoring performance by using the Query Store overview topic. A complete list of supported operations can be found in the Query Store Catalog Views documentation.

Next steps

Azure SQL Data Warehouse continues to lead in the areas of security, compliance, privacy, and auditing. For more information, refer to the whitepaper, “Guide to enhancing privacy and addressing GDPR requirements with the Microsoft SQL platform,” on Microsoft Trust Center, or our documentation, “Secure a database in SQL Data Warehouse.”

• For more information on Query Store in Azure SQL Data Warehouse, refer to the article, “Monitoring performance by using the Query Store,” and the Query Store DMVs, such as sys.query_store_query.
• For feature requests, please vote on our UserVoice.
• To get started today, create an Azure SQL Data Warehouse.
• To stay up-to-date on the latest Azure SQL Data Warehouse news and features, follow us on Twitter @AzureSQLDW.