Archive for July 8th, 2010

CHECKDB and Physical_Only Option July 8th, 2010

Vinod Kumar

A number of you have asked me during my sessions on how long will CHECKDB run for my 1 TB DB. Though there are no specific or scientific answer to this just like what Paul mentions over his blog. There can be tons of reasons for the same. The CHECKDB has been enhanced from every version and it gets better and better from versions from performance perspective but tons of other structures or additional checks get also added. During one of the presentation recently I did answer around using PHYSICAL_ONLY option for running on large databases.

If Indexed views and XML indexes are present in a SQL Server 2005+ database, the time taken to complete DBCC CHECKDB, DBCC CHECKTABLE will be longer. This is because of the extra checks that SQL Server 2005+ versions performs by default on these new data structures. If this is turning out to be a problem in such a way that the DBCC CHECK’s are not completing within the maintenance window, you can use the PHYSICAL_ONLY option.

Using the PHYSICAL_ONLY option skips the indexed view and xml index checks. Also data purity checks are skipped when PHYSICAL_ONLY option is specified for the DBCC CHECK command. Please refer to the Books Online topic discussion on DBCC CHECKDB statement for a full list of checks done when the PHYSICAL_ONLY option is specified.

Though this is handy in some way, you might still want to run CHECKDB on the full database once in a while – may be once a month to see your IO subsystem is not acting cranky …

Continue reading...