Page Life Expectancy

Question

Page Life Expectancy

pshaship

SSCertifiable

Points: 6125
More actions
March 12, 2010 at 12:40 am

#142059

Hi,
We have SQL Server 2005 Enterprise edition with SP3 and we have 4 SQL Instances on the same server.
We have 16 GB RAM and we set Max memory as 3 GB for each and left 4 GB for OS.
I'm getting the below message from monitoring tool weekly couple of times
Page Life Expectancy: The buffer cache page life expectancy is 58 seconds
Getting the Page Life Expectancy <300 seconds once in a while considered to be Memory pressure?
Or getting these spikes normal? As this low value NOT sustaining for much time. It's just for couple of seconds & that to weekly 2 to 3 times in a week
And how to capture what t-SQL code is running at that point (other than profiler)
Thanks for your inputs

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

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

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 1

The Page Life Expectancy is basically how long a page remains in memory (seconds). It is a sign that you do not have enough memory for SQL. If for example you have 1 instance that is using 1GB memory and then you have a 2GB high usage database, SQL will try to fit as much of the used portion of the database in the buffer pools (sql memory).

If you size that up to a 50GB database that has multiple 2GB queries/trans run against it youo start to see the problem. The PLE is the amount of time that a page can remain in memory before being overwritten with other data.

If you have 4 instances all sharing 12GB of memory then you start to see the issues, add to that sql CLR and Procedural caches (which come out of seperate memory) and you also have the additional overhead of multiple SQL instances.

Is a memory upgrade possible? If not could you consolidate two instances? Other then that i cant think of what to suggest.

Adam Zacks-------------------------------------------Be Nice, Or Leave

pshaship SSCertifiable Points: 6125 More actions · Answer 2

All the instances are for Biz Talk and the BizTalk databases are very small. The max database size that we have is 4 GB.

But PLE<300 coming once in a while. So that to be considered as Memory issue?

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 3

If its only very occasionally i wouldnt really worry (but keep it in mind cause its a sign that you may need to consider consolidation or memory upgrade).

What is the average PLE? And mean time between PLE <300?

Adam Zacks-------------------------------------------Be Nice, Or Leave

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

any kind of performance spike is normal. sustained values are the problem.

I will note that with 4 instances on the box you are 'wasting' roughly 1/8th of your total RAM in sql server instance 'overhead'. Not very effective, although sometimes required.

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

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 5

Here's a reference:

http://technet.microsoft.com/en-us/library/cc966401.aspx

marksullivan SSC Rookie Points: 34 More actions · Answer 6

I have seen other shops with BizTalk have memory issues and it was due to missing indexes. Check to see if you are doing unnessary table scans that could be done by index seeks. Because of your memory limitation, one or two table scans may force out memory that you need for other processes. If you could do the same with an index seek, other data will not be forced out of memory.