Data Integrity Validation

Transact-SQL has a set of DBCC statements used to verify the integrity of a database. The DBCC statements in Microsoft® SQL Server™ 2000 and SQL Server version 7.0 contain several improvements to the DBCC statements used in SQL Server version 6.5:

• The need to run the statements is reduced significantly. Two architectural changes in SQL Server have improved the robustness of the databases to the point that you do not have to verify their integrity:
  • The database engine has fail-fast logic to detect potential errors closer to the time they originate. This means errors are less likely to persist long enough to cause problems in a database.
    
    
  • The data structures in the database are simpler. This means they are easier to manage and less likely to have errors.
• It is not necessary to run DBCC validation statements as part of your normal backup or maintenance procedures. You should run them as part of a system check before major changes, such as before a hardware or software upgrade, or after a hardware failure. You should also run them if you suspect any problems with the system.
  
  
• SQL Server 2000 introduces a new PHYSICAL_ONLY option that allows a DBCC statement to run faster by only checking for the types of problems likely to be generated by a hardware problem. Run a DBCC check with PHYSICAL_ONLY if you suspect a hardware problem on your database server.
  
  
• The DBCC statements themselves also run significantly faster. Checks of complex databases typically run 8 to 10 times faster, and checks of some individual objects have run more than 300 times faster. In SQL Server 6.5, DBCC CHECKDB processed the tables serially. For each table, it first checked the structure of the underlying data and then checked each index individually. This resulted in a very random pattern of reads. In SQL Server 2000, DBCC CHECKDB performs a serial scan of the database while performing parallel checks of multiple objects as it proceeds. SQL Server 2000 also takes advantage of multiple processors when running parallel DBCC statements.
  
  
• The level of locks required by SQL Server 2000 DBCC statements are much lower than in SQL Server 7.0. DBCC statements can now be run concurrently with data modification statements, significantly lowering their impact on users working in the database.
  
  
• The SQL Server 2000 DBCC statements can repair minor problems they might encounter. The statements have the option to repair certain errors in the B-tree structures of indexes, or errors in some of the allocation structures.

See Also

DBCC

Optimizing DBCC Performance