July 18, 2008 at 1:40 pm
Good afternoon. I've read several different posts about memory counters. Here's what I've gleaned. Generally, page life expectancy should be above 300, correct? However, this varies depending on the queries hitting the SQL server. So, technically, if a server is used for different OLTP applications (mostly 3rd-party, which are horribly written...using lots of select *'s), page life expectancy can consistently be below 300, right? Other memory counters are within acceptable ranges - buffer cache hit ratio above 99% and page/sec below 20. Am I right?
Thanks...Chris
July 23, 2008 at 7:53 pm
Let me take the PLE (page life expectancy) part of your question. The PLE measures in seconds how long a page remains in memory before being reclaimed/flushed. In certain situations this counter can go below (down to zero) for short periods of time. If it remains below this threashold consistently SQL Server is consistently flushing pages and has fewer opportunities to reuse pages - this means more disk IO... and more CPU...
As a result, if you see a low PLE you are likely to experience a memory problem and are likely experiencing higher disk IO and CPU than necessary.
So to answer your question directly: "page life expectancy can consistently be below 300, right?" - it can, but it's not what you want. You need the PLE at or above 300 to be in the norm.
So how do you increasing the PLE? One of the following:
- Add memory
- Consider using /3GB for 32-bit systems
- Consider using a 64-bit platform (memory problems just go away... it's that simple...)
- If the system is running reports, consider off-loading the reports to another server (using replication perhaps to keep the data fresh)
- Consider purging the data of the application if you have large history records
- Consider splitting your OLTP databases on multiple servers
My favorite is the 64-bit approach - it allows database consolidation and provides a buffer for poorly coded 3rd party applications.
Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply