Post reply

Configure instance memory through min and max server

  • February 17, 2009 at 3:17 pm

    #127843

    I would like yours though about how to configured min server memory and max memory server for a dedicated server box. Both articles are for performance. One article's for a biztalk implementation while the other is the pure sql implementation. However for this scenario, biztalk is using a dedicated sql box, so in other word using a pure sql configuration for maximizing memory performance.

    Therefore, I found those two contradictory assertions, both from Microsoft:

    SQL Server memory should be set to a fixed value by setting both Min Server Memory and Max Server Memory to the same value. In general, allocate 75% of physical memory to SQL Server and leave 25% for the rest of the operating system and any applications. If this is a dedicated SQL Server, you can decrease the amount reserved for the operating system to a minimum of 1GB.

    http://technet.microsoft.com/en-us/library/cc558618.aspx

    Do not set min server memory and max server memory server configuration options to the same value, thereby fixing the amount of memory allocated to SQL Server. Dynamic memory allocation gives you the best overall performance over time. For more information, see Server Memory Options.

    http://msdn.microsoft.com/en-us/library/ms177455(SQL.90).aspx

    From my personal point of view, for a dedicated box, I would leave alone min and max server memory configuration option. But am I missing something that would be preferable to set them to a fixed number like technet suggest?

  • February 17, 2009 at 3:24 pm

    #945552

    I will not follow flat 75-25 rule from MS. as if the server is having 64gig+ memory, leaving 25 % of memory for OS will be very high. there is one very good article, which may help you in deciding the appropriate memory setting for SQL Server:

    http://blogs.technet.com/askperf/archive/2008/03/25/lock-pages-in-memory-do-you-really-need-it.aspx

  • February 18, 2009 at 10:13 am

    #946104

    Thanks for the article it has providing interesting information about memory usage.

    The next step I believe would be to find memory ressources for each applications running on the box. I wonder if the "Peak Working Set" memory view of process explorer could be a quick way to gather the applications memory footprint.

    I should check that once back at the office

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

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