Monitoring Database File IO ::fn_virtualfilestats

Why are people still using SQL 2000? I don’t know but in my experience it’s still used in a lot of companies certainly when the have a ‘if it ain’t broken don’t fix it’ policy. Facts is that as a DBA I cannot use those beautiful DMV’s of SQL 2005 and up nor can I use the SQL 2008 Data Management Warehouse (DMW). So here I am on my own and I need to determine how the load is on the different database files because I have performance issues and the SAN guy choose the server for a free disk upgrade (must be an exception this SAN guy:-)). Looking around for a possible solution I found the built-in SQL function fn_vurtualfilestats. In SQL 2005 and 2008 it even gives you more information but I have to deal with the SQL 2000 version so my scripts are based on the 2000 version.

First step is to create or find a database that will be excluded from the monitoring. Next step is to create a table where I list the databases with their files I want to monitor, I put in some more information because in the end I would like to make some reports and it will make the queries simpler then. I also add an identity column that I will use in my monitoring script to loop over all the rows in this table:

USE MonitorDB
GO

CREATE TABLE Db2Monitor (
 ID int IDENTITY (1,1)
 ,DatabaseID smallint
 ,DBName varchar (50)
 ,FileID smallint
 ,intFileName varchar(50)
 ,OSFileName nvarchar(260)
 )
GO

CREATE CLUSTERED INDEX CX_DBID_FILE_ID
 ON Db2Monitor (DatabaseID,FileID)
GO

 Now I fill the table with the information of the databases I want to monitor.:

USE MonitorDB
GO

DECLARE @DB varchar(50)
SET @DB = ‘nees monitoring>’

INSERT INTO Db2Monitor
SELECT db_id(@DB),@DB, fileid, name, filename
FROMnees monitoring>..sysfiles

Next I need a table that will hold the logging information of the database files. In the table I have all the columns of fn_virtualfilestats and I add columns that will hold the delta between the current snapshot and the previous one. For reporting purposes I also add a SnapTime column that will contain the datetime off the moment the snapshot is taken:

USE dbMonitor
GO

CREATE TABLE FileStats (
 ID int IDENTITY(1,1)
 ,DatabaseId smallint
 ,FileId smallint
 ,FS_TimeStamp int
 ,NumberReads bigint
 ,DNReads bigint
 ,NumberWrites bigint
 ,DNWrites bigint
 ,BytesRead bigint
 ,DBRead bigint
 ,BytesWritten bigint
 ,DBWritten bigint
 ,IoStallMS bigint
 ,DIoStall bigint
 ,SnapTime datetime DEFAULT getdate()
 )
GO

CREATE INDEX IX_ID
 ON FileStats (ID)
GO

CREATE CLUSTERED INDEX CX_DBID_FILE_ID
 ON FileStats (DatabaseID,FileID)
GO

Having the basis in place I can start writing the script to insert and calculate the values. I start with the declaration of my variables and assigning a value to it. I start a loop over every row in my db2Monitor table and first I insert all the values from fn_virtualfilestats:

DECLARE @i int –loop counter
DECLARE @Cnt int –number of rows in db2Monitor will define end of loop
DECLARE @dbID smallint
DECLARE @FileID smallint

SET @i=1
SELECT @Cnt =  count (*) from db2Monitor

WHILE @i <= @Cnt
BEGIN
 SELECT @dbID = DatabaseID FROM Db2Monitor WHERE ID = @i
 SELECT @FileID = FileID FROM Db2Monitor WHERE ID = @i
 INSERT INTO [FileStats]
           ([DatabaseId]
           ,[FileId]
           ,[FS_TimeStamp]
           ,[NumberReads]
           ,[NumberWrites]
           ,[BytesRead]
           ,[BytesWritten]
           ,[IoStallMS]
           )
            SELECT * FROM ::fn_virtualfilestats(@dbID,@FileID)

Now I have the information of the total amount of reads/writes I can calculate the delta between the current and previous snapshot. Note that the first time the complete procedure is run the delta columns will have a NULL value. So I update in the running loop my Delta columns based on the MAX(ID) that is smaller than my current ID:

      UPDATE FileStats
  SET DNReads = NumberReads – (SELECT NumberReads
          FROM FileStats FS1
          WHERE FS1.ID = (SELECT MAX(ID)
              FROM FileStats FS2
              WHERE FileStats.DatabaseId = FS2.DatabaseId
               AND FileStats.FileId = FS2.FileId
               AND FS2.ID < Filestats.ID)),
   DNWrites = NumberWrites – (SELECT NumberWrites
          FROM FileStats FS1
          WHERE FS1.ID = (SELECT MAX(ID)
              FROM FileStats FS2
              WHERE FileStats.DatabaseId = FS2.DatabaseId
               AND FileStats.FileId = FS2.FileId
               AND FS2.ID < Filestats.ID)),
   DBRead = BytesRead – (SELECT BytesRead
          FROM FileStats FS1
          WHERE FS1.ID = (SELECT MAX(ID)
              FROM FileStats FS2
              WHERE FileStats.DatabaseId = FS2.DatabaseId
               AND FileStats.FileId = FS2.FileId
               AND FS2.ID < Filestats.ID)),
   DBWritten = BytesWritten – (SELECT BytesWritten
          FROM FileStats FS1
          WHERE FS1.ID = (SELECT MAX(ID)
              FROM FileStats FS2
              WHERE FileStats.DatabaseId = FS2.DatabaseId
               AND FileStats.FileId = FS2.FileId
               AND FS2.ID < Filestats.ID)),
   DIoStall = IoStallMS – (SELECT IoStallMS
          FROM FileStats FS1
          WHERE FS1.ID = (SELECT MAX(ID)
              FROM FileStats FS2
              WHERE FileStats.DatabaseId = FS2.DatabaseId
               AND FileStats.FileId = FS2.FileId
               AND FS2.ID < Filestats.ID))               
  WHERE DatabaseId = @dbID
   AND FileId = @FileID
   AND ID = (SELECT MAX(ID)
      FROM FileStats FS2
      WHERE FileStats.DatabaseId = FS2.DatabaseId
       AND FileStats.FileId = FS2.FileId
       )  

The last thing I need to do is add 1 to the loop counter and end my loop so the next row of the DB2Monitor table will be handled:

 SET @i=@i+1 
END

Now I have to choice the create a Stored Procedure for this and schedule a job to run this procedure every x minutes depending on the level of reporting I desire.

How I create a PowerPivot report on top of this data is described here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: