Average I/O stalls

| 2 Comments | 5,774 views
Categories: ,

VN:F [1.9.17_1161]
Rating: 4.3/5 (4 votes cast)

Explanation: This is a general indicator of performance problems. High stall times indicate I/O problems, which can be attributed to busy physical disks or queries that return large data sets to the client.

Guideline values: The ideal value for this metric should be less than 100, but this is just a rule-of-thumb. Higher values may be acceptable for your organization.

Check also:

  • Disk avg. read time
  • Disk avg. write time
  • Avg. disk queue length

Possible solutions:
IO Stalls are affected by three possible factors:

  1. poorly performing disk subsystem (such as a misconfigured SAN)
  2. poorly defined queries
  3. overloaded disks (“data bursts”)

Solutions may involve allocating new hardware resources in addition to performance tuning of individual queries.

More information:

Wikis: Disk Contention Issues

Troubleshooting SQL Server I/O requests taking longer than 15 seconds – I/O stalls & Disk latency

Average I/O stalls, 4.3 out of 5 based on 4 ratings

Metric definition

Metric Name:
Average IO stalls
Description:
Explanation: This is a general indicator of performance problems. High stall times indicate I/O problems, which can be attributed to busy physical disks or queries that return large data sets to the client. Guideline values: The ideal value for this metric should be less than 100, but this is just a rule-of-thumb. Higher values may be acceptable for your organization. Check also:
  • Disk avg. read time
  • Disk avg. write time
  • Avg. disk queue length
Possible solutions: IO Stalls are affected by three possible factors:
  1. poorly performing disk subsystem (such as a misconfigured SAN)
  2. poorly defined queries
  3. overloaded disks (“data bursts”)

Solutions may involve allocating new hardware resources in addition to performance tuning of individual queries.

More information:

Wikis: Disk Contention Issues

Troubleshooting SQL Server I/O requests taking longer than 15 seconds - I/O stalls & Disk latency

Enter the T-SQL query that will collect data:
SELECT  CAST(SUM(io_stall_read_ms + io_stall_write_ms) /
             SUM(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10, 1)
        ) AS [avg_io_stall_ms]
FROM    sys.dm_io_virtual_file_stats(DB_ID(), NULL)
WHERE   FILE_ID <> 2;
Select instances to collect from:
Select all
Choose databases to collect from:
All databases
Set collection frequency:
Collect data every minute
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
Enabled

Alert definition

An alert is not required

VN:F [1.9.17_1161]
Rating: 4.3/5 (4 votes cast)