Know Your DMVs : sys.dm_exec_procedure_stats

Context 

The impetus behind this post is a technical conversation I had with a fellow team member couple of days back. He was doing the performance analysis of a few procedures in a datawarehouse and was using the DMV sys.dm_exec_procedure_stats for the purpose. He had some concerns over the results he was getting and came to discuss with me on the same.
His major issues were the below

1. The names of the procedures returned by the DMV are not always correct
2. When trying to link to the sys.procedures view to get definition some of the rows are getting missed in the output.

He shared with me the below query which he was using and having issues with

select OBJECT_NAME(s.object_id) AS ProcName,

last_execution_time,

execution_count,

last_elapsed_time,

last_execution_time,

last_logical_reads,

last_physical_reads,

last_worker_time

from sys.dm_exec_procedure_stats s

Illustration

I thought of analyzing this myself and setup a sample database with few procedures and executed them. Then I executed the above query to capture their stats and got the below output

As specified earlier I could also get NULL values for few objects. 

I tried adding join to sys.procedures and got the following result

Which was also the same as what my colleague specified

Solution

I understood there is something fundamentally wrong we are doing in the query. 

I analysed the DMV carefully and understood the issue

Just do a select * on the DMV and you'll see the below result

Which clearly indicates the below

• The DMV is always executed from master db context and returns the details of all procedures in the cache from all the databases within the server instance. The database_id field indicates the database to which the procedure belongs. As such joining with catalog views like sys.procedures will only return matches for the objects which belong to the database from which query is executed. Also usage of functions like OBJECT_NAME will also return values for objects based on current database scope  itself  by default so if there's another object with same id in the executing database it will get returned as the object name instead of the actual object name.
• As shown by the above result the query also retrieves the details on extended stored procedures so if we want to see details of procedures alone we need to add a filter on type or type_desc fields

Based on these observations I tweaked the query as below and executed

select OBJECT_NAME(s.object_id) AS ProcName,

st.text AS ProcDefinition,

last_execution_time,

execution_count,

last_elapsed_time,

last_execution_time,

last_logical_reads,

last_physical_reads,

last_worker_time

from sys.dm_exec_procedure_stats s

CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS st

WHERE database_id = DB_ID()

AND Type = 'P'

This would cause the query to retrieve only details of the cached procedures which are in the current database scope. Hence you will get the correct object name listed. It would also cause extended procedures to get filtered out as they will have NULL values for database_id column.

You will get the result as below

If you want full list of procedures to be returned for the instance you need to do a slight tweak in above query to pass database_id also as an argument for OBJECT_NAME function to return the value from the correct database scope as shown below

More Info

Now If you analyze this DMV carefully you will understand that this is a very useful DMV which gives useful details on performance statistics of cached procedures like details on execution count,execution time,worker threads, logical / physical reads etc. One thing to notice is that the result will contain the details of a procedure only until it is cached. Remove the procedure from the cache and it will get excluded from the result as shown.

As you see from the above the procedure got excluded from the result
Hope this article will help you in understanding more on the DMV sys.dm_exec_procedure_stats and its usage and would help you in avoiding issues like above caused due to its incorrect usage.

Referrences

http://msdn.microsoft.com/en-us/library/cc280701.aspx