I wish this #sqlserver query would tell me how much disk IO checkdb performed against an internal snapshot...

Below is a query that won't work.  Not just because I'm not an expert SQL query writer 😀

Well... let me back up.  The query half-works.  The less interesting half 😀.

If checkdb/checktable is running with an internal snapshot (default behavior without TABLOCK), then the first CTE finds the database_id of the internal snapshot just peachy.

The theory behind my second CTE is sound even though there's probably a slicker way of doing it.

And sys.dm_os_volume_stats works just fine given the internal snapshot db_id and my proxy for file_id.

What doesn't work?  sys.dm_io_virtual_file_stats returns a bunch of NULL values. :-( And that's where the treasure is.

Now on the test system I'm using there's only 1 rowfile per Windows volume, so I can take out the VFS numbers for the persistent database from perfmon numbers for the volumes.  But if there were multiple rowfiles in these volumes, it'd be hard t o sort out.

;WITH int_snapdb(dbase_id) AS 
(SELECT stl.resource_database_id -- this little number will find an internal snapshot
 FROM sys.dm_tran_locks stl      -- eg for checkdb/checktable
 LEFT OUTER JOIN sys.databases sdb 
 ON sdb.database_id = stl.resource_database_id
 WHERE sdb.[name] IS NULL
 GROUP BY resource_database_id),
      nums (N) AS -- no single snapshot can have more files than are in sys.master_files :-)
(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N 
 FROM sys.master_files)
SELECT GETDATE() AS capture_time, 
       vfs.sample_ms, 
       ovs.database_id, 
       ovs.file_id, 
       ovs.volume_id, 
       ovs.total_bytes, 
       ovs.available_bytes, 
       vfs.num_of_reads, 
       vfs.num_of_bytes_read, 
       vfs.io_stall_read_ms, 
       vfs.io_stall_queued_read_ms,
       vfs.num_of_writes, 
       vfs.num_of_bytes_written, 
       vfs.io_stall_write_ms, 
       vfs.io_stall_queued_write_ms 
FROM int_snapdb
cross join nums 
cross apply sys.dm_os_volume_stats(int_snapdb.dbase_id, nums.N) ovs
outer apply sys.dm_io_virtual_file_stats(int_snapdb.dbase_id, nums.N) vfs