April 28, 2009 at 10:23 am
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
June 23, 2009 at 7:07 pm
Hi Simon,
Did you get any answer for ques? I am searching for the same.
June 23, 2009 at 7:07 pm
Hi Simon,
Did you get any answer for ques? I am searching for the same.
June 24, 2009 at 3:39 am
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
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply