Post reply

reduce max server memory

  • October 4, 2017 at 5:12 pm

    #328948

    Hi All,
    So we got alerted that the sql server was using 90% memory.

    I think the only fix is to reduce max sql server memory , I reduced it and then the sql server was using 80% memory which is good.

    What is the effect of reducing max serve memory on the fly? Does it make sql server recreate the cached plans and store them in memory thereby reducing performance and causing time outs?

    Thanks

  • October 5, 2017 at 2:50 am

    #1962415

    sqlguy80 - Wednesday, October 4, 2017 5:12 PM

    Hi All,
    So we got alerted that the sql server was using 90% memory.

    Normal, expected, desired behaviour.

    I think the only fix is to reduce max sql server memory , I reduced it and then the sql server was using 80% memory which is good.

    Not a fix, since high memory usage is desired. And desiring 20% of the server's memory idle is weird. Do you also want 20% of the desks in the office permanently empty, 20% of shelf space empty?

    What is the effect of reducing max serve memory on the fly? Does it make sql server recreate the cached plans and store them in memory thereby reducing performance and causing time outs?

    Less data cached in the data cache, fewer plans cached in the plan cache, overall poorer performance (though usually only slightly)

    The only time when lowering max server memory is indicated is when there's less than about 1MB in overall available memory (after SQL and OS have taken what they need)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply