2

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 - No , it doesn't have WITH RECOMPILE option int. There are 4 procedure gets executed by SSIS packages which runs for like 7 hrs all together and none of them is listed in DMV. I am not sure if there is heavy load on the server. Please suggest how to find this and if the plan is getting shunted out. – manjunathnarayani Jul 20 '15 at 15:56
  • What is the exact query you are running against the DMV? Is it possible you are filtering by database but the procedure is actually in another database (or it's a nested call from another details)? More details = better chance to help. – Aaron Bertrand Jul 20 '15 at 19:34
2

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 :)

1

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

  • @ KASQLDBA - yes , I am using sql server 2012 (SP1) – manjunathnarayani Jul 20 '15 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.