<\/P>
The sys.dm_io_virtual_file_stats dynamic management function returns I\/O statistics for data and log files [MDF and LDF file], with two parameters, one for database_id and another for the file_id. This function will help you to identify I\/O file level.
I was led here when doing a search because we were getting enormous amounts of disk queuing the other day during a round of testing, so I started hunting around, looking for some information on some query to get this information from SQL Server, and I found this article from Itzik Ben-Gan entitled Query DMFs to Analyze Performance Stats<\/A>. His stuff is always good, if some of it blows right past my non-mathematical brain quite often (as does a bit of that article) and it pointed me to some queries that are useful, but I also wanted to be able to take a “point in time” reading that I could compare with. This information, coupled with stuff from BOL here<\/A> got me going. It returns its readings since when the SQL Servers was started. I was wanting to use it to determine hotspots in the filesystem during some very heavy processing times, so I built a little snippet of code to create a table (based on the value of a variable) so you compare some segment of time. declare @resetbaseLine bit set nocount on –output the values, subtracting the baseline from the “currentLine” values \ud83d\ude42 The query returns the following, offset from when the baseline was taken. <\/P> The only real downside in this query is that it will show you almost too much information about your hotspots and you may have to go fix things!<\/P>
set @resetbaseLine = 0 <\/FONT>
if @resetBaseLine = 1 or object_id(‘tempdb..#baseline’) is null
begin
if object_id(‘tempdb..#baseline’) is not null
drop table #baseline
–initialize the baseline table
select db_name(mf.database_id) as databaseName, mf.physical_name,
num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes,
num_of_bytes_written, io_stall_write_ms, io_stall,size_on_disk_bytes,
getdate() as baselineDate
into #baseline
from sys.dm_io_virtual_file_stats(null,null) as divfs
join sys.master_files as mf
on mf.database_id = divfs.database_id
and mf.file_id = divfs.file_id
end <\/FONT>
select currentLine.databaseName, left(currentLine.physical_name,1) as drive
,currentLine.physical_name as file_name
<\/FONT> ,currentLine.io_stall – #baseline.io_stall as io_stall
,currentLine.io_stall_read_ms – #baseline.io_stall_read_ms as io_stall_read_ms
,currentLine.io_stall_write_ms – #baseline.io_stall_write_ms as io_stall_write_ms
<\/FONT> ,currentLine.num_of_reads – #baseline.num_of_reads as num_of_reads
,currentLine.num_of_bytes_read – #baseline.num_of_bytes_read as num_of_bytes_read
<\/FONT> ,currentLine.num_of_writes – #baseline.num_of_writes as num_of_writes
,currentLine.num_of_bytes_written – #baseline.num_of_bytes_written as num_of_bytes_written
<\/FONT> ,currentLine.size_on_disk_bytes – #baseline.size_on_disk_bytes as size_change_on_disk_in_bytes
, dateDiff(second,#baseline.baselineDate,currentLine.baselineDate) as seconds_since_baseline
from (
select db_name(mf.database_id) as databaseName, mf.physical_name,
num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes,
num_of_bytes_written, io_stall_write_ms, io_stall,size_on_disk_bytes,
getdate() as baselineDate
from sys.dm_io_virtual_file_stats(null,null) as divfs
join sys.master_files as mf
on mf.database_id = divfs.database_id
and mf.file_id = divfs.file_id) as currentLine
join #baseline
on #baseLine.databaseName = currentLine.databaseName
and #baseLine.physical_name = currentLine.physical_name
order by currentLine.io_stall – #baseline.io_stall desc<\/FONT><\/P>