Bewerken

Delen via


sys.dm_exec_query_stats (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.

Note

  • The results of sys.dm_exec_query_stats may vary with each execution as the data only reflects finished queries, and not ones still in-flight.
  • To call this from dedicated SQL pool in Azure Synapse Analytics or Analytics Platform System (PDW), use the name sys.dm_pdw_nodes_exec_query_stats. For serverless SQL pool use sys.dm_exec_query_stats.
Column name Data type Description
sql_handle varbinary(64) Is a token that uniquely identifies the batch or stored procedure that the query is part of.

sql_handle, together with statement_start_offset and statement_end_offset, can be used to retrieve the SQL text of the query by calling the sys.dm_exec_sql_text dynamic management function.
statement_start_offset int Indicates, in bytes, beginning with 0, the starting position of the query that the row describes within the text of its batch or persisted object.
statement_end_offset int Indicates, in bytes, starting with 0, the ending position of the query that the row describes within the text of its batch or persisted object. For versions before SQL Server 2014 (12.x), a value of -1 indicates the end of the batch. Trailing comments are no longer included.
plan_generation_num bigint A sequence number that can be used to distinguish between instances of plans after a recompile.
plan_handle varbinary(64) Is a token that uniquely identifies a query execution plan for a batch that has executed and its plan resides in the plan cache, or is currently executing. This value can be passed to the sys.dm_exec_query_plan dynamic management function to obtain the query plan.

Will always be 0x000 when a natively compiled stored procedure queries a memory-optimized table.
creation_time datetime Time at which the plan was compiled.
last_execution_time datetime Last time at which the plan started executing.
execution_count bigint Number of times that the plan has been executed since it was last compiled.
total_worker_time bigint Total amount of CPU time, reported in microseconds (but only accurate to milliseconds), that was consumed by executions of this plan since it was compiled.

For natively compiled stored procedures, total_worker_time may not be accurate if many executions take less than 1 millisecond.
last_worker_time bigint CPU time, reported in microseconds (but only accurate to milliseconds), that was consumed the last time the plan was executed. 1
min_worker_time bigint Minimum CPU time, reported in microseconds (but only accurate to milliseconds), that this plan has ever consumed during a single execution. 1
max_worker_time bigint Maximum CPU time, reported in microseconds (but only accurate to milliseconds), that this plan has ever consumed during a single execution. 1
total_physical_reads bigint Total number of physical reads performed by executions of this plan since it was compiled.

Will always be 0 querying a memory-optimized table.
last_physical_reads bigint Number of physical reads performed the last time the plan was executed.

Will always be 0 querying a memory-optimized table.
min_physical_reads bigint Minimum number of physical reads that this plan has ever performed during a single execution.

Will always be 0 querying a memory-optimized table.
max_physical_reads bigint Maximum number of physical reads that this plan has ever performed during a single execution.

Will always be 0 querying a memory-optimized table.
total_logical_writes bigint Total number of logical writes performed by executions of this plan since it was compiled.

Will always be 0 querying a memory-optimized table.
last_logical_writes bigint Number of buffer pool pages dirtied during the most recently completed execution of the plan.

After a page is read, the page becomes dirty only the first time it is modified. When a page becomes dirty, this number is incremented. Subsequent modifications of an already dirty page do not affect this number.

This number will always be 0 when querying a memory-optimized table.
min_logical_writes bigint Minimum number of logical writes that this plan has ever performed during a single execution.

Will always be 0 querying a memory-optimized table.
max_logical_writes bigint Maximum number of logical writes that this plan has ever performed during a single execution.

Will always be 0 querying a memory-optimized table.
total_logical_reads bigint Total number of logical reads performed by executions of this plan since it was compiled.

Will always be 0 querying a memory-optimized table.
last_logical_reads bigint Number of logical reads performed the last time the plan was executed.

Will always be 0 querying a memory-optimized table.
min_logical_reads bigint Minimum number of logical reads that this plan has ever performed during a single execution.

Will always be 0 querying a memory-optimized table.
max_logical_reads bigint Maximum number of logical reads that this plan has ever performed during a single execution.

Will always be 0 querying a memory-optimized table.
total_clr_time bigint Time, reported in microseconds (but only accurate to milliseconds), consumed inside Microsoft .NET Framework common language runtime (CLR) objects by executions of this plan since it was compiled. The CLR objects can be stored procedures, functions, triggers, types, and aggregates.
last_clr_time bigint Time, reported in microseconds (but only accurate to milliseconds) consumed by execution inside .NET Framework CLR objects during the last execution of this plan. The CLR objects can be stored procedures, functions, triggers, types, and aggregates.
min_clr_time bigint Minimum time, reported in microseconds (but only accurate to milliseconds), that this plan has ever consumed inside .NET Framework CLR objects during a single execution. The CLR objects can be stored procedures, functions, triggers, types, and aggregates.
max_clr_time bigint Maximum time, reported in microseconds (but only accurate to milliseconds), that this plan has ever consumed inside the .NET Framework CLR during a single execution. The CLR objects can be stored procedures, functions, triggers, types, and aggregates.
total_elapsed_time bigint Total elapsed time, reported in microseconds (but only accurate to milliseconds), for completed executions of this plan.
last_elapsed_time bigint Elapsed time, reported in microseconds (but only accurate to milliseconds), for the most recently completed execution of this plan.
min_elapsed_time bigint Minimum elapsed time, reported in microseconds (but only accurate to milliseconds), for any completed execution of this plan.
max_elapsed_time bigint Maximum elapsed time, reported in microseconds (but only accurate to milliseconds), for any completed execution of this plan.
query_hash Binary(8) Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values.
query_plan_hash binary(8) Binary hash value calculated on the query execution plan and used to identify similar query execution plans. You can use query plan hash to find the cumulative cost of queries with similar execution plans.

Will always be 0x000 when a natively compiled stored procedure queries a memory-optimized table.
total_rows bigint Total number of rows returned by the query. Cannot be null.

Will always be 0 when a natively compiled stored procedure queries a memory-optimized table.
last_rows bigint Number of rows returned by the last execution of the query. Cannot be null.

Will always be 0 when a natively compiled stored procedure queries a memory-optimized table.
min_rows bigint Minimum number of rows ever returned by the query during one execution. Cannot be null.

Will always be 0 when a natively compiled stored procedure queries a memory-optimized table.
max_rows bigint Maximum number of rows ever returned by the query during one execution. Cannot be null.

Will always be 0 when a natively compiled stored procedure queries a memory-optimized table.
statement_sql_handle varbinary(64) Applies to: SQL Server 2014 (12.x) and later.

Populated with non-NULL values only if Query Store is turned on and collecting the stats for that particular query.
statement_context_id bigint Applies to: SQL Server 2014 (12.x) and later.

Populated with non-NULL values only if Query Store is turned on and collecting the stats for that particular query.
total_dop bigint The total sum of degree of parallelism this plan used since it was compiled. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
last_dop bigint The degree of parallelism when this plan executed last time. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
min_dop bigint The minimum degree of parallelism this plan ever used during one execution. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
max_dop bigint The maximum degree of parallelism this plan ever used during one execution. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
total_grant_kb bigint The total amount of reserved memory grant in KB this plan received since it was compiled. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
last_grant_kb bigint The amount of reserved memory grant in KB when this plan executed last time. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
min_grant_kb bigint The minimum amount of reserved memory grant in KB this plan ever received during one execution. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
max_grant_kb bigint The maximum amount of reserved memory grant in KB this plan ever received during one execution. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
total_used_grant_kb bigint The total amount of reserved memory grant in KB this plan used since it was compiled. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
last_used_grant_kb bigint The amount of used memory grant in KB when this plan executed last time. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
min_used_grant_kb bigint The minimum amount of used memory grant in KB this plan ever used during one execution. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
max_used_grant_kb bigint The maximum amount of used memory grant in KB this plan ever used during one execution. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
total_ideal_grant_kb bigint The total amount of ideal memory grant in KB this plan estimated since it was compiled. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
last_ideal_grant_kb bigint The amount of ideal memory grant in KB when this plan executed last time. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
min_ideal_grant_kb bigint The minimum amount of ideal memory grant in KB this plan ever estimated during one execution. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
max_ideal_grant_kb bigint The maximum amount of ideal memory grant in KB this plan ever estimated during one execution. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
total_reserved_threads bigint The total sum of reserved parallel threads this plan ever used since it was compiled. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
last_reserved_threads bigint The number of reserved parallel threads when this plan executed last time. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
min_reserved_threads bigint The minimum number of reserved parallel threads this plan ever used during one execution. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
max_reserved_threads bigint The maximum number of reserved parallel threads this plan ever used during one execution. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
total_used_threads bigint The total sum of used parallel threads this plan ever used since it was compiled. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
last_used_threads bigint The number of used parallel threads when this plan executed last time. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
min_used_threads bigint The minimum number of used parallel threads this plan ever used during one execution. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
max_used_threads bigint The maximum number of used parallel threads this plan ever used during one execution. It will always be 0 for querying a memory-optimized table.

Applies to: SQL Server 2016 (13.x) and later.
total_columnstore_segment_reads bigint The total sum of columnstore segments read by the query. Cannot be null.

Applies to: Starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x) CU3
last_columnstore_segment_reads bigint The number of columnstore segments read by the last execution of the query. Cannot be null.

