SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 regular drops of page life expectancy to zero
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

influent
Constraint Violating Yak Guru

USA
367 Posts

Posted - 11/09/2011 :  14:21:11  Show Profile  Reply with Quote
Well in my case it didn't make a difference, the PLE still dropped to zero with CHECKDB running against the scrubbed databases.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36674 Posts

Posted - 11/09/2011 :  14:47:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
You'll want to add more RAM if it becomes a problem in the future. If your system has any kind of slower period, make sure to run that process during that period. On our busiest and most critical systems, we run checkdb on a backup/restore server. We just can't afford the heavy I/O checkdb puts on the system.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/09/2011 :  15:40:51  Show Profile  Reply with Quote
"On our busiest and most critical systems, we run checkdb on a backup/restore server"

Me too.
Go to Top of Page

sql-lover
Yak Posting Veteran

84 Posts

Posted - 11/13/2012 :  14:23:13  Show Profile  Reply with Quote
Bringing this thread to life!

And thanks Tara for your comments and follow up. Interesting conversation.

I may have the same problem at work. I just created hundreds of maintenance plans using smart jobs. The databases have not been checked or defrag in years. But now we are getting some timeouts issues that coincidentally occur during the time the Integrity Check jobs runs. I do have separate Integrity Jobs though, one per database.

Now, while checking a monthly performance monitor that I have for one of my system, I noticed that page life expectancy drop horrible during the exact time the dbcc checkdb occurs. That's what makes me think there should be some relationship between both.

How I can accomplish the Integrity Check on a different system when the DBA deals with hundreds of databases? Manually will be time consuming. Any automatic or script solution for that? I guess we are talking about these steps

-Restore existing backup for a database
-Run Integrity Check
-Send alert if job fails
-Delete the restored database

I do not see above solution for all databases at the same time, as we will require massive amount of space.

Any comments?

Thanks,
Go to Top of Page

robvolk
Most Valuable Yak

USA
15658 Posts

Posted - 11/13/2012 :  14:34:32  Show Profile  Visit robvolk's Homepage  Reply with Quote
There's a lot of guidance here:

http://www.sqlskills.com/blogs/paul/post/CHECKDB-From-Every-Angle-Consistency-Checking-Options-for-a-VLDB.aspx
Go to Top of Page

sql-lover
Yak Posting Veteran

84 Posts

Posted - 11/13/2012 :  14:46:15  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

There's a lot of guidance here:

http://www.sqlskills.com/blogs/paul/post/CHECKDB-From-Every-Angle-Consistency-Checking-Options-for-a-VLDB.aspx



Great link :-) ... I actually read it already.

My question was more towards a specific solution for doing offsite db checks, like via scripts.

I would like to adopt an existing process, instead of re-inventing the wheel.

I MUST check my databases. I can't afford a data corruption. My alternative are:

-Doing Integrity Jobs monthly basis (kind of scary)
-Using a separate system.

But I am not willing to take the 2nd approach if it won't be an automatic process and for each and all of my databases. Otherwise, I prefer to live with the PLE issue than taking the risk of ending with corrupted database.

Edited by - sql-lover on 11/13/2012 14:48:05
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 11/13/2012 :  14:51:35  Show Profile  Visit chadmat's Homepage  Reply with Quote
I thought DBCC CheckDB used disfavoring so as to not flush out your buffer pool? I could swear Paul Randal said that, but maybe I am mistaken.

-Chad
Go to Top of Page

sql-lover
Yak Posting Veteran

84 Posts

Posted - 11/13/2012 :  15:08:08  Show Profile  Reply with Quote
quote:
Originally posted by chadmat

I thought DBCC CheckDB used disfavoring so as to not flush out your buffer pool? I could swear Paul Randal said that, but maybe I am mistaken.

-Chad



Well, I checked my perf. monitor and filtered by that exact time; I can see how the PLE goes down almost instantly, when jobs kick in, and goes up an hour or so later, which is basically when all the checks are done.

I should mention that the two servers are really old. Not enough RAM and running SQL 2005. That probably is exacerbating the issue.

My perf. monitor did not run for my SQL 2008 boxes, so I cannot validate there, but the errors come from those two old machines.

There is a high chance the DBCC command has something to do there.

If I can't find an existing offsite dbcc check solution, maybe I will change schedule (big dbs) to monthly and push it a bit, so it won't overlap with the smaller dbs. That may alleviate the problem.

Edited by - sql-lover on 11/13/2012 15:11:24
Go to Top of Page

robvolk
Most Valuable Yak

USA
15658 Posts

Posted - 11/13/2012 :  17:28:58  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
Originally posted by chadmat

I thought DBCC CheckDB used disfavoring so as to not flush out your buffer pool? I could swear Paul Randal said that, but maybe I am mistaken.
I'm pretty sure I heard the same thing from him. Maybe it's different for 2005 vs. 2008+?

If this is really a big problem, add more RAM, it's ridiculously cheap now, about $10/GB. We just bought a little over 1 TB for about $12.5K. If you're maxed out on your servers, then upgrade them, or move those databases to better boxes. Otherwise look at piecemeal checks like Paul suggested.
Go to Top of Page

sql-lover
Yak Posting Veteran

84 Posts

Posted - 11/14/2012 :  09:37:42  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

quote:
Originally posted by chadmat

I thought DBCC CheckDB used disfavoring so as to not flush out your buffer pool? I could swear Paul Randal said that, but maybe I am mistaken.
I'm pretty sure I heard the same thing from him. Maybe it's different for 2005 vs. 2008+?

If this is really a big problem, add more RAM, it's ridiculously cheap now, about $10/GB. We just bought a little over 1 TB for about $12.5K. If you're maxed out on your servers, then upgrade them, or move those databases to better boxes. Otherwise look at piecemeal checks like Paul suggested.



Agree!

Adding more RAM is a cheap and quick solution, but management refuses to do it, because the downtime. Long history.

I will move these two boxes with all those 200+ databases to a brand new Cluster soon.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000