March Madness – SQL Server System Tables – fn_virtualfilestats

OK, so technically the fn_virtualfilestats is not exactly a system table. Please don’t email me or leave comments regarding this fact. So why am I including this in my March Madness series? Because it is so very, very important for any DBA (especially a new DBA) to know and understand that this function exists.

Unlike the preceding blog posts in this series, today’s topic is not slated to be removed from a future version of SQL Server. Well, they don’t have a warning posted on the MSDN page, so I am assuming it is going to stick around.

What is this table for?

This table function will return details about the I/O statistics for your database files. Which database files? Great question! Which ever ones you want, or all of them, or none of them if you decide to not look. The choice is yours.

Why should you care?

Have you ever looked at the goodness returned by this table function? If so then you wouldn’t have to ask such a question about why you should care. Go ahead and run this:

SELECT *
FROM fn_virtualfilestats(NULL,NULL)

(UPDATE: For SQL2000 you will want to use this format instead:)

SELECT *
FROM ::fn_virtualfilestats(-1,-1)

And look at what gets returned. I’m not going to explain each column to you, if you want those details then just go here. What I will do is explain the two parameters that you must pass to fn_virtualfilestats. The first one is for the database ID and the second is for the file ID. As you have seen, using NULL returns information about all databases, or all files, or both (as we just did above).

That means you could get info on tempdb quickly by running:

SELECT *
FROM fn_virtualfilestats(2,NULL)

Or, perhaps you know that on your server all log files have a file ID of 2, and you have your log files on a dedicated disk. You could run the following to get details on the I/O statistics for those specific files:

SELECT *
FROM fn_virtualfilestats(NULL,2)

You could return information from fn_virtualfilestats on a regualr basis, say every hour, and perform some quick calculations to look for periods of increased I/O activity. You could even slice and dice your way to find out which database files are busiest at specific times of day, if you so desired. Believe me, this is good information to have, no matter who you are.

Where else is this information?

Well, the fn_virtualfilestats is not going away but you may want to look at the sys.dm_io_virtual_file_stats DMV. That DMV returns the same details as fn_virtualfilestats. Go ahead and see for yourself:

SELECT *
FROM sys.dm_io_virtual_file_stats(NULL, NULL)

But for those of you still running SQL2000 you only have the option of fn_virtualfilestats.