By: SolarWinds on March 22, 2013

Waits on this wait type can result from inefficient SQL statements. Tuning the SQL statement will allow it to execute while reading less data, which will reduce wait times on this event.

If the statements are already tuned or cannot be tuned (because the application is provided by a vendor or other reasons), you might try one of the following steps:

  1. Identify disk bottlenecks, using counters such as PhysicalDisk (Disk Sec/read, Disk Sec/write, Disk Queues). If disk bottlenecks are found, any of the following will reduce wait times:
    1. Add additional I/O bandwidth, i.e. add faster disks
    2. Balance I/O from busy devices to less busy devices
    3. It’s possible the query usually executes well, but periodically the optimizer may choose a bad query plan.
  2. Use SolarWinds Database Performance Analyzer  to review this data:
    1. If wait time for the IO_COMPLETION wait type are a significant percentage of the total wait time, look for system-wide disk bottlenecks.
    2. If wait time is high for IO_COMPLETION on only a few queries, tune those SQL statements.
    3. Run a Top SQL waiting on a Single Wait report to determine the queries waiting on this event.
  3. Check fn_virtualfilestats:
    1. Use the system table-valued function fn_virtualfilestats to check IoStallMS value. IoStallMS is the cumulative number of milliseconds of I/O waits for a particular file. If IoStallMS is inordinately high for one or more files, you have a disk bottleneck. To display IoStallMS, execute thisquery:
SELECT * FROM ::fn_virtualfilestats(dbid,file#)

Leave a Reply