Issues using sys.dm_io_virtual_file_stats dmv to check CheckDB Reads

  • onettleton

    SSC Rookie

    Points: 32

    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.

     

  • Site Owners

    SSC Guru

    Points: 80379

    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: 7224

    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.

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88174

    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.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • onettleton

    SSC Rookie

    Points: 32

    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.

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

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