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:

http://msdn.microsoft.com/en-us/library/ms187309.aspx

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.