sys.dm_exec_procedure_stats (Transact-SQL)
Returns aggregate performance statistics for cached stored procedures. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view. At that time, a Performance Statistics SQL trace event is raised similar to sys.dm_exec_query_stats.
Note
An initial query of sys.dm_exec_procedure_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 |
|---|---|---|
database_id |
int |
Database ID in which the stored procedure resides. |
object_id |
int |
Object identification number of the stored procedure. |
type |
char(2) |
Type of the object: P = SQL stored procedure PC = Assembly (CLR) stored procedure X = Extended stored procedure |
type_desc |
nvarchar(60) |
Description of the object type: SQL_STORED_PROCEDURE CLR_STORED_PROCEDURE EXTENDED_STORED_PROCEDURE |
sql_handle |
varbinary(64) |
This can be used to correlate with queries in sys.dm_exec_query_stats that were executed from within this stored procedure. |
plan_handle |
varbinary(64) |
Identifier for the in-memory plan. This identifier is transient and remains constant only while the plan remains in the cache. This value may be used with the sys.dm_exec_cached_plans dynamic management view. |
cached_time |
datetime |
Time at which the stored procedure was added to the cache. |
cached_time |
datetime |
Time at which the stored procedure was added to the cache. |
last_execution_time |
datetime |
Last time at which the stored procedure was executed. |
execution_count |
bigint |
Number of times that the stored procedure 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 stored procedure since it was compiled. |
last_worker_time |
bigint |
CPU time, in microseconds, that was consumed the last time the stored procedure was executed. |
min_worker_time |
bigint |
Maximum CPU time, in microseconds, that this stored procedure has ever consumed during a single execution. |
max_worker_time |
bigint |
Maximum CPU time, in microseconds, that this stored procedure has ever consumed during a single execution. |
total_physical_reads |
bigint |
Total number of physical reads performed by executions of this stored procedure since it was compiled. |
last_physical_reads |
bigint |
Number of physical reads performed the last time the stored procedure was executed. |
min_physical_reads |
bigint |
Minimum number of physical reads that this stored procedure has ever performed during a single execution. |
max_physical_reads |
bigint |
Maximum number of physical reads that this stored procedure has ever performed during a single execution. |
total_logical_writes |
bigint |
Total number of logical writes performed by executions of this stored procedure since it was compiled. |
last_logical_writes |
bigint |
Number of logical writes performed the last time the stored procedure was executed. |
min_logical_writes |
bigint |
Minimum number of logical writes that this stored procedure has ever performed during a single execution. |
max_logical_writes |
bigint |
Maximum number of logical writes that this stored procedure has ever performed during a single execution. |
total_logical_reads |
bigint |
Total number of logical reads performed by executions of this stored procedure since it was compiled. |
last_logical_reads |
bigint |
Number of logical reads performed the last time the stored procedure was executed. |
min_logical_reads |
bigint |
Minimum number of logical reads that this stored procedure has ever performed during a single execution. |
max_logical_reads |
bigint |
Maximum number of logical reads that this stored procedure has ever performed during a single execution. |
total_elapsed_time |
bigint |
Total elapsed time, in microseconds, for completed executions of this stored procedure. |
last_elapsed_time |
bigint |
Elapsed time, in microseconds, for the most recently completed execution of this stored procedure. |
min_elapsed_time |
bigint |
Minimum elapsed time, in microseconds, for any completed execution of this stored procedure. |
max_elapsed_time |
bigint |
Maximum elapsed time, in microseconds, for any completed execution of this stored procedure. |
Permissions
Requires VIEW SERVER STATE permission on server.
Remarks
Statistics in the view are updated when a stored procedure execution completes.
Examples
The following example returns information about the top ten stored procedures identified by average elapsed time.
SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
d.cached_time, d.last_execution_time, d.total_elapsed_time, d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_procedure_stats AS d
ORDER BY [total_worker_time] DESC;