Export (0) Print
Expand All
2 out of 4 rated this helpful - Rate this topic

sys.dm_exec_query_stats

Updated: 12 December 2006

Returns aggregate performance statistics for cached query plans. 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.

ms189741.note(en-US,SQL.90).gifNote:
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.

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. A value of -1 indicates the end of the batch.

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.

creation_time

datetime

Time at which the plan was compiled.

last_execution_time

datetime

Last time at which the plan was executed.

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, in microseconds, that was consumed by executions of this plan since it was compiled.

last_worker_time

bigint

CPU time, in microseconds, that was consumed the last time the plan was executed.

min_worker_time

bigint

Minimum CPU time, in microseconds, that this plan has ever consumed during a single execution.

max_worker_time

bigint

Maximum CPU time, in microseconds, that this plan has ever consumed during a single execution.

total_physical_reads

bigint

Total number of physical reads performed by executions of this plan since it was compiled.

last_physical_reads

bigint

Number of physical reads performed the last time the plan was executed.

min_physical_reads

bigint

Minimum number of physical reads that this plan has ever performed during a single execution.

max_physical_reads

bigint

Maximum number of physical reads that this plan has ever performed during a single execution.

total_logical_writes

bigint

Total number of logical writes performed by executions of this plan since it was compiled.

last_logical_writes

bigint

Number of logical writes performed the last time the plan was executed.

min_logical_writes

bigint

Minimum number of logical writes that this plan has ever performed during a single execution.

max_logical_writes

bigint

Maximum number of logical writes that this plan has ever performed during a single execution.

total_logical_reads

bigint

Total number of logical reads performed by executions of this plan since it was compiled.

last_logical_reads

bigint

Number of logical reads performed the last time the plan was executed.

min_logical_reads

bigint

Minimum number of logical reads that this plan has ever performed during a single execution.

max_logical_reads

bigint

Maximum number of logical reads that this plan has ever performed during a single execution.

total_clr_time

bigint

Time, in microseconds, 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 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, in microseconds, 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, in microseconds, 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, in microseconds, for completed executions of this plan.

last_elapsed_time

bigint

Elapsed time, in microseconds, for the most recently completed execution of this plan.

min_elapsed_time

bigint

Minimum elapsed time, in microseconds, for any completed execution of this plan.

max_elapsed_time

bigint

Maximum elapsed time, in microseconds, for any completed execution of this plan.

Requires VIEW SERVER STATE permission on server.

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

The following example returns information about the top five queries by average CLR time.

SELECT TOP 5 creation_time, last_execution_time, total_clr_time,
    total_clr_time/execution_count AS [Avg CLR Time], last_clr_time,
    execution_count, 
    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
ORDER BY total_clr_time/execution_count DESC;
GO

Release History

12 December 2006

Changed content:
  • Corrected the definitions of the sql_handle, plan_generation_num, and plan_handle columns.

14 April 2006

Changed content:
  • Corrected the example.

5 December 2005

New content:
  • Added a note about the need to query this dynamic management view multiple times to obtain more accurate results if there is a workload currently executing on the server.
  • Explained that the statement_end_offset and statement_start_offset columns are zero-based, and that the value of -1 in the statement_end_offset column indicates the end of the batch.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.