SQL Server Performance

dbcc checkdb - does this do integity checks on ALL indexes?

Discussion in 'SQL Server 2005 General DBA Questions' started by Trev256b, Sep 28, 2011.

  1. Trev256b Member

    I have noticed that a miantenance plan allows you to run integrity checks on indexes as an option.

    I have also noticed BOL states this about DBCC CHECKDB:
    NOINDEX
    Specifies that intensive checks of nonclustered indexes for user tables should not be performed. This decreases the overall execution time. NOINDEX does not affect system tables because integrity checks are always performed on system table indexes.
    However, whne I run DBCC CHECKDB and compare speed with running a miantnence plan with check indexes option selected, then DBCC CHECKDB takes less time.
    1) Does this mean that DBCC CHECKDB does not do integrity checks on ALL indexes? For some reason I think DBCC CHECKDB runs less.

    2) Do an integrity checks in maintenance plans run DBCC CHECKDB or something else?
  2. satya Moderator

    What is the size of database you are running?
    Also see BOL reference:
    DBCC CHECKDB and related statements typically must read each allocated page from disk into memory so that it can be checked. Running DBCC CHECKDB when there is already a lot of activity on the system therefore impairs DBCC performance for two reasons. First, less memory is available, and the SQL Server Database Engine is forced to spool some of DBCC CHECKDB's internal data to the tempdb database. Second, DBCC CHECKDB tries to optimize the way that it reads data from the disk. If an intensive workload is also using the same disk, optimization will be greatly reduced, resulting in slower execution.

Share This Page