none
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
    Monday, May 02, 2011 11:40 PM

Answers

  • 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.



    • Marked as answer by Molku Tuesday, May 03, 2011 12:17 PM
    Tuesday, May 03, 2011 12:04 PM

All replies