2

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?

Improve this question
4
  • How are you measuring quantity of I/O? It is a matter of quantity and not SAN latency?
    – Eric Higgins
    Sep 25, 2012 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, 2012 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, 2012 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, 2012 at 18:16

2 Answers 2

Reset to default
2

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 Link

Improve this answer
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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