Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    133

    Unanswered: check index health

    Hi guys,
    is there a DBCC command to check index health status of a particular table?
    no table locking involve upon executing that dbcc command. thanks

  2. #2
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97
    There are so many flavors of consistency checkers I cannot remember all. IIRC, true checkers, those that analyze all index pages, have all some degree of intrusion. They are supposed to be run during a maintenance window; for instance, on Sundays. This applies to dbcc checkalloc, dbcc checktable, dbcc indexalloc, dbcc reindex and even dbcc checkstorage. dbcc checkstorage is the recommended tool for global consistency checking and I think it does not lock too much, but it may report spurious results (false inconsistencies) if ran while applications are running (unless they are read only)

    sp_indsuspect is really fast and harmless... because it just reports the indexes marked suspect during startup and recovery. It is worth running sp_indsuspect in every database at least once, because some indexes may be corrupt from the installation or upgrade. But it is not a replacement for the above.

    Regards,
    Mariano Corral Herranz

  3. #3
    Join Date
    Feb 2012
    Posts
    133
    Thanks for the replied.

    I'm quite confused with Dbcc Reindex function. Is dbcc reindex similar to drop/create index action?or just a consistency index checker.

    Do dbcc reindex involve table locking or just an index level locking page? thanks

  4. #4
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97
    dbcc reindex is a consistency checker that is followed by drop and create of the suspect index if found corrupt. Hence, not to be run while being used by the applications.

    I cannot say how safe dbcc reindex is. Personally, I never include in my regular maintenance scripts any tool which fixes corruptions automatically. I prefer only the reporting version and I'll decide later whether the fixing version ought to be run against the objects reported.

    That said, fixing indexes without modifying the data is always safer than fixing the table itself.

    Regards,
    Mariano Corral

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •