Database Reference
In-Depth Information
from
sys.dm_io_virtual_file_stats(null,null) fs join
sys.master_files mf with (nolock) on
fs.database_id = mf.database_id and
fs.file_id = mf.file_id
join sys.databases d with (nolock) on
d.database_id = fs.database_id
where
fs.num_of_reads + fs.num_of_writes > 0
option (recompile)
Figure 27-7. Sys_dm_io_virtual_file_stats output
Unfortunately, sys.dm_io_virtual_file_stats provides cumulative statistics as of the time of a SQL Server restart
without any way to clear it. If you need to get a snapshot of the current load in the system, you should run this function
several times and compare how the results changed between calls. Listing 27-4 shows the code that allows you to do that.
Listing 27-4. Using sys.dm_io_virtual_file_stats to obtain statistics about the current I/O load
create table #Snapshot
(
database_id smallint not null,
file_id smallint not null,
num_of_reads bigint not null,
num_of_bytes_read bigint not null,
io_stall_read_ms bigint not null,
num_of_writes bigint not null,
num_of_bytes_written bigint not null,
io_stall_write_ms bigint not null
);
insert into #Snapshot(database_id,file_id,num_of_reads,num_of_bytes_read
,io_stall_read_ms,num_of_writes,num_of_bytes_written
,io_stall_write_ms)
select database_id,file_id,num_of_reads,num_of_bytes_read
,io_stall_read_ms,num_of_writes,num_of_bytes_written
,io_stall_write_ms
from sys.dm_io_virtual_file_stats(NULL,NULL)
option (recompile);
Search WWH ::




Custom Search