Find Top 5 expensive Queries from a Read IO perspective


The below written query helps us find the most expensive queries from a read IO perspective. The DMV that we have used in this script is sys.dm_exec_query_stats and the function that we have used is sys.dm_exec_sql_text.

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()
ORDER BY qstats.total_physical_reads DESC

Advertisements

Tagged: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: