Comments posted to this topic are about the item Find Top 5 expensive Queries from a Read IO perspective
Very good observation Keith.
Here's your query
SELECT TOP 5
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
-- Cached SP's By Physical Reads (SQL 2005) Physical reads relate to read I/O pressure (Query 30) (SP Physical Reads)
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.
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
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)