Applies to: Starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x) CU3
min_columnstore_segment_reads bigint The minimum number of columnstore segments ever read by the query during one execution. Cannot be null.

Applies to: Starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x) CU3
max_columnstore_segment_reads bigint The maximum number of columnstore segments ever read by the query during one execution. Cannot be null.

Applies to: Starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x) CU3
total_columnstore_segment_skips bigint The total sum of columnstore segments skipped by the query. Cannot be null.

Applies to: Starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x) CU3
last_columnstore_segment_skips bigint The number of columnstore segments skipped by the last execution of the query. Cannot be null.

Applies to: Starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x) CU3
min_columnstore_segment_skips bigint The minimum number of columnstore segments ever skipped by the query during one execution. Cannot be null.

Applies to: Starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x) CU3
max_columnstore_segment_skips bigint The maximum number of columnstore segments ever skipped by the query during one execution. Cannot be null.

Applies to: Starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x) CU3
total_spills bigint The total number of pages spilled by execution of this query since it was compiled.

Applies to: Starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x) CU3
last_spills bigint The number of pages spilled the last time the query was executed.

Applies to: Starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x) CU3
min_spills bigint The minimum number of pages that this query has ever spilled during a single execution.

Applies to: Starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x) CU3
max_spills bigint The maximum number of pages that this query has ever spilled during a single execution.

Applies to: Starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x) CU3
pdw_node_id int The identifier for the node that this distribution is on.

Applies to: Azure Synapse Analytics, Analytics Platform System (PDW)
total_page_server_reads bigint Total number of remote page server reads performed by executions of this plan since it was compiled.

Applies to: Azure SQL Database Hyperscale
last_page_server_reads bigint Number of remote page server reads performed the last time the plan was executed.

Applies To: Azure SQL Database Hyperscale
min_page_server_reads bigint Minimum number of remote page server reads that this plan has ever performed during a single execution.

Applies To: Azure SQL Database Hyperscale
max_page_server_reads bigint Maximum number of remote page server reads that this plan has ever performed during a single execution.

Applies To: Azure SQL Database Hyperscale

Note

1 For natively compiled stored procedures when statistics collection is enabled, worker time is collected in milliseconds. If the query executes in less than one millisecond, the value will be 0.

Permissions

On SQL Server and SQL Managed Instance, requires VIEW SERVER STATE permission.

On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

Remarks

Statistics in the view are updated when a query is completed.

Examples

A. Finding the TOP N queries

The following example returns information about the top five queries ranked by average CPU time. This example aggregates the queries according to their query hash so that logically equivalent queries are grouped by their cumulative resource consumption. The Sample_Statement_Text column shows an example of the query structure that matches the query hash, but it should be read without regard to specific values in the statement. For example, if a statement contains WHERE Id = 5, you may read it in its more generic form: WHERE Id = @some_value

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 Sample_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;  

B. Returning row count aggregates for a query

The following example returns row count aggregate information (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;  

See also

Execution Related Dynamic Management Views and Functions (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_query_plan (Transact-SQL)
sys.dm_exec_procedure_stats (Transact-SQL)
sys.dm_exec_trigger_stats (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)