Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
How are you measuring quantity of I/O? It is a matter of quantity and not SAN latency? –  Eric Higgins Sep 25 '12 at 17:45
    
The SAN admin is showing us IOPS data, which I think would be quantity? We are seeing performance degradation, which is why we started looking into it, which I would imagine also indicates latency issues. –  Phil Sandler Sep 25 '12 at 17:49
2  
Spikes sound like something scheduled. Check with the SAN folks about SAN snapshots or other forms of replication at the SAN level. I bet those are scheduled for that minute hand. –  Aaron Bertrand Sep 25 '12 at 17:52
    
Is your SAN dedicated to your SQL server instance? Has the SAN admin told you the IO spike is from your database server? –  user9164 Sep 25 '12 at 18:16
add comment

2 Answers 2

OK probably going to be a slightly controversial suggestion here, but have you considered running a server-side trace to monitor for high reads or writes? You only need to schedule it once and for a few minutes, around the known time of your problem, and you may well pick up your problem query. It's fair to say this should have also have been picked up in Activity Monitor, so as indicated, it may be your IO issue is not SQL Server-related. At least if it didn't turn up anything, you could present this as evidence to your SAN admin.

Also, what version of SQL Server are you using? If it's 2008 or 2012 you could do this with Extended Events which would be even more lightweight than a server-side trace.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.