Proc caching on read only db's/replicas

Question

I've been given the task of finding which stored procs are being called in our database. To that end I started querying sys.dm_exec_cached_plans. All is well and good, until I go over to our instance that has a read only copy of our DB and a read only secondary in our AG. It looks to me like the DMV does not get updated when procs are executed. Makes sense I suppose, but it there another way? I can go the extended event route if I have to, but the DMVs are so much easier.

Answer 1

You're getting close, you're looking at the cached plans which says what has been executed. That doesn't tell you what's being executed right now, or yesterday, or in ten minutes.

Try this to see what's running right now.

SELECT  s.loginame, db_name(s.dbid) name, s.hostname,  
 s.program_name,  
 s.sql_handle, s.stmt_start, s.stmt_end,  
 s.spid, CONVERT(smallint, s.waittype) waittype,  
 s.lastwaittype, s.ecid, s.waittime , s.blocked,  
 r.plan_handle,  r.statement_start_offset, r.statement_end_offset, r.query_plan_hash, r.start_time, 
 q.plan_generation_num 
FROM master..sysprocesses AS s WITH(NOLOCK)  
LEFT OUTER JOIN sys.dm_exec_requests AS r WITH(NOLOCK)  
   ON (s.spid = r.session_id) 
LEFT OUTER JOIN sys.dm_exec_query_stats q  
  ON q.plan_handle = r.plan_handle  
  AND q.statement_start_offset = r.statement_start_offset  
  AND q.statement_end_offset = r.statement_end_offset 
WHERE (s.cmd<>'AWAITING COMMAND'  
                AND s.cmd NOT LIKE '%BACKUP%'  
                AND s.cmd NOT LIKE '%RESTORE%'  
                AND s.cmd NOT LIKE 'FG MONITOR%' 
                AND s.hostprocess > '' 
                AND s.spid>50  
                AND s.spid<>@@SPID)   
AND lastwaittype NOT IN ('WAITFOR', 'SLEEP_TASK')  
ORDER BY s.spid, s.ecid ASC 

I would use SP_Whoisactive and log into a table for monitoring and seeing what's executing. Note, this is not the best way. This only captures what is running in real time when the query runs.

The next best way is to apply CDC / CDT / Trigger notifications on the databases / running SQL Profiler.

Answer 2

It looks to me like the DMV does not get updated when procs are executed.

That is not true. if the procs are running on the secondary replica it will be cached. It could mean either that the stored procs are not running or its getting evicted out of cache and are not in the cache anymore.

Off course querying the cache does not give you a complete picture and It might be better off to use profiler or xevents to track all the storedprocs.