Spotlight on SQL Server 10.0.3 - Getting Started Guide

I/O Wait Time Alarm

The I/O Wait Time alarm becomes active when the average I/O wait time for any database file exceeds a threshold. This value is taken over a specific number of background collections.

Sustained high I/O wait times are a good indicator that you have a disk subsystem bottleneck and that I/O device service times will be degraded.

When this alarm is current, you should look at:

• The I/O page on the Diagnostics drilldown to see what is contributing to I/O consumption. If SQL Server is contributing to I/O consumption look at the following:

  • The I/O by File page on the SQL Activity drilldown to see which files are waiting on I/O operations. High rates of wait on log files may indicate that the device on which the log file resides is not able to keep up.

  • The Sessions page on the SQL Activity drilldown to see which SQL Server sessions are generating high amounts of disk activity. The SQL and the query plan are available for further analysis by clicking a row in the grid.

  • The Top SQL Statements drilldown. Looking at the top statements by Avg Logical Reads/Writes shows SQL that may be doing large amounts of logical I/O. This logical I/O may result in unnecessary physical I/O.

Tips:

• You can configure this alarm to ignore certain values. See "Configure an Alarm to Ignore Certain Values" (page 1) for more information.

• You can configure different thresholds for specific database files. See "Configure Keyed Alarms" (page 1) for more information.

Related Topics

Spotlight on SQL Server Alarms (page 1)

Diagnostics Drilldown (page 1)

SQL Activity Drilldown (page 1)

Top SQL Statements Drilldown (page 1)