Capture Virtual File statistics to a table to track reads, writes, and possible file contention in SQL Server

Author: Jon Baker

USE DBA
go

IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'P' AND NAME = 'usp_get_virtualfilestats')
DROP PROCEDURE usp_get_virtualfilestats
go

CREATE PROCEDURE usp_get_virtualfilestats
/*
Author: Jon Baker
Date : 10/5/2005
Paramaters : None

Test Data :
usp_get_virtualfilestats
select * from dba.dbo.dba_virtualfilestats
truncate table dba_virtualfilestats

Purpose :         To collect data for virtual file statistics by file in each database

Revisions :

*/
AS

SET NOCOUNT ON

DECLARE @rc INT
DECLARE @errmsg VARCHAR (256)
DECLARE @fileid INT
DECLARE @filename SYSNAME
DECLARE @dbid INT
DECLARE @dbname SYSNAME
DECLARE @strSQL VARCHAR(3000)

IF NOT EXISTS (SELECT 1 FROM dba.dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[dba_virtualfilestats]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
        CREATE TABLE [dbo].[dba_virtualfilestats] (
                [Dbname] [sysname] NOT NULL,
                [DbId] [smallint] NOT NULL ,
                [FileId] [smallint] NOT NULL ,
                [FileName] [sysname] NOT NULL,
                [TimeStamp] [int] NOT NULL ,
                [NumberReads] [bigint] NOT NULL ,
                [NumberWrites] [bigint] NOT NULL ,
                [BytesRead] [bigint] NOT NULL ,
                [BytesWritten] [bigint] NOT NULL ,
                [IoStallMS] [bigint] NOT NULL,
                [collect_time] datetime not null
        ) ON [PRIMARY]
        IF @@ERROR <> 0
                BEGIN
                        SET @errmsg = 'create table failed!'
                        GOTO ERRORPROC
                END
END

DECLARE @tbldbname TABLE
(
dbid INT NOT NULL PRIMARY KEY,
dbname SYSNAME NOT NULL
)

DECLARE @virtualfilestats TABLE
(
        [DbId] [smallint] NOT NULL ,
        [FileId] [smallint] NOT NULL ,
        [TimeStamp] [int] NOT NULL ,
        [NumberReads] [bigint] NOT NULL ,
        [NumberWrites] [bigint] NOT NULL ,
        [BytesRead] [bigint] NOT NULL ,
        [BytesWritten] [bigint] NOT NULL ,
        [IoStallMS] [bigint] NOT NULL
)

CREATE TABLE #files
(
fileid INT NOT NULL PRIMARY KEY,
[filename] SYSNAME NOT NULL
)

--Get databases and dbids
INSERT @tbldbname SELECT dbid, name FROM master.dbo.sysdatabases where name not in ('pubs', 'northwind', 'lumigent', 'questsoftware', 'dba', 'model', 'test')
IF @@ERROR <> 0
                BEGIN
                        SET @errmsg = 'Could not populate list of database!'
                        GOTO ERRORPROC
                END

SELECT TOP 1 @dbid = dbid, @dbname = dbname FROM @tbldbname
IF @@ERROR <> 0
                BEGIN
                        SET @errmsg = 'Could not get first database name!'
                        GOTO ERRORPROC
                END

--Loop Through the databases
WHILE @dbid is not null
BEGIN

        --Get the files
        SET @strSQL = 'use [' + @dbname + ']; select fileid, name from sysfiles'
        INSERT #files EXEC (@strSQL)
        IF @@ERROR <> 0 or @rc <> 0
                BEGIN
                        SET @errmsg = 'Could not get list of files for database ' + @dbname + ' !'
                        GOTO ERRORPROC
                END
        SELECT TOP 1 @fileid = fileid, @filename = [filename] FROM #files
                IF @@ERROR <> 0
                BEGIN
                        SET @errmsg = 'Could not get first fileid from database ' + @dbname + ' !'
                        GOTO ERRORPROC
                END
        WHILE @fileid IS NOT NULL
        BEGIN

                --loop through the files
                INSERT @virtualfilestats SELECT * FROM ::fn_virtualfilestats(@dbid, @fileid)
                IF @@ERROR <> 0
                BEGIN
                        SET @errmsg = 'Could not get file stats from fn_virtualfilestats for ' + convert (varchar(10), @dbid) + ',' + convert(varchar(10), @fileid) + ' !'
                        GOTO ERRORPROC
                END
                DELETE FROM #files WHERE fileid = @fileid
                IF @@ERROR <> 0
                BEGIN
                        SET @errmsg = 'Could not delete from #files!'
                        GOTO ERRORPROC
                END
                INSERT dba.dbo.dba_virtualfilestats
                        ([Dbname],
                        [DbId],
                        [FileId],
                        [FileName],
                        [TimeStamp],
                        [NumberReads],
                        [NumberWrites],
                        [BytesRead],
                        [BytesWritten],
                        [IoStallMS],
                        [collect_time]
                        )
                SELECT
                        @dbname,
                        [DbId],
                        [FileId],
                        @FileName,
                        [TimeStamp],
                        [NumberReads],
                        [NumberWrites],
                        [BytesRead],
                        [BytesWritten],
                        [IoStallMS],
                        getdate()
                FROM @virtualfilestats
                IF @@ERROR <> 0
                BEGIN
                        SET @errmsg = 'Could not add record to @virtualfilestats!'
                        GOTO ERRORPROC
                END
                DELETE FROM @virtualfilest