how to list Query Statistics

 

Understanding dm_exec_query_stats in SQL Server

The dm_exec_query_stats view in SQL Server provides aggregated performance statistics for cached query plans. Each row in this view corresponds to a single query statement within a cached plan. The data stored here is tied to the lifetime of the plan, meaning that when a plan is removed from the cache, the associated rows in this view are also deleted.

---

Query to Retrieve Data Based on Execution Count

This SQL query displays results based on the number of times each query has been executed.

SELECT TOP 1000 
    SUBSTRING(qt.TEXT, (qs.statement_start_offset / 2) + 1, 
    ((CASE qs.statement_end_offset
        WHEN -1 THEN DATALENGTH(qt.TEXT)
        ELSE qs.statement_end_offset
    END - qs.statement_start_offset) / 2) + 1),
    qs.execution_count,
    qs.total_logical_reads,
    qs.last_logical_reads,
    qs.total_logical_writes,
    qs.last_logical_writes,
    qs.total_worker_time,
    qs.last_worker_time,
    qs.total_elapsed_time / 1000000 AS total_elapsed_time_in_S,
    qs.last_elapsed_time / 1000000 AS last_elapsed_time_in_S,
    qs.last_execution_time,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY execution_count DESC;

---

Example: Top Five Queries by Average CPU Time

The following query returns the top five queries based on average CPU time. The queries are grouped by their query hash, which ensures that logically equivalent queries are aggregated based on their total resource consumption.

SELECT TOP 5 
    query_stats.query_hash AS "Query Hash",
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
    MIN(query_stats.statement_text) AS "Statement Text"
FROM 
    (SELECT QS.*, 
    SUBSTRING(ST.text, (QS.statement_start_offset / 2) + 1, 
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(ST.text)
        ELSE QS.statement_end_offset END
        - QS.statement_start_offset) / 2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST) AS query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;

---

Example: Query to Retrieve Row Count Aggregates

This query retrieves aggregate row count information, including total rows, minimum rows, maximum rows, and last rows for queries.

SELECT 
    qs.execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset / 2 + 1, 
    (CASE WHEN qs.statement_end_offset = -1 
        THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 
        ELSE qs.statement_end_offset END - 
        qs.statement_start_offset) / 2) AS query_text,
    qt.dbid, 
    dbname = DB_NAME(qt.dbid), 
    qt.objectid,
    qs.total_rows, 
    qs.last_rows, 
    qs.min_rows, 
    qs.max_rows
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.text LIKE '%SELECT%'
ORDER BY qs.execution_count DESC;

---

For more detailed information, you can refer to the official documentation: Sys.dm_exec_query_stats Documentation.