The core functionality of database systems is to retrieve data, and this is reflected in the amount of I/O involved, total CPU time, logical reads/writes etc. We can look at the queries that are most expensive (taking more time to execute), use the most I/O to determine whether they can be changed to retrieve data more efficiently.

The script below helps to identify the top 10 queries that use the most I/O as ordered by Total IO, taking more time to execute (means most expensive) across all databases on the server.

Query:

• The query calculates the Total IO by summing the columns total_logical_reads and total_logical_writes.
• The Average IO is calculated by dividing the sum of the total_logical_reads and total_logical_writes by the number of times the query has executed (execution_count).
• The other output column are:
  • Database Name
  • Individual Query
  • Parent Query (from where the query is executing)
  • Total elapsed time in seconds
  • Query’s cached plan (query_plan) 
  • The output is sorted by Total IO in descending order as of now.

You can modify the query as per your requirement like filter on database name only, set required Order by.

SELECT TOP 10
        [Individual Query] = SUBSTRING(qt.TEXT,
                                       ( qs.statement_start_offset / 2 ) + 1,
                                       ( ( CASE qs.statement_end_offset
                                             WHEN -1 THEN DATALENGTH(qt.TEXT)
                                             ELSE qs.statement_end_offset
                                           END - qs.statement_start_offset )
                                         / 2 ) + 1),
        [Total IO] = ( qs.total_logical_reads + qs.total_logical_writes ),
        [Average IO] = ( qs.total_logical_reads + qs.total_logical_writes ) / qs.execution_count,
        [Execution Count] = qs.execution_count,
        [Total Logical Reads] = qs.total_logical_reads,
        [Total Logical Writes] = qs.total_logical_writes,
        [Total Worker Time/CPU time] = qs.total_worker_time,
        [Total Elapsed Time In Seconds] = qs.total_elapsed_time / 1000000,
        [Parent Query] = qt.text,
        [DatabaseName] = DB_NAME(qt.dbid),
        [Query Plan] = qp.query_plan
FROM    sys.dm_exec_query_stats qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
        CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY [Total IO] DESC --IO
-- ORDER BY [Total Elapsed Time In Seconds] DESC  --elapsed time
-- ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

 To reduce reads you need to look at a couple of things, first being query design, secondly being indexing. If your query is pulling a large number of records that could be filtered by getting a smaller set prior to pulling that data then you can always cut down on reads that way. With improved indexing, specifically with covering indexes, you can reduce the number of pages that are being read as well.

I hope this will be helpful somewhere sometime. J

Appendix

• Logical reads - number of pages read from the data cache
  • Logical read indicates total number of data pages needed to be accessed from data cache to process query. It is very possible that logical read will access same data pages many times, so count of logical read value may be higher than actual number of pages in a table. Usually the best way to reduce logical read is to apply correct index or to rewrite the query.
• Physical reads - number of pages read from disk
  • Physical read indicates total number of data pages that are read from disk. In case no data in data cache, the physical read will be equal to number of logical read in most of the cases. And usually it happens for first query request. And for subsequent same query request the number will be substantially decreased because the data pages have been in data cache.