sys.dm_exec_query_stats (Transact-SQL)

 

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.

注意事項
An initial query of sys.dm_exec_query_stats might produce inaccurate results if there is a workload currently executing on the server. More accurate results may be determined by rerunning the query.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Column name	Data type	Description
sql_handle	varbinary(64)	Is a token that refers to 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 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)	A token that refers to the compiled plan that the query is part of. 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 the number of buffer pool pages dirtied the last time the plan was executed. If a page is already dirty (modified) no writes are counted. Will always be 0 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 returned by the query over the number of times that the plan has been executed since it was last compiled. 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 returned by the query over the number of times that the plan has been executed since it was last compiled. 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 through SQL Server 2014. Reserved for future use.
statement_context_id	bigint	Applies to: SQL Server 2014 through SQL Server 2014. Reserved for future use.

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

Permissions

Requires VIEW SERVER STATE permission on server.

備註

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.

USE AdventureWorks2012; GO 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;

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;

請參閱

動態管理檢視和函數 (Transact-SQL)
執行相關的動態管理檢視和函數 (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_query_plan (Transact-SQL)