SQL Server v.Next (Denali) : Breaking change to fn_virtualfilestats

Yesterday I posted a general warning about changes to Denali that will potentially break your existing code base, with a strong suggestion to grab the summer CTP as soon as it is available and start testing. I posted an example of a breaking change that will not be documented since it affects a commonly-used but undocumented DBCC command (DBCC LOGINFO), and also mentioned a couple of other changes in passing (). Today it occurred to me that it may be more useful if, when I come across a potential breaking change, I were to blog about it explicitly.

One way that many folks gauge the I/O to their data and log files is to use the system function ::fn_virtualfilestats. This function returns information such as the number of reads/writes, the number of bytes read/written, number of I/O stalls, etc. for each data and log file in the instance. The syntax is:

SELECT DbId, FileId, ... FROM ::fn_virtualfilestats(database_id, file_id);

In order to return rows for all of the databases or files on the system, you can pass NULL for either argument. So for example, to get all of the files for tempdb, you would say:

SELECT DbId, FileId, ... FROM ::fn_virtualfilestats(4, NULL);

And to get all of the initial or primary data files (file_id = 1) for all databases, you would say:

SELECT DbId, FileId, ... FROM ::fn_virtualfilestats(NULL, 1);

And to get all data for all files on the system, you pass NULL to both arguments: 

SELECT DbId, FileId, ... FROM ::fn_virtualfilestats(NULL, NULL);

However, and I'm not quite sure why, but I quite commonly see this usage interchanged for the above (using -1 instead of NULL):

SELECT DbId, FileId, ... FROM ::fn_virtualfilestats(-1, -1);

Now, while that syntax does work on SQL Server 2005, 2008 and 2008 R2, the documentation for fn_virtualfilestats does not suggest, never mind promote, the use of -1 in place of NULL:


And as it turns out, if you use -1 for either argument in Denali, you get an empty result set. So, one other thing to prepare for Denali is to make sure that your code uses this syntax instead (even against older versions of SQL Server – doesn't NULL feel more natural and less icky than a token value of -1 that was in use back in SQL Server 2000?):

SELECT DbId, FileId, ... FROM sys.fn_virtualfilestats(NULL, NULL);

Note that I use sys.fn_virtualfilestats to use Dynamic Management Object syntax instead of the old :: notation (the documentation, for some reason, doesn't use either syntax, even though this behavior change document suggests using sys. instead of ::). You are free to use either of course, but if I were to bet on longevity, I would say that the sys. syntax will be around longer … and I always use the schema prefix as a matter of principle and as a best practice.

I'm not sure if this change is a known issue, intentional, or if a bug has been filed against it. My suggestion is to do the right thing and use the documented syntax instead of undocumented syntax that just happens to work for now.


Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a father of two, a huge hockey and football fan, and my pronouns are he/him. If I've helped you out, consider thanking me with a coffee. :-)

4 Responses

  1. AaronBertrand says:

    Yes Andrew, that is probably the wiser choice (though I don't think the function is deprecated – I don't recall hearing that and I don't see anything in Denali BOL that would lead me to believe that it is true).
    There is a different level of effort involved when you're talking about keeping your code working on the new version of SQL Server (simply changing the inputs to the function) and migrating all of your code to the DMF (which has different column names). While I agree with you in principle, to move to the DMF might not be as simple as just search and replace.

  2. Andrew Kelly says:

    Well I would say that you should be using the DMF instead of the function which to the best of my knowledge is depreciated.  Use sys.dm_io_virtual_file_stats() instead.

  3. AaronBertrand says:

    Wow, okay, I didn't go back that far. Makes sense.

  4. Brooke Philpott says:

    The NULL, NULL syntax appears to only work on 2005 and higher. For SQL Server 2000 you had to do -1, -1. I think that's where the usage of that format came from.