Possible Memory Issue

Last Post 20 Sep 2007 12:13 PM by mddba. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
mddba
New Member
New Member

--
20 Sep 2007 11:33 AM
Hello All,

We're running SQL 2000 Enterprise, SP4 build 2187 on Windows 2003 Datacenter Server. AWE is enabled. We have a 16 GB server with 14.5 dedicated for SQL. I am trying to determine if we have a memory bottleneck. All perfmon counters look reasonable except for Page Life Expectancy, which seems very low. If pages/sec is almost always at 0 but Page Life Expectancy is almost always below 300 (as low as 50 sometimes), is there a bottleneck? Maybe I don't understand but if Page Life Exp. dips below 300, shouldn't we start to see paging?

Any help is appreciated. Here's the perfmon memory counters:

MEMORY - Available MB - 511
MEMORY - Pages/ Sec - 0.000
SQLServer:Buffer Manger - Buffer Cahche Hit ratio - 99.863
SQLServer:Buffer Manger - Database pages - 1754977
SQLServer:Buffer Manger - Page Life Expectancy - 150
SQLServer:Buffer Manger - Stolen Pages - 139562
SQLServer:Buffer Manger - Target pages - 1900544
SQLServer:Buffer Manger - Total pages - 1900544
SQLServer:Memory Manager - Target Server Memory(KB) - 15211840
SQLServer:Memory Manager - Total Server Memory(KB) - 15211840

Again, thanks.
mddba
New Member
New Member

--
20 Sep 2007 12:13 PM
How do I reconcile the very low page life expectancy with no paging and the high buffer cache hit ratio? Is page life expectancy as useful an indicator as paging and the buffer cache hit ratio?

thanks.
mddba
New Member
New Member

--
26 Sep 2007 11:23 AM
Thanks to everyone who posted on this thread. I really appreciate the input and advice. To double check if I understand PLE correctly:
1) We can see low PLE and a high buffer cache hit ratio.

2) We can see a low PLE and no OS paging.

3) The low PLE indicates a PROBABLE memory issue, regardless of Buffer Cache hit ratio.

In response to the question about DB size, we have 7 user db's. The three largest mdf's are 65GB, 56GB and 28GB. The next largest mdf is temp at 4GB. I don't have TPS counts but I can say this is a highly used OLTP system.

So...given the low PLE and the fact it's unrealistic the vendor will work with us on query tuning, does anyone think we'd see a performance gain by adding RAM? GunnyK, in the mean time, I'll make the modification of allowing the OS 2 GB.

thanks.


Acceptable Use Policy
---