Find Top 5 expensive Queries from a Read IO perspective

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

  • 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 .

  • Very good observation Keith.

  • 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.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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

  • Chris,

    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.



  • Thanks for the script.

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

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