How To: Automate Continuous SQLServer Activity with Stored Procedures and Powershell Jobs
It’s often useful to be able to run a bunch of stored procedures in the background over a period of time against a test instance.
Read MoreFinding Plans and Stats for Queries Based on Wildcard Text Matching
Category:
performance-tuning
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.
The Challenge
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 Solution
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.
Important Note
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
RELATED POSTS
