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

on May 16, 2009

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 <> 'Proc' --optional restriction by type
    -- and cast(qs.max_elapsed_time/1000000./60. as decimal(10,2)) > 10 --optional restriction by longest time run
    --and last_execution_time > dateadd(hh,-1,getdate())
ORDER BY
    last_execution_time DESC
GO