3

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.

mustaccio
  • 28,207
  • 24
  • 60
  • 76
manjunathnarayani
  • 31
  • 1
  • 3

2 Answers2

3

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

Community
  • 1
KASQLDBA
  • 7,203
  • 6
  • 30
  • 53
3

I had a similar problem with a Stored Procedure that did not show up in the dm_exec_procedure_stats view. No "WITH RECOMPILE" clause and I tried to DROP and CREATE the PROCEDURE again. New object_id but still missing from the dm_exec_procedure_stats view.

The SP is using a # temp table, and it actually also had a ALTER TABLE statement adding a few columns in the temp table.

I changed that so the temp table is initially created with all columns, and VIOLA ... it appeared in the dm_exec_procedure_stats view :)

So I think my approach with altering object during runtime may be the reason that an execution plan is not possible. Don't know but it worked :)

Ronnie Petersen
  • 31
  • 2