Average I/O stalls

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.

Install metric...

Metrics install automatically if you have SQL Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, SQL Monitor, you can instantly install and run this metric on your servers.

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 11 ratings

Metric definition

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

The T-SQL query that will collect data

Instances to collect from

Select all

Databases to collect from

0

Collection frequency

60

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

An alert is not required