Home Forums SQL Server 2008 SQL Server 2008 Performance Tuning Sudden PLE Drop Possible Without Memory Pressure? RE: Sudden PLE Drop Possible Without Memory Pressure?

  • March 10, 2014 at 7:54 am

    #1695526

    timothy.lazarus (3/9/2014)

    Okay, after reading through everything I could get my hands on regarding SQL memory, I think I've identified the culprit. Per VMware's recommendation, I had enabled large page allocation. However, what they fail to mention is that one very large consequence of this is that dynamic allocation of memory to the buffer pool is disabled, and that what SQL can allocate at startup is what you get. It looks like my instance of SQL Server was only able to grab enough memory to allocate 5GB to the buffer pool, hence the odd looking values in the DMVs. When I disabled Large Page Allocation and restarted SQL, the amount of memory being used by SQL climbed to the max, and the buffer pool almost tripled in size.

    Guess I learned a lot today 🙂

    Thanks to your post, I did, too! Thank you for taking the time. I really appreciate it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)