A couple of days ago, I ran into this interesting error message. As I was preparing some diagnostic queries, to add to our Query Library, I was playing with some DMV’s & DMF’s (Dynamic Management Views & Functions) to gather some under-the-hood details about our Production Sql Server instance. When I JOIN’ed system views with some DMF’s, I get this error  message saying that this is not allowed. It’s interesting because, I remember being able to successfully do this in the past. Not this exact query, but something like this, and it worked. Not this time.

[sourcecode language=”sql”]
SELECT @@SERVERNAME AS [Server_Name]
, Db_name(Db_id()) AS [Database Name]
, name AS [File Name]
, F.[file_id] AS [File_ID]
, physical_name AS [Physical Name]
, size / 128.0 AS [Total Size in MB]
, size / 128.0 – Cast(Fileproperty(name, ‘SpaceUsed’) AS INT) / 128.0 AS [Available Space In MB]
, num_of_reads AS [num_of_reads]
, num_of_writes AS [num_of_writes]
, io_stall_read_ms AS [io_stall_read_ms]
, io_stall_write_ms AS [io_stall_write_ms]
, Cast(100. * io_stall_read_ms / ( io_stall_read_ms + io_stall_write_ms ) AS DECIMAL(10, 1)) AS [IO Stall Reads Pct]
, Cast(100. * io_stall_write_ms / ( io_stall_write_ms + io_stall_read_ms ) AS DECIMAL(10, 1)) AS [IO Stall Writes Pct]
, ( num_of_reads + num_of_writes ) AS [Writes + Reads]
, num_of_bytes_read AS [num_of_bytes_read]
, num_of_bytes_written AS [num_of_bytes_written]
, Cast(100. * num_of_reads / ( num_of_reads + num_of_writes ) AS DECIMAL(10, 1)) AS [# Reads Pct]
, Cast(100. * num_of_writes / ( num_of_reads + num_of_writes ) AS DECIMAL(10, 1)) AS [# Write Pct]
, Cast(100. * num_of_bytes_read / (num_of_bytes_read + num_of_bytes_written ) AS DECIMAL(10, 1)) AS [Read Bytes Pct]
, Cast(100. * num_of_bytes_written / (num_of_bytes_read + num_of_bytes_written) AS DECIMAL(10, 1)) AS [Written Bytes Pct]
FROM sys.database_files AS F
CROSS APPLY sys.Dm_io_virtual_file_stats(Db_id(), F.file_id) AS VF
OPTION (recompile);
GO
[/sourcecode]

Error Message:

Msg 413, Level 16, State 1, Line 1
Correlated parameters or sub-queries are not supported by the inline function “sys.Dm_io_virtual_file_stats”.

 So, I play with a little bit and try to see if it is syntactic or semantic issue. But no progress. So, I looked up the error message, but did not get anything directly useful. Looks like this is a rare error. And what I gather from MSDN Connect, there is no immediate fix for it.

But there are workarounds. Connect shows a work around from Simon Sabin. But below is my easier (I think) work around. I use CTE to wrap-around the DMF and JOIN it with any table or system view. And it works !!

[sourcecode language=”sql”]
WITH CTE_VFS (Database_ID, [File_ID], sample_ms, num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes, num_of_bytes_written, io_stall_write_ms, io_stall, size_on_disk_bytes, file_handle)
AS
(
SELECT Database_ID, [File_ID], sample_ms, num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes, num_of_bytes_written, io_stall_write_ms, io_stall, size_on_disk_bytes, file_handle
FROM sys.Dm_io_virtual_file_stats(Db_id(), NULL) AS VF
)
SELECT @@SERVERNAME AS [Server_Name]
, Db_name(Db_id()) AS [Database Name]
, F.name AS [File Name]
, F.[file_id] AS [File_ID]
, F.physical_name AS [Physical Name]
, F.size / 128.0 AS [Total Size in MB]
, F.size / 128.0 – Cast(Fileproperty(name, ‘SpaceUsed’) AS INT) / 128.0 AS [Available Space In MB]
, VF.num_of_reads AS [num_of_reads]
, VF.num_of_writes AS [num_of_writes]
, VF.io_stall_read_ms AS [io_stall_read_ms]
, VF.io_stall_write_ms AS [io_stall_write_ms]
, Cast(100. * VF.io_stall_read_ms / ( VF.io_stall_read_ms + VF.io_stall_write_ms ) AS DECIMAL(10, 1)) AS [IO Stall Reads Pct]
, Cast(100. * VF.io_stall_write_ms / ( VF.io_stall_write_ms + VF.io_stall_read_ms ) AS DECIMAL(10, 1)) AS [IO Stall Writes Pct]
, ( VF.num_of_reads + VF.num_of_writes ) AS [Writes + Reads]
, VF.num_of_bytes_read AS [num_of_bytes_read]
, VF.num_of_bytes_written AS [num_of_bytes_written]
, Cast(100. * VF.num_of_reads / ( VF.num_of_reads + VF.num_of_writes ) AS DECIMAL(10, 1)) AS [# Reads Pct]
, Cast(100. * VF.num_of_writes / ( VF.num_of_reads + VF.num_of_writes ) AS DECIMAL(10, 1)) AS [# Write Pct]
, Cast(100. * VF.num_of_bytes_read / (VF.num_of_bytes_read + VF.num_of_bytes_written ) AS DECIMAL(10, 1)) AS [Read Bytes Pct]
, Cast(100. * VF.num_of_bytes_written / (VF.num_of_bytes_read + VF.num_of_bytes_written) AS DECIMAL(10, 1)) AS [Written Bytes Pct]
FROM CTE_VFS AS VF
INNER JOIN sys.database_files AS F
ON VF.File_ID = F.file_id
GO
[/sourcecode]

Hope this helps,
_Sqltimes