DBCC CHECKDB is SQL Server built-in utility command to analyze and report the database integrity in various aspects. It checks the logical and physical integrity of all the objects in the specified database by performing the following operations:
- Runs DBCC CHECKALLOC on the database
- Runs DBCC CHECKTABLE on every table and view in the database
- Runs DBCC CHECKCATALOG on the database
- Validates the contents of every indexed view in the database
It is a useful command and should not be abandoned due to its resource hungry execution. However under default deployment it may take significant time on production databases. With increasing time window, risk of performance degradation increases. You may have faced these issues several times on your production servers. Following short comings may be noticed in default implementation.
- Error or information messages are not stored any where when scheduled job is run
- Check is required to exclude any databases other than ONLINE from the target databases list
- A strategy is required to minimize the activity on server
We may take few steps to make the process optimized and log the output. Following are points that are recommended for it.
- Save log file of DBCC CHECKDB scheduled job output to disk. It may have just error messages if any or also the informational messages.
- Make sure to exclude the databases whose CHECKDB is not required. CHECKDB is performed for ONLINE databases, so make sure to include the check in the script that will filter out all the databases that are not ONLINE.
- Use parameter PHYSICAL_ONLY. It will greatly reduce the process time and will only consider the data as it is stored on physical layer.
- We can check the logical structure on any fresh restored copy of same databases on other server. Logical checks are not dependent on machine or disk. It will totally eliminate the load of logical checks from production server. This process will also make sure the validity check of backups.
Page Verification Settings and DBCC CheckDB
It seems here relevant to discuss the relationship between the Page verification check sum and DBCC CheckDB. PAGE VERIFICATION is a database level setting related to data recovery and integrity. Its value may be NULL, TORN_PAGE_DETECTION or CHECKSUM. For SQL Server 2005 and onwards CHECKSUM is the default option for new databases. CHECKSUM is more detailed and granular algorithm than TORN_PAGE_DETECTION. CHECKSUM covers all aspects of TORN_PAGE_DETECTION. However it may require more CPU resources as compared to TORN_PAGE_DETECTION. CHECKSUM Calculates a checksum over the contents of the whole page and stores the value in the page header when a page is written to disk.
A common misconception is that if we enable CHECKSUM for Page verification feature then we may skip the DBCC CheckDB command as CHECKSUM will be evaluating the data integrity.
It is not true at all. Page verification CHECKSUM is not alternate of DBCC CheckDB however it may effectively enhance the scope of DBCC CheckDB. Page verification CHECKSUM is a limited scope page level verification whereas DBCC CheckDB covers far more areas than that.
Page verification CHECKSUM is calculated/updated only when a data page is saved back to disk. If a data page gets corrupt after it has been saved to disk with CHECKSUM calculated on it then we will not get its report until it will be retrieved again. To verify through the calculated CHECKSUM we have to run DBCC CheckDB necessarily.
Technical detail of differences is not under scope of this statement. For better verification framework we should use both features.