I often need to find a query plan in the cache for a process that has run long overnight. Typically I’ll be able to figure out from our logging some of the tables involved in the query. Sometimes I will have most of the executing text but won’t know exactly what dates or reference points were included.
Even when I have enough information to get an estimated plan, it’s usually really helpful if I can pull the actual plan out of the cache along with runtime statistics.
The query below is what I use at this point to try to find these plans– I also sometimes use it just to look for long running queries in general.
One note to remember– the last_execution_time field is the time of the plan activities at the last execution. So if you’re looking for a query that ran for an hour, this time would show at the beginning of that execution. (The logging on my systems is done after a batch of activities complete, so I always have to do a bit of work to figure out approximately when the activity would have started and look around that time for the plan.)
--Query plans and text looking for a given pattern SELECT TOP 100 qs.Plan_handle , cp.objtype , qs.last_execution_time , cp.useCounts , st. , query_plan , lastElapsedTimeMinutes = cast(qs.last_elapsed_time/1000000./60. as decimal(10,2)) , maxElapsedTimeMinutes= cast(qs.max_elapsed_time/1000000./60. as decimal(10,2)) , totalElapsedTimeMinutes= cast(qs.total_elapsed_time/1000000./60. as decimal(10,2)) , totalWorkerTimeMinutes=cast(qs.total_worker_time/1000000./60. as decimal(10,2)) , lastWorkerTimeMinutes=cast(qs.last_worker_time/1000000./60. as decimal(10,2)) , qs.total_physical_reads , qs.total_logical_reads , qs.total_logical_writes , qs.last_physical_reads , qs.last_logical_reads , qs.last_logical_writes FROM sys.dm_exec_query_stats AS qs JOIN sys.dm_exec_cached_plans cp on qs.plan_handle=cp.plan_handle CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) as st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) where st.text not like '%sys.dm_exec_query_stats%' --ignore queries looking for the plan and st.text like '%InvalidPlacementAdDay%' -- look for queries against this table -- and cp.objtype &amp;lt;&amp;gt; 'Proc' --optional restriction by type -- and cast(qs.max_elapsed_time/1000000./60. as decimal(10,2)) &amp;gt; 10 --optional restriction by longest time run --and last_execution_time &amp;gt; dateadd(hh,-1,getdate()) ORDER BY last_execution_time DESC GO