Log Shipping – NORECOVERY vs. STANDBY mode

Excellent dmf that shows, for each file that SQL Server uses for the databases, stats on how frequently the file has been used by the database. This is one of the primary dynamic management views I use almost daily (well, not usually when I am on vacation, but even sometimes then!)  The file can be any file used in the database, including the log or full text files.

Parameters:

• database_id – key of the database, retrieved from sys.databases
  
• file_id - key of a file in a database. Can be retrieved from sys.database_files if you are working in the context of a database, or sys.master_files will give you all files in all databases
  

Columns:

• database_id, file_id – same as the parameter descriptions
  
• sample_ms – the number of milliseconds that have passed since the values for sys.dm_io_virtual_file_stats were reset the only way to reset the values is to restart the server.
  
• num_of_reads – number of individual read operations that were issued to the file. Note that this is physical reads, not logical reads. Logical reads would not be registered.
  
• num_of_bytes_read – the number of bytes that were read, as opposed to the number of reads. The size of a read is not a constant value that can be calculated by the number of reads.
  
• Io_stall_read_ms – total time user processes waited for IO. Note that this number can be much greater than the sample_ms. If 10 processes are trying to use the file simultaneously, but the disk is only able to serve 1, and then you might get 9 seconds waiting over a 10 second time period.
  
• num_of_writes , num_of_bytes_written, io_stall_write_ms - the same as the read values, except for writes.
  
• io_stall – sum of io_stall_write_ms and io_stall_read_ms
  
• size_on_disk_bytes – the size of the file in bytes
  
• file_handle – the Windows file handle of the file (Books Online)
  

Example:

For all databases, get vital stats on how busy the file has been, since the last restart:

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

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