none
Low Page Life Expectancy

    Question

  • Hi

    I have a server with 16GB RAM. and by notes on the internet i put maximum and minimum of SQL Server memory is MAX (2147483647).

    Server has Windows Server 2003 R2 SP2 x86 (it can not be x64 due to it's hardware). 

    i have an alert for create mail when Page Life Expectancy below 10. it is about a week that it's around 10.

    but it is not obvious by users when server works.

    SQL Memory usage in task manager is around 1.8 GB far away from Server Memory and Maximum defined for it.

    what can i do to have a better performance?

     


    Regards Ahmad
    Monday, October 10, 2011 10:36 AM

Answers

  • Did you also configure SQL Server to use AWE and grant the service account for SQL Server the privilige "Lock pages in memory"? (All this should be in articles you find...)
    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by Ahmad Anbaran Tuesday, October 11, 2011 10:09 AM
    Tuesday, October 11, 2011 8:27 AM
  • Please check that AWE is enabled. Run the following and post the output.
    SP_CONFIGURE 'show advanced options', 1
    GO
    RECONFIGURE
    GO
    SP_CONFIGURE
    GO
    

    Does the account running the SQL Server service have the locked pages in memory privilege? http://msdn.microsoft.com/en-us/library/ms190730(v=SQL.90).aspx



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you
    • Marked as answer by Ahmad Anbaran Tuesday, October 11, 2011 10:09 AM
    Tuesday, October 11, 2011 8:32 AM
  • Did you try restarting SQL Server since adding the lock pages in memory privilege?

    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you
    • Marked as answer by Ahmad Anbaran Tuesday, October 11, 2011 10:09 AM
    Tuesday, October 11, 2011 8:49 AM

All replies

  • Hi,

    From the looks of things, you have neither the PAE switch in the boot.ini, or AWE enabled.  On your 32 bit system, you have a virtual address space of 4GB; 2GB for user apps, and 2GB for the OS.  This is why in task manager you're only seeing SQL Server using 1.8GB RAM (N.B. you should use perfmon to gather metrics, not task manager).    If you put the /PAE switch in your boot.ini, you can present more memory to SQL Server.  You then enable AWE via sp_configure in SQL Server to take advantage of the visible memory.

    You should then cap the max server memory setting at approx. 12GB.  Adjust as necessary.

    Once you've done this, you should find that SQL Server doesn't have to swap stuff out of the buffer pool so quickly and your PLE will steadily increase.



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you
    Monday, October 10, 2011 11:15 AM
  • Hi

    as you said AWE  is enabled.

    which parameter in performance monitor i should watch. and if it is ok with memory why PLE is low.

    Thanks

    Ahmad


    Regards Ahmad
    Monday, October 10, 2011 11:43 AM
  • Hi

    as you said AWE  is enabled.

    which parameter in performance monitor i should watch. and if it is ok with memory why PLE is low.

    Thanks

    Ahmad


    Regards Ahmad


    Hi Ahmad,

    PLE will be low for lots of reasons.

    First check you total & target memory

    select

    * from sys.dm_os_performance_counters

    where

    counter_name like

    '%server%'

     



    Muthukkumaran Kaliyamoorthy

    Helping SQL DBAs and Developers >>> SqlserverBlogForum
    Monday, October 10, 2011 11:55 AM
  • Have a look at this article from Brent Ozar for SQL Server perfmon counters: http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

    Do you have the /PAE switch in the boot.ini?

    Your PLE is low because the OS is only presenting 2GB of usable RAM to SQL Server, thereby reducing the size of the buffer pool.



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you
    Monday, October 10, 2011 11:58 AM
  • I thought that Task Manager were totally PAE/AWE unaware and the value would be something ridicilous like 100 MB when running with PAE/AWE. I.e., the fact that Tasl Manager does show a reasonable value I would take as a sign that it *isn't* running in AWE mode! Which in turn can be a contributing factor to low PLE. The question in the end, IMO, is why running a 32 bit server nowadays in the first place?
    Tibor Karaszi, SQL Server MVP | web | blog
    Monday, October 10, 2011 2:14 PM
  • Hi

    first of all hardware dept says that they server has issues with x64 (maybe it is too old!). i have executed query and it has a result:

    SQLServer:Memory Manager | Target Server Memory (KB) |  | 1507008 | 65792
    SQLServer:Memory Manager | Total Server Memory (KB) |  | 1507008 | 65792

    is it means server memory is 1.5 GB? what can i do with that?

     


    Regards Ahmad
    Tuesday, October 11, 2011 4:58 AM
  • Then I would guess that you didn't fully enable PAE and AWE. I suggest you fire up your favourite search engine and search for something like below (and add your version of the OS):

    enable PAE


    Tibor Karaszi, SQL Server MVP | web | blog
    Tuesday, October 11, 2011 6:10 AM
  • I have /PAE switch in boot.ini server. all documents say that is enough. i have another switch: /noexecute=optout

    has that effects on PAE enabling?

     


    Regards Ahmad
    Tuesday, October 11, 2011 6:52 AM
  • /PAE enables PAE

    http://support.microsoft.com/kb/274750


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, October 11, 2011 6:54 AM
  • I have /PAE switch in boot.ini server. all documents say that is enough. i have another switch: /noexecute=optout

    has that effects on PAE enabling?

     


    Regards Ahmad

    Can you show me your whole boot.ini file.

    Muthukkumaran Kaliyamoorthy

    Helping SQL DBAs and Developers >>> SqlserverBlogForum
    Tuesday, October 11, 2011 7:12 AM
  • Also tell me your OS/SQL editions

    Muthukkumaran Kaliyamoorthy

    Helping SQL DBAs and Developers >>> SqlserverBlogForum
    Tuesday, October 11, 2011 7:15 AM
  • Hi

    My boot.ini:

    [boot loader]
    redirect=usebiossettings
    timeout=30
    default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
    [operating systems]
    multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /redirect /PAE

    Server Windows: Windows Server 2003 R2 SP2 x86

    SQL Server: SQL Server 2008 10.0.3798.0

     


    Regards Ahmad
    Tuesday, October 11, 2011 7:40 AM
  • Did you also configure SQL Server to use AWE and grant the service account for SQL Server the privilige "Lock pages in memory"? (All this should be in articles you find...)
    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by Ahmad Anbaran Tuesday, October 11, 2011 10:09 AM
    Tuesday, October 11, 2011 8:27 AM
  • Please check that AWE is enabled. Run the following and post the output.
    SP_CONFIGURE 'show advanced options', 1
    GO
    RECONFIGURE
    GO
    SP_CONFIGURE
    GO
    

    Does the account running the SQL Server service have the locked pages in memory privilege? http://msdn.microsoft.com/en-us/library/ms190730(v=SQL.90).aspx



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you
    • Marked as answer by Ahmad Anbaran Tuesday, October 11, 2011 10:09 AM
    Tuesday, October 11, 2011 8:32 AM
  • hi

    it shows an error

    Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
    Msg 5845, Level 16, State 1, Line 1
    Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.

    i have changed sql server service account to a new domain account (member of server administrator). when you noticed that i set the privilege of "Lock Page on Memory" to service account and also my personal domain account. bu when i executed your query (reconfigure) it shows error as pasted above.

     


    Regards Ahmad
    Tuesday, October 11, 2011 8:41 AM
  • Did you try restarting SQL Server since adding the lock pages in memory privilege?

    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you
    • Marked as answer by Ahmad Anbaran Tuesday, October 11, 2011 10:09 AM
    Tuesday, October 11, 2011 8:49 AM
  • hi

    i restarted SQL server and it's PLE is increasing it's 300 now. but it's lunch time and i should take care of that in next 1 hour to view if it's solved or not.

    thank you all for helping

     


    Regards Ahmad
    Tuesday, October 11, 2011 8:57 AM
  • Sounds good.  Don't forget to put a cap on the max server memory so that SQL Server doesn't hog all the memory for itself... I'd start at 12-13Gb and work from there

    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you
    Tuesday, October 11, 2011 9:02 AM
  • hi

    after all, it worked and PLE counter shows 4465 now and increasing all the time.

    is it important to do this for a server that only serves SQL Server? (i mean limiting maximum memory for SQL Server)

     


    Regards Ahmad
    Tuesday, October 11, 2011 10:08 AM
  • I would.  You don't want to starve the OS, and there's stuff (linked servers, CLR, etc) that uses memory outside the buffer pool that could also be affected.  It's a balancing act though, so you might find you can adjust the setting quite a bit to suit your environment.

    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you
    Tuesday, October 11, 2011 10:16 AM