• Arsh - Thursday, September 14, 2017 2:44 AM

    GilaMonster - Wednesday, September 13, 2017 11:28 AM

    Jeff Moden - Wednesday, September 13, 2017 6:33 AM

    SQL Server will do its damnedest to pre-allocate all of the memory that it has been allowed to use. 

    Not in current versions. It used to be the case that min server memory was allocated on startup, but that hasn't been the case for a long time.
    SQL will take what it needs, if it needs more and hasn't reached max server memory yet, it will allocate more. It will aggressively ramp up the buffer pool initially, but it won't grab more than it needs.
    That said, there's more that needs memory than just the data cache.

    The only time it pre-allocates is when large pages are used, as memory allocated that way cannot be resized after allocation.

    Thanks  Gail, Jeff and ZZartin for sharing your valuable thoughts on this.

    Just because you quoted it, let me say again that I shouldn't have used the word "pre-allocate" in my post because that's incorrect.  As Gail said, it's pretty aggressive in allocating memory to itself but it's when there's a need.  It doesn't do pre-allocation except for the relatively low amount that the instance minimum memory setting is set to.  If that's high, that may be the cause of what you're seeing.

    --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)