Reporting on fn_virtualfilestats

In my previous post about monitoring database file IO I described how I monitor the IO for every file in every desired database on a certain server. After I ran the procedure at regular intervals during a week I now have a lot of data ready to analyze. So let’s get started. To make it easy to get the data I need in Excel I first create a view with the tables I need. Remark that I use a LEFT to get the drive letter from the OSFilename because I want to see the concurrent load on my drives and that I use the DATEDIFF function to round-up the time of the snapshot to the minute and I filter out the NULL values because they only exists in the resultset of the first execution of the script:

 , M.intFileName
 , LEFT(M.OSFileName,1) AS Drive
 , FS.DNReads
 , FS.DNWrites
 , FS.DBRead
 , FS.DBWritten
 , FS.DIoSTall
 , DATEADD(mi, DATEDIFF(mi, 0, CAST(FS.SnapTime AS DATETIME(1))),0 ) AS SnapTime
FROM DB2Monitor M
  ON M.DatabaseID = FS.DatabaseID
  AND M.FileID = FS.FileID

 With the view in place I can start using my Excel to insert a PivotChart, I create a new external connection to my ReportData view in my MonitorDB database:

Now I have my connection I can start designing my Chart. For the layout I choose  Stacked Column. Next I design the fields of the PivotTable. The Axis will made of the SnapTime field, the Legend will be grouped by Drive and IntFileName and for this report I choose the delta fields DNReads and DNWrites as Values. In the end it looks like this:


 The chart is now completely unreadable because I was taking snapshots every 5 minutes for a week so I use the Filter Pane to select some interesting, heavy load moments. To find these I run the following query on my RepordData view:

SELECT TOP 20 Snaptime, SUM(DNReads) + SUM(DNWrites) AS TopUsage
FROM ReportData
GROUP BY Snaptime

These Snaptime values are selected in the Filter Pane that looks like this:

And finally I have a nice chart to show to my colleagues and use for further analysis of the usage of the disks:


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

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

 ON Db2Monitor (DatabaseID,FileID)

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

USE MonitorDB

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

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

 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()

 ON FileStats (ID)

 ON FileStats (DatabaseID,FileID)

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
 SELECT @dbID = DatabaseID FROM Db2Monitor WHERE ID = @i
 SELECT @FileID = FileID FROM Db2Monitor WHERE ID = @i
 INSERT INTO [FileStats]
            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
      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 

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.

Configuring SSIS when no default instance is installed

Hey, got an error message when I was trying to import a SSIS package in msdb saying I needed to configure SSIS. I never saw this before and apparently I’ve had always the luck of working on SQL Servers where a default instance (MSSQLServer) was installed. But where is this information stored? When I open the file ‘MsDtsSrvr.ini.xml’ in ‘%Program Files%\Microsoft SQL Server\90/100\DTS\Binn’ with Notepad I see the following information:

<?xml version=”1.0″ encoding=”utf-8″?>
<DtsServiceConfiguration xmlns:xsd=”” xmlns:xsi=”“>
      <Name>File System</Name>

When looking at the <ServerName> value everything comes clear. Just like in other connections the ‘.’ points to the local, default server. So from here it’s quite easy. I add my servername\instancename between the ‘<ServerName>’ tags. And off course I cannot save my package yet. The SSIS service needs to be restarted. And now I can move on.

%d bloggers like this: