Low Page life expectancy when SQL server service fail over to passive node

Question

Low Page life expectancy when SQL server service fail over to passive node

Mani-584606

SSChampion

Points: 11788
More actions
March 14, 2010 at 4:03 pm

#139325

Hello,
Is that NORMAL to get low page life expectancy when the SQL server service fail over to passive node?
Because I'm seeing very low value of 4 for Page life expectancy when cluster failovers.
thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 1

what is the "normal" value? ie, before its failed over?

_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 2

Anything below 300 is a cause for concern for PLE.

What is the BCHR on the node when failover occurs? less than 99% ?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Mani-584606 (3/14/2010)
Hello,
Is that NORMAL to get low page life expectancy when the SQL server service fail over to passive node?
Because I'm seeing very low value of 4 for Page life expectancy when cluster failovers.
thanks

I would expect a time early after the failover to see counters indicating IO issues as the server brings data into the buffer pool. But if your system is configured properly this should quickly taper off.

1) What is the 'normal' PLE for the primary node?

2) Does the PLE go up after a while on secondary node?

3) Are the machines EXACTLY identical, including such detailed things as firmware/driver levels, HBA settings, etc? Many companies skimp on the failover machine and are disappointed with the results...

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

RML51 SSCarpal Tunnel Points: 4410 More actions · Answer 4

If it's starved for memory your PLE may drop. Do both nodes have identical memory?

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

1) What is the 'normal' PLE for the primary node?

It's>300

2) Does the PLE go up after a while on secondary node?

Yes, PLE reaches >300 in <2 mins

3) Are the machines EXACTLY identical, including such detailed things as firmware/driver levels, HBA settings, etc? Many companies skimp on the failover machine and are disappointed with the results...

Yes, they are same. I'm seeing this message whenever the following things happen:

1. Fail over to other node

2.Running DBCC CHECKDB-->Some times PLE = 3 0r 4 when this job runs but not all the time

3.Rebuild/Reorganize Indexes-->Some times PLE = 3 0r 4 when this job runs but not all the time

thanks

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Mani-584606 (3/15/2010)
1) What is the 'normal' PLE for the primary node?
It's>300
2) Does the PLE go up after a while on secondary node?
Yes, PLE reaches >300 in <2 mins
3) Are the machines EXACTLY identical, including such detailed things as firmware/driver levels, HBA settings, etc? Many companies skimp on the failover machine and are disappointed with the results...
Yes, they are same. I'm seeing this message whenever the following things happen:
1. Fail over to other node
2.Running DBCC CHECKDB-->Some times PLE = 3 0r 4 when this job runs but not all the time
3.Rebuild/Reorganize Indexes-->Some times PLE = 3 0r 4 when this job runs but not all the time
thanks

So steady-state you don't have a problem on the passive node when it is active. That is good. Items 2 and 3 you list are HUGELY IO INTENSIVE and are expected to hit your IO subsystem and buffer pool HARD. The fact that your PLE gets so low indicates too little RAM and/or too slow IO subsystem. You might consider dropping MAXDOP to 1 or 2 during such operations. Tempdb location/throughput could be suboptimal too.

Lots of things could be evaluated/improved on your system. I highly recommend a short engagement with a performance tuning professional to quickly get you where you need to be and mentor you on how to keep a better eye on things.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service