patternMinor
No rows in sys.dm_exec_procedure_stats for a particluar stored procedure
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
Please suggest what could be various reason for this as I can see my stored procedure has been executed multiple times.
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
Also,
You can look in the plan cache to get a pretty good idea of stored procedure usage.
There are various other queries via which you can analyse the metrics for your stored procedures. Monitoring stored procedure usage
Also, read here
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.