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.
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.
Thanks @Shaulinator, since I'm not as interested in what's running "right now", but rather "what has recently been run", I think Extended events might be the way to go. More lightweight than triggers/profiler I would think? Dec 7, 2016 at 22:59
Also, I think CDC/CDT wouldn't apply since no data is getting changed. Dec 8, 2016 at 18:57
Well, if we don't care about what is running today and what runs today does not change data, then the cache is pretty much your best bet unless you had audit tables that tracked what procs did what. SP_whoisactive can log what has ran and what will run assuming it runs when the query runs. Dec 9, 2016 at 16:20
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.
Thanks @jesijesi, but I executed a proc and then checked the DMV and proc did not show up there. Dec 7, 2016 at 22:57
Any chance the procedure has "with recompile" inside it?– jesijesiDec 8, 2016 at 3:55
good question, but no. I suspect on readonly db's the DMVs are not updated. I ended up going with an Extended Event solution. Dec 9, 2016 at 14:45