DBCC CHECKDB

Hi,
Just got a question about the way DBCC CHECKDB works...
When I run a DBCC CHECKDB statement against a database, are all the
pages for that database read from the disk (i.e. if there are pages for
the database in the buffer cache then these are not used)?
ThanksHi
Well, you can run SQL Server Profiler while this coomand is running and see
what is going on
<nate.vu@.gmail.com> wrote in message
news:1146716830.675530.159530@.u72g2000cwu.googlegroups.com...
> Hi,
> Just got a question about the way DBCC CHECKDB works...
> When I run a DBCC CHECKDB statement against a database, are all the
> pages for that database read from the disk (i.e. if there are pages for
> the database in the buffer cache then these are not used)?
> Thanks
>|||2000:
It reads from cache. Below test verifies that (database is small enough to b
e cached entirely):
EXEC sp_configure 'max server', 500
reconfigure
shutdown
CHECKPOINT
DBCC DROPCLEANBUFFERS
DECLARE @.dt datetime
SET @.dt = GETDATE()
DBCC CHECKDB(credit) WITH NO_INFOMSGS
SELECT DATEDIFF(ms, @.dt, GETDATE())
SET @.dt = GETDATE()
DBCC CHECKDB(credit) WITH NO_INFOMSGS
SELECT DATEDIFF(ms, @.dt, GETDATE())
SET @.dt = GETDATE()
DBCC CHECKDB(credit) WITH NO_INFOMSGS
SELECT DATEDIFF(ms, @.dt, GETDATE())
2005:
First I didn't see a significant difference between executions. Then I thoug
ht about the fact that
CHECKDB uses an internal database snapshot for this, so perhaps this is why
I don't see a big
difference difference in execution time on 2005...
Adding the TABLOCK option for CHECKDB show a consistent difference between f
irst execution (nothing
cache) and subsequent executions.
I believe that CHECMDB does indeed read from cache, but for my small databas
e (200MB) is so small so
that the overhead of using the internal database snapshot overshadows the di
fference between data in
cache vs data not in cache.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<nate.vu@.gmail.com> wrote in message news:1146716830.675530.159530@.u72g2000cwu.googlegroups
.com...
> Hi,
> Just got a question about the way DBCC CHECKDB works...
> When I run a DBCC CHECKDB statement against a database, are all the
> pages for that database read from the disk (i.e. if there are pages for
> the database in the buffer cache then these are not used)?
> Thanks
>