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

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: