Troubleshooting I/O Bottleneck

/*

Troubleshooting I/O bottleneck.
Applies to: SQL Server 2005.
www.SqlCoffee.com

*/

-- The following DMV identifies and analyze the queries that are generating the most I/Os.
-- Use Database Engine Tuning Advisor to find missing indexes.

USE master
SELECT top 20 (qs.total_logical_reads/qs.execution_count) as LogicalReads,
(qs.total_logical_writes/qs.execution_count) as LogicalWrites,
(qs.total_physical_reads/qs.execution_count) as PhysicalReads,
qs.Execution_count, qs.sql_handle, qs.plan_handle, qt.Text
FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
ORDER BY (qs.total_logical_reads + qs.total_logical_writes) Desc

-- Use the following query to examine the query plan
SELECT * FROM sys.dm_exec_query_plan (<plan_handle>)

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.