Post reply

TOP 5 CPU queries running against a given database

  • March 20, 2009 at 9:56 am

    #207614

    In a given databse how do you determine the top 5 (by CPU) t-sql queries?

    I use this (but it often returns NULL which isn't of great help):

    select top 5

    qt.text as 'stored proc',

    qs.total_worker_time as 'total worker time',

    qs.total_worker_time/qs.execution_count as 'average worker time',

    qs.execution_count as 'execution count',

    isnull(qs.execution_count/datediff(second, qs.creation_time, getdate()), 0) as 'calls/second',

    isnull(qs.total_elapsed_time/qs.execution_count, 0) as 'average elapsed time',

    qs.max_logical_reads, qs.max_logical_writes,

    datediff(minute, qs.creation_time, getdate()) as 'age in cache'

    from sys.dm_exec_query_stats as qs

    cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

    where qt.dbid = db_id()

    order by qs.total_worker_time desc

    TIA,

    barkingdog

  • March 20, 2009 at 1:54 pm

    #963767

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    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

Viewing 2 posts - 1 through 1 (of 1 total)

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