Post reply

identify last big query that ran

  • November 30, 2012 at 7:10 am

    #271448

    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

    #1564369

    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

    #1564429

    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

    http://www.databasejournal.com/features/mssql/article.php/3802936/Finding-the-Worst-Performing-T-SQL-Statements-on-an-Instance.htm

    ______________________________________________________________________________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