DBA tool--dmv--Viewing stored procedure execution information through Sys.dm_exec_procedure_stats

Original: DBA tool--dmv--View stored procedure execution information through Sys.dm_exec_procedure_stats

For DBAs, there is often some information about the phone's stored procedure:

1. How many times did you execute
2. How does the execution plan
3. What is the average read and write performed
4. How long does it take to execute on average

Column Name	Data Type	Description
database_id	int	The database ID where the stored procedure resides.
object_id	int	The object identification number of the stored procedure.
type	char (2)	Type of object: P = SQL Stored Procedure PC = assembly (CLR) stored procedure X = Extended Stored Procedure
Type_desc	nvarchar ()	Description of the object type: Sql_stored_procedure Clr_stored_procedure Extended_stored_procedure
sql_handle	varbinary (+)	Can be used to associate with queries executed from this stored procedure in sys.dm_exec_query_stats .
plan_handle	varbinary (+)	The identifier for the in-memory schedule. The identifier is transient and remains the same only if it is scheduled to remain in the cache. This value can be used with the sys.dm_exec_cached_plans dynamic management view.
Cached_time	datetime	The time that the stored procedure was added to the cache.
Cached_time	datetime	The time that the stored procedure was added to the cache.
Last_execution_time	datetime	The last time the stored procedure was executed.
Execution_count	bigint	The number of times the stored procedure has been executed since it was last compiled.
Total_worker_time	bigint	The total amount of CPU time (in microseconds) that this stored procedure has been executing since it was compiled.
Last_worker_time	bigint	The CPU time, in microseconds, that the stored procedure was last executed.
Min_worker_time	bigint	The maximum CPU time (in microseconds) that this stored procedure has consumed during a single execution.
Max_worker_time	bigint	The maximum CPU time (in microseconds) that this stored procedure has consumed during a single execution.
total_physical_reads	bigint	The total number of physical reads performed during the execution of this stored procedure since it was compiled.
last_physical_reads	bigint	The number of physical reads performed when the stored procedure was last executed.
min_physical_reads	bigint	The minimum number of physical reads performed by the stored procedure during a single execution.
max_physical_reads	bigint	The maximum number of physical reads that the stored procedure performed during a single execution.
total_logical_writes	bigint	The total number of logical writes performed during the execution of this stored procedure since it was compiled.
last_logical_writes	bigint	The number of logical writes performed when the stored procedure was last executed.
min_logical_writes	bigint	The minimum number of logical writes that the stored procedure performed during a single execution.
max_logical_writes	bigint	The maximum number of logical writes that the stored procedure performed during a single execution.
total_logical_reads	bigint	The total number of logical reads performed by this stored procedure during execution since it was compiled.
last_logical_reads	bigint	The number of logical reads performed when the stored procedure was last executed.
min_logical_reads	bigint	The minimum number of logical reads performed by the stored procedure during a single execution.
max_logical_reads	bigint	The maximum number of logical reads that the stored procedure performed during a single execution.
Total_elapsed_time	bigint	Total time (in microseconds) to complete the execution of this stored procedure.
Last_elapsed_time	bigint	The time, in microseconds, that the recent execution of this stored procedure has been completed.
Min_elapsed_time	bigint	The minimum time (in microseconds) to complete the execution of this stored procedure at any one time.
Max_elapsed_time	bigint	The maximum amount of time, in microseconds, to complete the execution of this stored procedure at any one time.

The following statement returns the longest-consuming stored procedure information for the first 10 sentences:

SELECT TOP        a.object_id,        a.database_id,        db_name (ISNULL (a.database_id, ')) ' DatabaseName ',        object_name (object_id, database_id) ' proc NAME ',        a.cached_time,        a.last_execution_time,        A.total_ Elapsed_time,        a.total_elapsed_time/a.execution_count as [Avg_elapsed_time],        A.execution_count,        A.total_physical_reads/a.execution_count avg_physical_reads,        a.total_logical_writes,        A.total_logical_ Writes/a.execution_count avg_logical_reads,        a.last_elapsed_time,        a.total_elapsed_time/a.execution_ Count Avg_elapsed_time,        b.text,        c.query_planfrom    sys.dm_exec_procedure_stats as a cross        APPLY Sys.dm_exec_sql_text (a.sql_handle) b cross        APPLY sys.dm_exec_query_plan (A.plan_handle) CORDER by [Total_worker_ TIME] DESC; GO

DBA tool--dmv--to view stored procedure execution information through Sys.dm_exec_procedure_stats