DBCC CHECKDB

Question

DBCC CHECKDB

Mani-584606

SSChampion

Points: 11788
More actions
April 1, 2010 at 4:51 pm

#146253

Hi,
We have sql server 2005 ee 64 bit with SP3 and have 4 GB RAM. Max memory is set to 3 GB & left 1 GB for OS. We have a database of size 8 GB.
If we run DBCC CHECKDB on this database, it should load 8GB of data to Memory & then perform the DBCC CJECKDB? Because when ever I run DBCC CHECKdB, the PLE dropping to 30 to 40 sec?
If that is the case, we need to have Max memory setting as always greater than total databases size in that instance right?
Please advice

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

GregoryF SSCertifiable Points: 6296 More actions · Answer 1

Memory and caching are far more complicated than that. There are other db's on the server, execution plans are cached, indexes, etc... Just leave your memory setting where they are and let SQL determine what it cache's and what's left.

It's not just a matter of db size versus memory.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

I agree that it is more complicated.

Imagine the implications of having a 1TB database, would you also have 1TB of memory?

Your max memory setting would not correlate to aggregate of DB size.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Mani-584606 SSChampion Points: 11788 More actions · Answer 3

thank you,

So is that normal to have low PLE while running DBCC CHECKDB? after DBCC CHECKDB completes, then the PLE starts increasing gradually. Can we ignore low PLE while running dbcc checkdb?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Since a CHeckDB also performs a checktable (which checks all of the pages in the database), it would seem that it should drop the PLE very low. Somebody else can probably explain it better or correct me here. It would seem that the pages should be flushed in order to properly check them. When the pages are flushed, then you will see a drop in PLE.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Mani-584606 (4/1/2010)
So is that normal to have low PLE while running DBCC CHECKDB?

Absolutely. CheckDB reads every allocated page in the entire database, hence you're going to have a lot of churn in the data cache as pages come in get checked and later get replaced by others.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

GilaMonster (4/2/2010)
Mani-584606 (4/1/2010)
So is that normal to have low PLE while running DBCC CHECKDB?
Absolutely. CheckDB reads every allocated page in the entire database, hence you're going to have a lot of churn in the data cache as pages come in get checked and later get replaced by others.

Thanks for clarifying that Gail.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

gary1 SSC-Insane Points: 21152 More actions · Answer 7

Absolutely. CheckDB reads every allocated page in the entire database, hence you're going to have a lot of churn in the data cache as pages come in get checked and later get replaced by others

So I'm understanding in way that we can safely ignore the low PLE only when DBCC CHECKDB runs right?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

gmamata7 (4/2/2010)
Absolutely. CheckDB reads every allocated page in the entire database, hence you're going to have a lot of churn in the data cache as pages come in get checked and later get replaced by others
So I'm understanding in way that we can safely ignore the low PLE only when DBCC CHECKDB runs right?

Yes - they are meaningless during a CHECKDB, as far as interpreting pressure.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events