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:

CREATE VIEW ReportData
AS
SELECT M.DBNAme
 , 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
 INNER JOIN FileStats FS
  ON M.DatabaseID = FS.DatabaseID
  AND M.FileID = FS.FileID
WHERE DNReads IS NOT NULL

 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
ORDER BY TopUsage DESC

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:

Advertisements

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.

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=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance“>
  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
  <TopLevelFolders>
    xsi:type=”SqlServerFolder”>
      <Name>MSDB</Name>
      <ServerName>.</ServerName>
    </Folder>
    xsi:type=”FileSystemFolder”>
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
  </TopLevelFolders> 
</DtsServiceConfiguration>

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: