Who is Active?

Adam Machanic has released v9.57 of his epic procedure for SQL Server activity monitoring.

Get it while it's hot.

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!

It's too hot Dad

Our cat snowball tries to keep cool as we suffer through our third night of 30+ celcius.

View from the new office.

As I haven't posted in a while, I thought it would be appropriate that I start with a shot of the view from our new office.

Sunset from our office window

One of the last sunsets from this window. Rumour has it that we're moving to new offices soon.