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:
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.
The results will display the query_id and the text of the query being executed.
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.
And the results will look like the following:
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.
Tracking queries with the highest variance in execution
Further reading
- Get started with the Query Store
- Find out more about the Query Store in the documentation
- Explore the Query Store DMVs
Leave A Comment