Find Top 5 expensive Queries from a Read IO perspective

  • Bodhisatya

    SSC Veteran

    Points: 283

    Comments posted to this topic are about the item Find Top 5 expensive Queries from a Read IO perspective

  • keith.ketterer

    SSC Enthusiast

    Points: 176

    When I run this on my development instance it returns no records. So I did some digging and found this may be a bug in SQL Server 2008 .

  • Bodhisatya

    SSC Veteran

    Points: 283

    Very good observation Keith.

  • ChrisM@Work

    SSC Guru

    Points: 186120

    Here's your query


    qt.text AS 'SQL',

    qstats.total_physical_reads AS 'Total Physical Reads',

    qstats.total_physical_reads/qstats.execution_count AS 'Average Physical Reads',

    qstats.execution_count AS 'Execution Count',

    qstats.total_worker_time/qstats.execution_count AS 'Average Worker Time',

    qstats.total_worker_time AS 'Total Worker Time',

    DATEDIFF(Hour, qstats.creation_time, GetDate()) AS 'AgeInCache In Hours',

    db_name(qt.dbid) AS 'Database Name'

    FROM sys.dm_exec_query_stats AS qstats

    CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS qt

    WHERE qt.dbid = db_id() -- Filter by current database

    ORDER BY qstats.total_physical_reads DESC

    Here's Glenn Berry's [/url]

    -- Cached SP's By Physical Reads (SQL 2005) Physical reads relate to read I/O pressure (Query 30) (SP Physical Reads)

    SELECT TOP(25)

    qt.[text] AS [SP Name],




    total_logical_reads/qs.execution_count AS [AvgLogicalReads],

    qs.execution_count AS [Execution Count],

    qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second],

    qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],

    qs.total_worker_time AS [TotalWorkerTime],

    qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],

    DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]

    FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

    CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

    WHERE qt.[dbid] = DB_ID() -- Filter by current database

    AND qs.total_physical_reads > 0

    ORDER BY qs.total_physical_reads DESC OPTION (RECOMPILE);

    Imitation is the sincerest form of flattery.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]

    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • Bodhisatya

    SSC Veteran

    Points: 283


    Search up articles or scripts on sql and u will find so many queries which are similar on similar topics. Doesn't mean that each author has imitated the other. and in case u have missed out, my query is diff from that of Glenn's. the nature of the query is such that you have to use the same dmv's

    LEARN to be honest and u will find honesty in others.



  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

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

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