Azure SQL Data Warehouse automatic performance monitoring

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:

  • Troubleshoot and improve ad hoc workloads
  • Identify resource consuming queries
  • Monitor query performance and impact to the plans by evaluating changes in statistics, indexes, or system size

The Query Store contains the following stores:

  • plan store for persisting the execution plan information
  • runtime stats store for persisting the execution statistics
  • wait stats store for persisting wait stats information

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.

Enabling Query Store

Query Store is not active for new databases by default. There are to options for enabling the Query Store:

Enable the Query Store in SQL Server Management Studio:

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.

Enable the Query Store via Transact-SQL Statements:

Run an ALTER DATABASE T-SQL statement:

Enable Query Store SQL

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.

How to find the full text for any query

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.

Fullt text query

The results will display the query_id and the text of the query being executed.

full-text-query-table

[su_note note_color=”#eeeeee” text_color=”#151212″]Pro Tip: Get comprehensive insights into your Azure SQL Data Warehouse.[/su_note]

Finding the top executing queries

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.

top-queries

And the results will look like the following:

top-queries-table

Finding execution time for a specific query

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.

query-execution-time
query-execution-time-result

[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]

Tracking queries with the highest variance in execution

queries-highest-variance
queries-highest-variance-result

Further reading

Find out how Netreo can help with all Azure monitoring needs. Request a Demo Today

Ready to get started? Get in touch or schedule a demo.