Issues using sys.dm_io_virtual_file_stats dmv to check CheckDB Reads

Question

Post reply

Issues using sys.dm_io_virtual_file_stats dmv to check CheckDB Reads

onetsql

Valued Member

Points: 57
More actions
December 2, 2019 at 5:01 pm
Go to Answer
#3703526
I'm trying to get a picture of what impact our overnight CheckDB's are having on storage using sys.dm_io_virtual_file_stats dmv. We capture the data from the sys.dm_io_virtual_file_stats dmv on a regular interval, but I see hardly any increase in the num_of_reads column for the database I'm performing a CheckDB on.
See code below that I'm using to test the theory:
```
dbcc dropcleanbuffers
go

select  
mf.name,
divfs.num_of_reads
, divfs.num_of_bytes_read/1024
from sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id AND mf.file_id = divfs.file_id
where db_name(mf.database_id) = 'AdventureWorks2016'


go

dbcc checkdb (AdventureWorks2016) with all_errormsgs, no_infomsgs, tableresults --, physical_only


go

select  
mf.name,
divfs.num_of_reads
, divfs.num_of_bytes_read/1024
from sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id AND mf.file_id = divfs.file_id
where db_name(mf.database_id) = 'AdventureWorks2016'
```
In the above example I'm only seeing the "num_of_reads" counter increase by 2,. The database is 209MB.
I also tried this on a 500GB database and the num_of_reads increased by 10!
Has anybody else had issues capturing IO from CheckDB?
The reason I'm trying this is that we believe CheckDB is overwhelming the storage and I'm trying to measure how much load it's putting through.
Jeffrey Williams

SSC Guru

Points: 90301
More actions
December 3, 2019 at 6:46 pm
Answer
#3703960

rvsc48 wrote:
CheckDB when running primarily effects IO for the TempDB database and not so much on the database you are running it on. Try running your query against TempDB while CheckDB is running and you should see the number of reads go up substantially. Hopefully, you have TempDb on its own drive (fast storage) and not on the same drive or LUN as your data separated.
This won't actually help - as the IO for CHECKDB is performed on the hidden snapshot that is created specifically for the integrity check process. To be able to monitor IO performance you will be better off using perfmon and monitoring specific drives/volumes.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

rvsc48 SSCertifiable Points: 7684 More actions · Answer 2

CheckDB when running primarily effects IO for the TempDB database and not so much on the database you are running it on. Try running your query against TempDB while CheckDB is running and you should see the number of reads go up substantially. Hopefully, you have TempDb on its own drive (fast storage) and not on the same drive or LUN as your data separated.

onetsql Valued Member Points: 57 More actions · Answer 3

Thanks, I came to the same conclusion that it must appear as IO on the hidden snapshot. Shame as I normally find the virtual file stats extremely useful.

In the end I queried our SCOM repository to pull out the data I needed.

Thanks for the replies guys.