Impact of executing DBCC CHECKDB on databases... when and how shall I execute it on Production Database?

There is lot of concern among many people on this issue. The concern of executing DBCC CHECKDB on a database is typically from the fear of severe performance degradation of database/application.

Possibility of performance degradation can be classified in below two sub-categories:

1. DBCC CHECKDB creates blocking for other processes: This is simply not true. DBCC CHECKDB does not block anything and solely works on database snapshot. There is no need to be worried about blocking or locking related issues from execution of DBCC CHECKDB if you are running SQL 2005 or later versions (Even in SQL 2000, it will only take occasional SCH_S locks (schema-stability locks) that would only block table scans and table schema modifications).

Unless you explicitly use WITH TABLOCK option (which is not recommended also), there will be no problem if you execute DBCC CHECKDB on a database. Please refer to http://www.sqlskills.com/BLOGS/PAUL/category/Database-Snapshots.aspx if you are interested to learn more details about this.

2. DBCC CHECKDB takes too much resource: DBCC CHECKDB performs a through analysis (literally a page by page analysis of your database) and hence it needs resource to complete this work.

Microsoft recommends that you use the PHYSICAL_ONLY option for frequent use of DBCC CHECKDB on production systems. Using PHYSICAL_ONLY can greatly shorten run-time for DBCC CHECKDB on databases and limits the checking to the integrity of the physical structure of the page and record headers and the allocation consistency of the database. This check is designed to provide a small overhead check of the physical consistency of the database, but it can also detect torn pages, checksum failures, and common hardware failures that can compromise a user's data. However it is recommended that you periodically run DBCC CHECKDB with no options for a through analysis of the database. How frequently you should perform these runs depends on your environment.

Personally I recommend using DBCC CHECKDB on a scheduled pattern to make sure none of the database is corrupted in line with Microsoft’s recommendations. It is always advisable to explain all stake-holders about the importance of this preventative maintenance and buy a time when you can run CHECKDB on your production databases in a scheduled basis.