DBCC Consistency Check impacts...

  • Dear Experts,
    I've setup a  'DBCC CHECKDB'  job on our two 700 GB databases  on two different servers. Though it takes around a good  one hour to complete , some days it runs for over 7 hours . Would be grt if anybody can share thoughts on why it  takes so much time despite it being read-only operation ? I am more concerned as the system already has some performance issues . Thank you .

  • It's got to read every single page in that 700GB database, and do a bunch of checks to make sure all the pages are correct, all the DB structures are correct. An hour on 700GB isn't bad. For the 7 hour extremes, see if there's any IO contention at the time.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • One thing you can do (assuming you have disk space) is restore the database to another location and then run DBCC on that. There are a few types of errors that won't get picked up in that way, but all the internal consistency errors will be, and those are the ones that occur most frequently. Offloading DBCC through a secondary server is a very common practice to help reduce the load on the production machine.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

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