Chapter 48. Query performance and disk I/O counters
SQL Server provides an excellent set of tools for troubleshooting query performance problems. At the top of the list, you have tools for capturing and analyzing query plans, tools for checking whether statistics are up to date or an index is useful, tools for capturing the time and resource cost of processing a query, and tools to help optimize SQL queries.
What you do not typically find in the toolset recommended for troubleshooting SQL Server query performance problems are the disk I/O performance counters. This is understandable because the disk I/O counters are the statistics at the operating system drive level. Although they can help you determine whether your disk subsystem is a bottleneck in the overall resource consumption of your SQL Server system, they do not generally reveal useful information on processing a particular query.
In some scenarios you can use the disk I/O performance counters in troubleshooting query performance problems. To completely ignore the disk I/O performance counters is to miss out on a powerful tool for troubleshooting SQL Server query problems.
Before diving into the query performance scenarios, let us first highlight the basics of disk I/Os, and review some salient disk I/O considerations in SQL Server query processing.
Disk I/Os are expensive and random disk I/Os are very expensive.
