November 30, 2012 at 7:10 am
Just had a massive performance heavy query that timed out causing multiple locks. Is there a way i can identify which query it was?
I tried using Dave Pinals script http://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/ but this only shows cached query plans.
Is there a way maybe in the log files to check this or any other alternative ways to identify the most performance hungry queries that ran in the day?
Thanks
November 30, 2012 at 7:51 am
If the query just ran, the plan would still be cached and it should fetch that gluttonous query. If the SPID is still active and you know the #, you could also run a DBCC INPUTBUFFER to see what that query was.
November 30, 2012 at 8:47 am
Chances are you can find your bad query by looking at the logical_reads, elapsed/worker_time, etc by using this:
SELECT
*
FROM
sys.dm_exec_query_stats qs
INNER JOIN sys.dm_exec_cached_plans cp
ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE
attribute = 'dbid'
AND DB_NAME(CAST(pa.value AS int)) = 'YourDB'
Also, here's a good article by Gregory Larson that has some great suggestions
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply