The sys.dm_exec_procedure_stats is one of my favorite DMV’s that I will use when I am looking into the performance of a stored procedure. This DMV returns information about the performance of a cached stored procedures. The information within this DMV is aggregated since the last time that the DMV statistics were reset. To better illustrate the benefits of this DMV, let run a quick query against my AdventureWorks2012 database.
SELECT * FROM sys.dm_exec_procedure_stats
WHERE database_id = db_id('AdventureWorks2012')
By looking at the results returned by this DMV, we can gather a wealth of knowledge about how a stored procedure may be performing. We can easily obtain the name of the stored procedure by using the OBJECT_NAME function. This query also returns the sql_handle and plan_handle and if you have been keeping up with this month long series on Dynamic Management Objects, then you know how we can use these two columns to obtain the actual sql text and the execution plan for these stored procedures. We are also able to see when the stored procedure was added to the cache and the last time it executed as well as how many times it has executed.
From a performance perspective, we are able to view statistics about the min, max, last, and total worker time a stored procedure used, which represents the amount of CPU time in microseconds that this stored procedure consumed.
We are able to see min, max, last, and total statistics for logical reads/writes and physical reads/writes. The duration that the stored procedure took to execute is also presented in the result set of this DMV represented as the min, max, last, and total elapsed times.
When looking for stored procedures that may need optimizations, this is an excellent place to start. Especially when you are needing a historical view and not necessarily a look at currently executing stored procedures. This is definitely one of my favorites!!
For more information of this Dynamic Management View, please see the below Books Online link: