Data warehouse performance monitoring and management are crucial for the overall health of the data environment and impact users’ ability to efficiently draw meaningful data insights.
Announced for preview Query Store feature provides customers with insight on query plan choice and performance by tracking queries, query plans, runtime statistics, and query history. Query Store combines a set of internal stores and Dynamic Management Views that let users:
The Query Store contains the following stores:
These stores are managed automatically by SQL Data Warehouse and provide an unlimited number of queries stored over the last 7 days at no additional charge.
Query Store is not active for new databases by default. There are to options for enabling the Query Store:
1. In Object Explorer, right-click a database, and then click Properties.
2. In the Database Properties dialog box, select the Query Store page.
3. In the Operation Mode (Requested) box, select On.
Run an ALTER DATABASE T-SQL statement:
To disable Query Store by run the ALTER DATABASE command specifying OFF.
NOTE: it is not possible to enable the Query Store for the master or tempdb database.
Retrieve the full text of any query executed over the last 7 days by using the sys.query_store_query and sys.query_store_query_text DMVs.
The results will display the query_id and the text of the query being executed.
[su_note note_color=”#eeeeee” text_color=”#151212″]Pro Tip: Get comprehensive insights into your Azure SQL Data Warehouse.[/su_note]
Query Store tracks all query executions by default. On a busy data warehouse, it may make sense to track the top queries by execution count. With the help of the Query Store views, it is possible to get the query execution count for the 10 commands executed most frequently.
And the results will look like the following:
Using the sys.query_store_plan and sys.query_store_runtime_stats DMVs it is possible to gather query statistics for the queries with high variance in execution.
[su_note note_color=”#eeeeee” text_color=”#151212″]Pro Tip: Get deep and immediate insight into the stability of all of your Azure resources.[/su_note]
