Query history use case

The query history service and the sys.query_history table provide developers, support engineers, and customers with tools for detailed investigations and large-scale insights.

The query history service and the sys.query_history table are designed for developers, support engineers, and customers. With a wide range of available fields, you can use the service to:

Find a specific query and view its details.
Analyze trends across multiple queries for performance monitoring.

This supports both detailed investigations and large-scale insights.

Example queries

Basic query information

Get key details such as SQL text, DAG ID, execution plan, and summary.

SELECT sql, tez_dag_id, plan, exec_summary  
FROM sys.query_history  
WHERE query_id = 'abc123';

Query count by user and database

Find the number of queries run by each user on each database.

SELECT db_name, end_user, COUNT(*) AS query_count  
FROM sys.query_history  
GROUP BY db_name, end_user  
ORDER BY db_name;

Top five longest-running queries

Identify the five queries with the highest execution time.

SELECT end_user, query_id, total_time_ms  
FROM sys.query_history  
ORDER BY total_time_ms DESC  
LIMIT 5;

Concurrent query detection

Determine how many queries overlapped with each query.

SELECT a.query_id, COUNT(b.query_id) AS concurrent_queries  
FROM sys.query_history a  
LEFT JOIN sys.query_history b  
  ON a.query_id != b.query_id  
  AND a.start_time <= b.end_time  
  AND b.start_time <= a.end_time  
GROUP BY a.query_id  
ORDER BY concurrent_queries DESC  
LIMIT 5;

Extracting counters from execution summary

Use regular expressions to extract specific counters that are not stored in separate fields.

SELECT query_id, regexp_extract(exec_summary, 'AM_CPU_MILLISECONDS: (\\d+)', 1) AS am_cpu_milliseconds  
FROM sys.query_history  
WHERE tez_dag_id IS NOT NULL;