| title | sys.dm_exec_query_stats (Transact-SQL) | Microsoft Docs | ||||
|---|---|---|---|---|---|
| ms.custom | |||||
| ms.date | 01/04/2018 | ||||
| ms.prod | sql | ||||
| ms.prod_service | database-engine, sql-database | ||||
| ms.reviewer | |||||
| ms.suite | sql | ||||
| ms.technology | system-objects | ||||
| ms.tgt_pltfrm | |||||
| ms.topic | language-reference | ||||
| f1_keywords |
|
||||
| dev_langs |
|
||||
| helpviewer_keywords |
|
||||
| ms.assetid | eb7b58b8-3508-4114-97c2-d877bcb12964 | ||||
| caps.latest.revision | 64 | ||||
| author | stevestein | ||||
| ms.author | sstein | ||||
| manager | craigg | ||||
| monikerRange | = azuresqldb-current || >= sql-server-2016 || = sqlallproducts-allversions |
[!INCLUDEtsql-appliesto-ss2008-asdb-xxxx-xxx-md]
Returns aggregate performance statistics for cached query plans in [!INCLUDEssNoVersion]. 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
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.
Note
To call this from [!INCLUDEssSDWfull] or [!INCLUDEssPDW], use the name sys.dm_pdw_nodes_exec_query_stats.
| 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 [!INCLUDEssSQL14], a value of -1 indicates the end of the batch. Trailing comments are no longer include. |
| 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 [!INCLUDEmsCoName] [!INCLUDEdnprdnshort] 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 [!INCLUDEdnprdnshort] 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 [!INCLUDEdnprdnshort] 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 [!INCLUDEdnprdnshort] 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: [!INCLUDEssSQL14] through [!INCLUDEssCurrent]. 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: [!INCLUDEssSQL14] through [!INCLUDEssCurrent]. 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| 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: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. |
| total_columnstore_segment_reads | bigint | The total sum of columnstore segments read by the query. Cannot be null. Applies to: Starting with [!INCLUDEssSQL15] SP2 and [!INCLUDEssSQL17] 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 [!INCLUDEssSQL15] SP2 and [!INCLUDEssSQL17] 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 [!INCLUDEssSQL15] SP2 and [!INCLUDEssSQL17] 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 [!INCLUDEssSQL15] SP2 and [!INCLUDEssSQL17] CU3 |
| total_columnstore_segment_skips | bigint | The total sum of columnstore segments skipped by the query. Cannot be null. Applies to: Starting with [!INCLUDEssSQL15] SP2 and [!INCLUDEssSQL17] 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 [!INCLUDEssSQL15] SP2 and [!INCLUDEssSQL17] 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 [!INCLUDEssSQL15] SP2 and [!INCLUDEssSQL17] 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 [!INCLUDEssSQL15] SP2 and [!INCLUDEssSQL17] CU3 |
| total_spills | bigint | The total number of pages spilled by execution of this query since it was compiled. Applies to: Starting with [!INCLUDEssSQL15] SP2 and [!INCLUDEssSQL17] CU3 |
| last_spills | bigint | The number of pages spilled the last time the query was executed. Applies to: Starting with [!INCLUDEssSQL15] SP2 and [!INCLUDEssSQL17] CU3 |
| min_spills | bigint | The minimum number of pages that this query has ever spilled during a single execution. Applies to: Starting with [!INCLUDEssSQL15] SP2 and [!INCLUDEssSQL17] CU3 |
| max_spills | bigint | The maximum number of pages that this query has ever spilled during a single execution. Applies to: Starting with [!INCLUDEssSQL15] SP2 and [!INCLUDEssSQL17] CU3 |
| pdw_node_id | int | The identifier for the node that this distribution is on. Applies to: [!INCLUDEssSDWfull], [!INCLUDEssPDW] |
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.
On [!INCLUDEssNoVersion_md], requires VIEW SERVER STATE permission.
On [!INCLUDEssSDS_md], requires the VIEW DATABASE STATE permission in the database.
Statistics in the view are updated when a query is completed.
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.
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; 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; 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)