04 July 2012

Average I/O stalls

by Stuart Ainsworth

56,942 0 I/O, Performance

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 Redgate Monitor installed.

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

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:

poorly performing disk subsystem (such as a misconfigured SAN)
poorly defined queries
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

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:

poorly performing disk subsystem (such as a misconfigured SAN)
poorly defined queries
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


		
			
			
Transact-SQL
			
			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;
			
				
					
				
					1
2
3
4
5
				
						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;

Instances to collect from

Select all

Databases to collect from

Collection frequency

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

56,942 Rate

Click to rate this post!

[Total: 13 Average: 4.2]

Average I/O stalls

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics