# Finding Plans and Stats for Queries like '%something%'

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_logical_writes
, 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;amp;lt;&amp;amp;gt; 'Proc' --optional restriction by type
-- and cast(qs.max_elapsed_time/1000000./60. as decimal(10,2)) &amp;amp;gt; 10 --optional restriction by longest time run