HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

No rows in sys.dm_exec_procedure_stats for a particluar stored procedure

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
storedrowsparticluardm_exec_procedure_statsproceduresysfor

Problem

I am running DMV queries to get stats for my stored procedure, but I am not finding any rows against this SP under sys.dm_exec_procedure_stats.

Please suggest what could be various reason for this as I can see my stored procedure has been executed multiple times.

Solution

By using this DMV, you can returns metrics for stored procedures, but
it does not directly identify the stored procedure by name. Instead,
the DMV only identifies the object_id, and a database_id for each
stored procedure: Therefore to identify the actual stored procedure
name, that the performance figures belong to, you should either join
the output of this DMV with one of the system views within the
appropriate database, or use a few metadata functions

Therefore you should consider using sys.dm_exec_query_stats along with one you mentioned in you're question to get stats.

Also,

You can look in the plan cache to get a pretty good idea of stored procedure usage.

SELECT DB_NAME(st.dbid) DBName
      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
      ,OBJECT_NAME(st.objectid,dbid) StoredProcedure
      ,max(cp.usecounts) Execution_count
 FROM sys.dm_exec_cached_plans cp
         CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
   group by cp.plan_handle, DB_NAME(st.dbid),
            OBJECT_SCHEMA_NAME(objectid,st.dbid), 
   OBJECT_NAME(objectid,st.dbid) 
 order by max(cp.usecounts)


There are various other queries via which you can analyse the metrics for your stored procedures. Monitoring stored procedure usage

Also, read here

Code Snippets

SELECT DB_NAME(st.dbid) DBName
      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
      ,OBJECT_NAME(st.objectid,dbid) StoredProcedure
      ,max(cp.usecounts) Execution_count
 FROM sys.dm_exec_cached_plans cp
         CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
   group by cp.plan_handle, DB_NAME(st.dbid),
            OBJECT_SCHEMA_NAME(objectid,st.dbid), 
   OBJECT_NAME(objectid,st.dbid) 
 order by max(cp.usecounts)

Context

StackExchange Database Administrators Q#107524, answer score: 3

Revisions (0)

No revisions yet.