April 27, 2012 at 5:27 am
Dear All, I'm running the following Stored Procedure to determine how many times a Stored Procedure has been run, but is there any way of filtering it out which Date it Started from please?
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) DESC
Thank you in advance!
April 27, 2012 at 5:46 am
sys.dm_exec_query_stats has a last_execution_time,
Does it help if you filter with that?
DECLARE @startfilter DATETIME
SET @startfilter = GETDATE() - 10
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
JOIN sys.dm_exec_query_stats qs
ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
where DB_NAME(st.dbid) is not null
and cp.objtype = 'proc'
AND last_execution_time > @StartFilter
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)
April 27, 2012 at 6:41 am
Just remember that this is only going to show the executions since the last time a given query was loaded into cache. Recompiles or aging out of cache will change the counts. You can get the create date from cache to see when the query was created in cache. That should help in terms of determining age.
And for those who had a hard time reading the single continous line of code:
SELECTDB_NAME(st.dbid) DBName,
OBJECT_SCHEMA_NAME(st.objectid, dbid) SchemaName,
OBJECT_NAME(st.objectid, dbid) StoredProcedure,
MAX(cp.usecounts) Execution_count
FROMsys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHEREDB_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) DESC
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply