Sql-server – Investigating/Troubleshooting I/O Spikes

partitioningsansql server

We have been experiencing some relatively large I/O (IOPS) spikes on the SAN that supports our production SQL Server.

The spikes seem to fall at an exact time after the hour, each hour. We have investigated every known scheduled task source (SQL Agent, backups, scheduled SSRS Reports, etc.) and can't find any rhyme or reason to it so far.

We have use Activity Monitor as well, but it has not yielded any answers to date.

How would one go about definitively finding the source of I/O spikes? Are there monitoring tools (commercial or otherwise) that would help pinpoint the problem?

Best Answer

I have experianced a similar issue a few of times in the last year, and each time it has been due to an external issue, normally anti-virus software with incorrectly configured exclusions. I would check this.

In the mean-time, the following query will let you examine the level of physical IOs comming from SQL Server:

SELECT a.io_stall, a.io_stall_read_ms, a.io_stall_write_ms, a.num_of_reads, 
a.num_of_writes, 
--a.sample_ms, a.num_of_bytes_read, a.num_of_bytes_written, a.io_stall_write_ms, 
( ( a.size_on_disk_bytes / 1024 ) / 1024.0 ) AS size_on_disk_mb, 
db_name(a.database_id) AS dbname, 
b.name, a.file_id, 
db_file_type = CASE 
                   WHEN a.file_id = 2 THEN 'Log' 
                   ELSE 'Data' 
                   END, 
UPPER(SUBSTRING(b.physical_name, 1, 2)) AS disk_location 
FROM sys.dm_io_virtual_file_stats (NULL, NULL) a 
JOIN sys.master_files b ON a.file_id = b.file_id 
AND a.database_id = b.database_id 
ORDER BY a.io_stall DESC

Pay particular attention to IO_Stalls, Reads and Writes. This query cam from http://blogs.msdn.com/b/dpless/archive/2010/12/01/leveraging-sys-dm-io-virtual-file-stats.aspx

Related Question