IO stalls

  • Hi All

    in the ms white paper on Waits & Queues (that was kindly recommended to me by grant) there is a query to determine avg i.o stalls

    If Im running this over a single disc box is there any "ballpark figures" for average amount of stalls per read and write I should be aware of ?

    many thanks

    simon

    --avg stalls per read/write

    select

    [name],

    database_id,

    filetype = case sys.database_files.type

    when 1 then 'Log'

    when 0 then 'Data'

    end,

    io_stall_read_ms,

    num_of_reads,

    cast (io_stall_read_ms/(1.0+num_of_reads) as numeric (10,1)) as 'avg_read_stall_ms',

    io_stall_write_ms,

    num_of_writes,

    cast (io_stall_write_ms/(1.0+num_of_writes) as numeric (10,1)) as 'avg_write_stall_ms',

    io_stall_write_ms+num_of_writes as io_stalls ,

    num_of_reads+num_of_writes as total_io,

    cast(io_stall_read_ms+io_stall_write_ms/(1.0+num_of_reads+num_of_writes) as numeric (10,1)) as 'avg_io_stall_ms'

    from sys.dm_io_virtual_file_stats (11,FILE_IDEX(11))

    join sys.database_files

    on sys.dm_io_virtual_file_stats .file_id = sys.database_files.file_id

    order by avg_io_stall_ms desc

    go

  • Hi Simon,

    Did you get any answer for ques? I am searching for the same.

  • Hi Simon,

    Did you get any answer for ques? I am searching for the same.

  • From the same white paper:

    http://technet.microsoft.com/en-us/library/cc966413.aspx


    If IO_stalls is inordinately high for one or more files, it is possible that there is either a disk bottleneck or that high reads and writes are occurring on one drive.

    Average IO Waits per read or write can distinguish between consistently high IO queues or a temporary IO spike. A significantly higher average value for IO stalls on one particular drive indicates consistently high IO requests.

    This should be corroborated with Performance Monitor counters Physical Disk: Average Disk Seconds/Read and Average Disk Seconds/Write. The following script can also compute the Average Disk Seconds/Read and Average Disk Seconds/Write using sys.dm_io_virtual_file_stats.

    So, essentially what you are looking for are any values that consistently stand out as unusually high - especially compared to other drives with a similar workload. I know that sounds a bit vague - but necessarily so: your workload and the characteristics of your I/O system make it difficult to give exact values which would apply to you.

    I like to also monitor the sys.dm_io_pending_io_requests, which can help in forming an overall picture, together with the other measures referenced above.

    Paul

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply