sys.dm_exec_procedure_stats (Transact-SQL)

 

Updated: August 1, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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.

In Azure SQL Database, dynamic management views cannot expose information that would impact database containment or expose information about other databases the user has access to. To avoid exposing this information, every row that contains data that doesn’t belong to the connected tenant is filtered out.

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.

NOTE for Azure SQL Data Warehouse! To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_exec_procedure_stats.

Column nameData typeDescription
database_idintDatabase ID in which the stored procedure resides.
object_idintObject identification number of the stored procedure.
typechar(2)Type of the object:

P = SQL stored procedure

PC = Assembly (CLR) stored procedure

X = Extended stored procedure
type_descnvarchar(60)Description of the object type:

SQL_STORED_PROCEDURE

CLR_STORED_PROCEDURE

EXTENDED_STORED_PROCEDURE
sql_handlevarbinary(64)This can be used to correlate with queries in sys.dm_exec_query_stats that were executed from within this stored procedure.
plan_handlevarbinary(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.

Will always be 0x000 when a natively compiled stored procedure queries a memory-optimized table.
cached_timedatetimeTime at which the stored procedure was added to the cache.
last_execution_timedatetimeLast time at which the stored procedure was executed.
execution_countbigintNumber of times that the stored procedure has been executed since it was last compiled.
total_worker_timebigintTotal amount of CPU time, in microseconds, that was consumed by executions of this stored procedure 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_timebigintCPU time, in microseconds, that was consumed the last time the stored procedure was executed. 1
min_worker_timebigintMinimum CPU time, in microseconds, that this stored procedure has ever consumed during a single execution. 1
max_worker_timebigintMaximum CPU time, in microseconds, that this stored procedure has ever consumed during a single execution. 1
total_physical_readsbigintTotal number of physical reads performed by executions of this stored procedure since it was compiled.

Will always be 0 querying a memory-optimized table.
last_physical_readsbigintNumber of physical reads performed the last time the stored procedure was executed.

Will always be 0 querying a memory-optimized table.
min_physical_readsbigintMinimum number of physical reads that this stored procedure has ever performed during a single execution.

Will always be 0 querying a memory-optimized table.
max_physical_readsbigintMaximum number of physical reads that this stored procedure has ever performed during a single execution.

Will always be 0 querying a memory-optimized table.
total_logical_writesbigintTotal number of logical writes performed by executions of this stored procedure since it was compiled.

Will always be 0 querying a memory-optimized table.
last_logical_writesbigintNumber 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_writesbigintMinimum number of logical writes that this stored procedure has ever performed during a single execution.

Will always be 0 querying a memory-optimized table.
max_logical_writesbigintMaximum number of logical writes that this stored procedure has ever performed during a single execution.

Will always be 0 querying a memory-optimized table.
total_logical_readsbigintTotal number of logical reads performed by executions of this stored procedure since it was compiled.

Will always be 0 querying a memory-optimized table.
last_logical_readsbigintNumber of logical reads performed the last time the stored procedure was executed.

Will always be 0 querying a memory-optimized table.
min_logical_readsbigintMinimum number of logical reads that this stored procedure has ever performed during a single execution.

Will always be 0 querying a memory-optimized table.
max_logical_readsbigintMaximum number of logical reads that this stored procedure has ever performed during a single execution.

Will always be 0 querying a memory-optimized table.
total_elapsed_timebigintTotal elapsed time, in microseconds, for completed executions of this stored procedure.
last_elapsed_timebigintElapsed time, in microseconds, for the most recently completed execution of this stored procedure.
min_elapsed_timebigintMinimum elapsed time, in microseconds, for any completed execution of this stored procedure.
max_elapsed_timebigintMaximum elapsed time, in microseconds, for any completed execution of this stored procedure.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

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.

Requires VIEW SERVER STATE permission on server.

Statistics in the view are updated when a stored procedure execution completes.

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;  

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

Community Additions

ADD
Show: