posted on Wednesday, June 14, 2006 3:07 AM
by
mz1313
DBCC CHECKDB
It appears that DBCC CHECKDB can cause performance degradation immidiately after it finishes. KB article is
here. In short, DBCC CHECKDB clears the whole procedure cache. Thus you can't reuse already compiled execution plans - they are just not there.
Another issue with DBCC CHECKDB that concerns only SQL Server 2005. In order to make DBCC CHECKDB run on transactionally consistent database, Yukon uses snapshot. It's hidden, so you can't tune it or even place at specific disk. The problem can occur when you have:
1. Large DB (so that DBCC CHECKDB takes significant time to complete)
2. Many DML operations at different tables (thus snapshot size will grow fast)
3. Low free space on disk.
So be careful - if your database usage patterns are similar to (1) and (2), verify that you have enough free space on disk prior to executing DBCC CHECKDB.
BTW, In SQL Server 2000 that's not the case. In order to provide transactional consistency it uses some clever mechanism that knows to integrate with transaction log.
Here is the detailed explanation (1st part of the future serie) about how DBCC CHECKDB works written by the guy that programmed it.
BTW,
here you can ask him to add more issues to the scope of the serie.