Virtual File Stats for all

Shock, Horror … yes its a blog post from Phil

Thought I’d post this script so I can find it. It captures the output from fn_VirtualFileStats to a table and runs on ALL versions of SQL Server from 2000 upwards.

For SQL 2000, it just leaves the extra columns provided by SQL 2005 & 2008 as nulls. It also join to sysaltfiles (SQL 2000) and sys.master_files (SQL 2005 & 2008) to return the logical and physical file names.

use [dba]
go

if exists (
    select * from dbo.sysobjects 
    where id = object_id(N'[dbo].[usp_sample_virtualfilestats]') 
      and objectproperty(id,N'isprocedure') = 1
  )
  drop procedure [dbo].[usp_sample_virtualfilestats]
go

set ansi_nulls on
go
set quoted_identifier on
go

create procedure [dbo].[usp_sample_virtualfilestats]

as
begin -- procedure
  set nocount on

  declare @hour_id int
  declare @prodVer varchar(20)
  declare @ver int
  declare @srvr varchar(50)
  -- set hour_id for sample
  set @hour_id = convert(varchar(10), GetDate(), 112) 
                    + convert(varchar(2), GetDate(), 108)
  -- get product version and convert major version to int
  set @prodVer = cast(serverproperty('ProductVersion') as varchar(20))
  set @ver = cast(left(@prodVer, charindex('.',@prodVer)-1) as int)
  -- get server name
  set @srvr = cast(serverproperty('ServerName') as varchar(50))
  -- make sure virtualFileStats table exists
  if object_id('[DBA].[dbo].[virtualFileStats]') is null
  begin
    create table [DBA].[dbo].[virtualFileStats] (
      [server_name] varchar(50) not null
      , [hour_id] int not null
      , [db_id] smallint not null
      , [file_id] smallint not null
      , [logical_name] nvarchar(128)
      , [physical_name] nvarchar(255)
      , [time_stamp] bigint
      , [number_of_reads] bigint
      , [number_of_writes] bigint
      , [bytes_read] decimal(19,2)
      , [bytes_written] decimal(19,2)
      , [io_stall_ms] bigint
      , [io_stall_read_ms] bigint
      , [io_stall_write_ms] bigint
      , [bytes_on_disk] decimal(19,2)
      , constraint [pk_virtualfilestats] primary key clustered (
        [hour_id]
        , [db_id]
        , [file_id]
      )
    ) 
  end

  -- check if running on SQL 2000
  if @ver = 8
  begin
    -- run command using SQL 2000 format
    insert into [DBA].[dbo].[virtualFileStats] (
      [server_name], [hour_id], [db_id], [file_id]
      , [logical_name], [physical_name], [time_stamp]
      , [number_of_reads], [number_of_writes]
      , [bytes_read], [bytes_written], [io_stall_ms]
    )
    select 
      @srvr, @hour_id, vfs.[DbID], vfs.[FileId], alt.[name]
      , alt.[filename], vfs.[TimeStamp], vfs.[NumberReads]
      , vfs.[NumberWrites], vfs.[BytesRead]
      , vfs.[BytesWritten], vfs.[IoStallMS]
    from ::fn_virtualfilestats(-1,-1) as vfs  
      inner join master..sysaltfiles alt
        on vfs.[dbid] = alt.[dbid]
        and vfs.[fileid] = alt.[fileid]
  end    
  else if (@ver = 9) or (@ver = 10)
  begin
    -- run command using SQL 2005 & 2008 format
    insert into [DBA].[dbo].[virtualFileStats] (
      [server_name], [hour_id], [db_id], [file_id]
      , [logical_name], [physical_name], [time_stamp]
      , [number_of_reads], [number_of_writes]
      , [bytes_read], [bytes_written], [io_stall_ms]
      , [io_stall_read_ms], [io_stall_write_ms]
      , [bytes_on_disk]
    )
    select
      @srvr, @hour_id, vfs.[database_id], vfs.[file_id]
      , mf.[name], mf.[physical_name], vfs.[sample_ms]
      , vfs.[num_of_reads], vfs.[num_of_writes]
      , vfs.[num_of_bytes_read], vfs.[num_of_bytes_written]
        , vfs.[io_stall], vfs.[io_stall_read_ms]
        , vfs.[io_stall_write_ms], vfs.[size_on_disk_bytes]
    from sys.dm_io_virtual_file_stats(null, null) as vfs
        inner join sys.master_files mf
        on vfs.[database_id] = mf.[database_id]
        and vfs.[file_id] = mf.[file_id]

  end
  
end -- procedure

go

Enjoy!