Help me interpret SQLIOsim results?

Question

I have SQLIOSim results from a client server and I don't really know how to interpret the results well. From what I have read the RunningAverageIODuration_ms is way too high but the other measurements I don't know. Some say "High numbers are good" but do not go into specifics about what is a high number. Below are the results with data file number first then logfile. If someone can give me some insight I would appreciate it.

TargetIoDuration_ms 100, 100
RunningAverageIODuration 63, 43
NumberOfTimesIOThrottled 10288, 109
IORequestBlocks 206, 8
Reads 14490, 0
ScatterReads 27405, 0
Writes 959, 8572
GatherWrites 25767, 0
TotalIOTime_ms 239327914, 503485
SectorSize 512, 512
Cylinders 11009, 11009
MediaType 0,0
SectorPerTrack 12, 12
TracksPerCylinder 32, 32
ReadCachEnabled T, T
WriteCacheEnabled F, F
ReadCount 41966, 41967
WriteCount 35469, 35478
WriteTime_ms 165995884, 165995963
IdleTime_ms 9430, 9430
BytesRead 8212389888, 8212393984
BytesWritten 6657832448, 6657869312
SplitIOCount 72, 72
StorageNumber 3,3
StorageManager FTDISK, FTDISK

---

Bob - www.crowcoder.com

Answer 1

SQLIOSim is a stress testing tool rather than a performance assessment tool. Many of the results are informational (ie SectorSize, MediaType, SectorPerTrack, TracksPerCyclinder, ReadCacheEnabled etc.). With SQLIOSim you're more interested in how stable the disk subsystem is rather than numeric outcomes.

If you're looking to measure the performance of the underlying disk subsystem then I'd recommend SQLIO.

http://www.microsoft.com/downloads/en/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19&displaylang=en

For a discussion on SQLIOSim output see the following link by Bob Dorr (who wrote SQLIOSim)

http://blogs.msdn.com/b/psssql/archive/2008/11/12/how-it-works-sqliosim-running-average-target-duration-discarded-buffers.aspx

SQLIOSim output

http://sqlblog.com/blogs/kevin_kline/archive/2007/06/28/understanding-sqliosim-output.aspx

You may find the following article helpful:

http://blogs.msdn.com/b/jimmymay/archive/2009/09/27/sqliosim-parser-by-jens-suessmeyer-yours-truly.aspx

Answer 2

Thank you for your reply. I have already been to all google-able sites regarding sqliosim.

---

Bob - www.crowcoder.com

Answer 3

If RunningAverageIODuration is too high then I'd investigate the performance of the disk subsystem using SQLIO.

Did the above links by Bob and Kevin answer your question?

Answer 4

The links, which I had already been to before I posted this question, only partially helped. For instance, IORequestBlocks - "High numbers are good". My results show 206 and I have no reference point to determine if that is high or low. Are you basically telling me this utility is of no use? My ultimate goal is to figure out why a process that takes about 5 minutes on our development servers runs for over an hour at my client site.

---

Bob - www.crowcoder.com

Answer 5

Ok, SQLIOSim is designed to stress test the IO subsystem and to determine how stable it is under load. i.e. Is this disk subsystem suitable to support mission-critical applications? It does not directly use SQL Server, instead it mimics some of the i/o behaviour of a typical SQL Server.

If you wish to discover at which point the disk subsystem becomes saturated (starts running slowly) then use SQLIO (see link above). To help parse the results from SQLIO see the following link (from this you can extact data from SQL and graph it in Excel).

http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO

However, if you are getting large RunningAverageIODuration it is "most likely" that collecting basic Windows perfmon counters like Avg. Disk sec/Read and Avg. Disk sec/Write on the individual SQL Server volumes (data, log etc) will point to a disk latency issue - which would be investigated in the usual way.