Find Top 5 expensive Queries from a Read IO perspective

Question

Find Top 5 expensive Queries from a Read IO perspective

Bodhisatya

Old Hand

Points: 313
More actions
September 26, 2013 at 10:33 pm

#283150

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

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

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

keith.ketterer SSC Enthusiast Points: 176 More actions · Answer 1

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 Old Hand Points: 313 More actions · Answer 2

Bodhisatya

Old Hand

Points: 313

September 28, 2013 at 10:00 pm

#1654318

Very good observation Keith.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

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],

qs.total_physical_reads,

total_logical_reads,

qs.max_logical_reads,

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

Bodhisatya Old Hand Points: 313 More actions · Answer 4

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.

Regards

Bodhisatya

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

April 27, 2016 at 4:56 pm

#1875115

Thanks for the script.