SQL SERVER – Get File Statistics Using fn_virtualfilestats

Quite often when I am staring at my SSMS I wonder what is going on under the hood in my SQL Server. I often want to know which database is very busy and which database is bit slow because of IO issue. Sometime, I think at the file level as well. I want to know which MDF or NDF is busiest and doing most of the work. Following query gets the same results very quickly.

SELECT DB_NAME(vfs.DbId) DatabaseName, mf.name,
mf.physical_name, vfs.BytesRead, vfs.BytesWritten,
vfs.IoStallMS, vfs.IoStallReadMS, vfs.IoStallWriteMS,
vfs.NumberReads, vfs.NumberWrites,
(
Size*8)/1024 Size_MB
FROM ::fn_virtualfilestats(NULL,NULL) vfs
INNER JOIN sys.master_files mf ON mf.database_id = vfs.DbId
AND mf.FILE_ID = vfs.FileId
GO

When you run above query you will get many valuable information like what is the size of the file as well how many times the reads and writes are done for each file. It also displays the read/write data in bytes. Due to IO if there has been any stall (delay) in read or write, you can know that as well.

I keep this handy but have not shared on blog earlier.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

11 thoughts on “SQL SERVER – Get File Statistics Using fn_virtualfilestats

  1. Hi Dave,

    This scripts is really helpful. I often being asked by user to provide some detail about this when they having performance issue.

    Is that possible to know which tables are busy as well?

    Regards,
    Jimmy Liew

    Like

  2. Great script, I have been watching an IO problem while we spec out new hardware and this is more elegant than my solution. Very helpful.

    Like

  3. Hi Dave, thanks for the nice article. It would be great if you can provide some threshold levels to identify issues while reviewing the data.

    Thanks!

    Like

  4. Some of my larger tables, like TEMPDB, were throwing error “Arithmetic overflow error converting expression to data type int.”, so I had to make the following change to the script, casting Size as bigint in the expression.
    Here is the complete script with the fix:

    SELECT DB_NAME(vfs.DbId) DatabaseName, mf.name,
    mf.physical_name, vfs.BytesRead, vfs.BytesWritten,
    vfs.IoStallMS, vfs.IoStallReadMS, vfs.IoStallWriteMS,
    vfs.NumberReads, vfs.NumberWrites,
    (cast(Size as bigint)*8)/1024 Size_MB
    FROM ::fn_virtualfilestats(NULL,NULL) vfs
    INNER JOIN sys.master_files mf ON mf.database_id = vfs.DbId
    AND mf.FILE_ID = vfs.FileId

    Like

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #011 « SQL Server Journey with SQL Authority

  6. The proble with this dynamic management function is, it gives cumulative value. We should see the difference vakue and not cumulative value. Since these numbers are big, it’s really difficult to find the difference.

    Like

  7. Hi Pinal ,

    Good Article , plese can you help me how to find out the IO problem using the resultys . i found the stalls columns resulted some values .

    i am not getting how to confirm that , there is problem in IO .. but i am getting Latch_IO..wait types some times . but there is no much blockings.

    please help me it .. i am facing this problem since 1 month .. not able to figure out how to fix this

    Like

  8. Hi Pinal

    I have real problem with understanding IoStallWriteMS column we have in fn_virtualfilestats view and also @@IO_BUSY.

    Unfortunately both of them seems wrong… I know it’s a little bold statement, but please take a look at my test and try to point out errors.

    So my tests are divided on 2 parts, first part create empty database , create table and create procedure which inserts 200000 rows to table. Second part – do snapshot of dm_io_virtual_file_stats and @@IO_BUSY before and after running procedure.

    So main problem is :

    without transaction timing on dm_io_virtual_file_stats seems correct, but with transaction it is totally strange – io_stall_write_ms is 2x bigger then elapsed time of whole test..- it could be possible with parallel processing but @@CPU_BUSY is just to low (Write time is much higher then CPU Usage – it’s impossible in my universe :-) ) on the other hand @@IO_BUSY is surprisingly low for example. Whole test take 30 seconds and difference in snapshot on @@IO_BUSY is just 1 second..

    The whole details about the test, scripts used and screenshots I put in different forum. Below you can find a link: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/76824b5e-6add-4685-8063-4377d6e3e6a7/io-timing-problem?forum=sqlgetstarted

    Can you help me to find out how it really works?

    Like

  9. Pingback: SQL SERVER – WRITELOG – Wait Type – Day 17 of 28 | Journey to SQL Authority with Pinal Dave

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