Command to execute CHECKDB
DBCC CHECKDB (DBNAME); — This Option use a SNAPSHOT
Command to avoid creating a SNAPSHOT and saving the space on disk by using the option WITH TABLOCK .
By using this option there will be Exclusive locks on database which will prevent accessing database by users
Also dont run this command on Bussiness hours as it will impact the Production. This showuld be run only on Non-Bussiness hours.
DBCC CHECKDB (DBNAME) WITH TABLOCK; — This option does NOT use a SNAPSHOT
The other alternative command to get CHECKDB runs faster and this will reduce the Contention
This Option DBCC CHECKDB with PHYSICAL_ONLY checks physical structure of the page and it will detect the torn pages ,common hardware failures which can compromises the users data.
DBCC CHECKDB (DBNAME) WITH PHYSICAL_ONLY
TempDB Usage and Intensity of I\O
We all know that DBCC CHECKDB is an I\O Intensive operation and major concern is related I/O caused by spooling to TEMPDBDB Database
The process of CHECKDb uses TEMPDB Database heavily and also we may get Performance Impact
To reduce the Impact of Performance we may have to configure disks Properly .So, that we get better throughput