Post reply

DMV to gather history on queries run 2 days ago ?

  • January 31, 2012 at 11:03 am

    #391441

    Is there any way to find if there was any query run which had high CPU and IO and ran for x number of sec/min ?

    I am investigating a CPU spike which happened last day. All other logs and non sql processes show no indication of anything.. wondering if someone has an SQL which can dig the past

  • January 31, 2012 at 11:51 am

    #1440871

    SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query],

    deqs.total_elapsed_time/1000000 as Total_Execution_time,

    deqs.total_worker_time/1000000 as CPU_time

    FROM sys.dm_exec_query_stats AS deqs

    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

    ORDER BY deqs.last_execution_time DESC

  • January 31, 2012 at 12:25 pm

    #1440904

    Thanks much !

  • January 31, 2012 at 1:12 pm

    #1440937

    Just bear in mind that the query given only returns data for queries whose plans are still in cache, and unless the queries are frequently run and the server has not restarted, queries run 2 days ago may well have had their plans removed from cache.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 2, 2012 at 5:55 am

    #1441742

    And the query stats are aggregate only. If the query was called multiple times and only one of them spiked, you won't be able to tell much about the spike.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply