Setting SQL Server Maximum Memory

One of the dafter defaults in SQL Server is the Maximum Server Memory (MB) – set to a value of 2147483647. Who has a server with 2PB RAM? Give me a call if you do – I want to have a look!

SQL Server’s standard operating procedure is to gradually take over all the memory it can, up to the limit specified, and then some – SQL Server also puts some objects outside of that pool. However, when it does that, there’s a risk that the operating system (and anything else running on the server) will be starved of resources, and SQL Server will be forced to yield memory back to the OS, which can result in a performance hit while that happens. [I blogged about a Memory Pressure query last year.] Generally, it’s a Good Thing that SQL Server does this – after all, the bulk of this is cached data – previously read, and retained in memory in case someone else wants to use it later – improves performance by reducing the number of times the server has to go to disk to get data.

The thing to do, ideally, is to configure the maximum server memory when you build the server; however, sometimes you walk into a place where there are many servers where this hasn’t been done, or are otherwise looking for a quick way to determine what the setting should be. Jonathan Kehayias of SQLSkills blogged about a sensible SQL Server Maximum memory calculation (in response to a post elsewhere about a really dodgy memory config advisor, but I’m not going to link to that…)

What I’ve done below is codify that knowledge into a nice friendly T-SQL query that you can run, below. It makes use of the sys.dm_os_sys_info DMV to get the memory physically in the server; that DMV, though, has changed form between SQL 2008R2 and SQL 2012, the new version reporting physical_memory_kb whereas the previous version had physical_memory_in_bytes. Hence a bit of dynamic SQL nastiness at the start of the query.

DECLARE @CurMemoryMB BIGINT ;
DECLARE @PhysMemMB BIGINT ;
DECLARE @CalcdMemMB BIGINT ;
 
SELECT
    @CurMemoryMB = CONVERT(BIGINT, value)
FROM
    sys.configurations
WHERE
    name LIKE 'max server memory%' ;
 
DECLARE @physmemtable TABLE ( PhysMemMB BIGINT ) ;
 
DECLARE @ver INT ;
DECLARE @ProductVersion VARCHAR(20) ;
DECLARE @memoryquery VARCHAR(4000) ;
 
SELECT
    @ProductVersion = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)) ;
 
SELECT
    @ver = CAST(LEFT(@ProductVersion, CHARINDEX('.' ,
                                                   @ProductVersion
                                               ) - 1) AS INT) ;
 
SELECT
    @memoryquery = 'SELECT ' + CASE
                                   WHEN @ver >= 11
                                       THEN 'physical_memory_kb / 1024 '
                                   ELSE
                                       'physical_memory_in_bytes / (1024*1024) '
                               END + ' AS PhysMemMB FROM sys.dm_os_sys_info' ;
 
INSERT INTO @physmemtable EXEC(@memoryquery) ;
 
SELECT
    @PhysMemMB = MAX(p.PhysMemMB)
FROM
    @physmemtable AS p ;

Yeah, it’s not the prettiest bit of code I’ve ever written, but it does the job.

This next chunk is where we take the @PhysMemMB and calculate what Jonathan recommends it should be (approximately – I’ve tweaked it a little for low memory servers, of which we have enough to depress me). His recommendations can be summarised as:

• reserve 1GB RAM for the OS
• reserve a further 1GB RAM for every 4GB between 4GB and 16GB
• reserve a further 1GB RAM for every 8GB above 16GB

My calculation routine:

SELECT
    @CalcdMemMB = CASE
                      WHEN @PhysMemMB <= 2048
                          THEN 1024
                      WHEN @PhysMemMB <= 4096
                          THEN @PhysMemMB - 1024
                      WHEN @PhysMemMB <= 16384
                          THEN @PhysMemMB * 3 / 4
                      ELSE
                          12288 + (( @PhysMemMB - 16384 ) * 7 / 8 )
                  END ;

Close enough. 🙂

The last chunk of the query is based around calling sp_configure to alter the “max server memory (MB)” setting. You may need to enable advanced settings first; I’ve been polite, and written code to check whether the advanced options are visible, make them visible if not, and re-invisiblificate them if applicable…

The full query:

DECLARE @CurMemoryMB BIGINT ;
DECLARE @PhysMemMB BIGINT ;
DECLARE @CalcdMemMB BIGINT ;
 
SELECT
    @CurMemoryMB = CONVERT(BIGINT, value)
FROM
    sys.configurations
WHERE
    name LIKE 'max server memory%' ;
 
DECLARE @physmemtable TABLE ( PhysMemMB BIGINT ) ;
 
DECLARE @ver INT ;
DECLARE @ProductVersion VARCHAR(20) ;
DECLARE @memoryquery VARCHAR(4000) ;
 
SELECT
    @ProductVersion = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)) ;
 
SELECT
    @ver = CAST(LEFT(@ProductVersion, CHARINDEX('.' ,
                                                   @ProductVersion
                                               ) - 1) AS INT) ;
 
SELECT
    @memoryquery = 'SELECT ' + CASE
                                   WHEN @ver >= 11
                                       THEN 'physical_memory_kb / 1024 '
                                   ELSE
                                       'physical_memory_in_bytes / (1024*1024) '
                               END + ' AS PhysMemMB FROM sys.dm_os_sys_info' ;
 
INSERT INTO @physmemtable EXEC(@memoryquery) ;
 
SELECT
    @PhysMemMB = MAX(p.PhysMemMB)
FROM
    @physmemtable AS p ;
 
SELECT
    @CalcdMemMB = CASE
                      WHEN @PhysMemMB <= 2048
                          THEN 1024
                      WHEN @PhysMemMB <= 4096
                          THEN @PhysMemMB - 1024
                      WHEN @PhysMemMB <= 16384
                          THEN @PhysMemMB * 3 / 4
                      ELSE
                          12288 + (( @PhysMemMB - 16384 ) * 7 / 8 )
                  END ;
 
SELECT
    @CurMemoryMB AS CurrentMaxMemory ,
    @PhysMemMB   AS PhysicalMB ,
    @CalcdMemMB  AS CalculatedMB ;
 
DECLARE @Advanced INT ;
 
SELECT
    @Advanced = CONVERT(INT, value_in_use)
FROM
    sys.configurations
WHERE
    name = 'show advanced options' ;
 
IF @Advanced <> 1
BEGIN
    EXEC sp_configure 'show advanced options', 1 ;
 
    RECONFIGURE WITH OVERRIDE ;
END ;
 
EXEC sp_configure 'max server memory (MB)', @CalcdMemMB ;
 
RECONFIGURE WITH OVERRIDE ;
 
IF @Advanced <> 1
BEGIN
    EXEC sp_configure 'show advanced options', 0 ;
 
    RECONFIGURE WITH OVERRIDE ;
END ;

That’s it. A nice, easy query to set maximum memory to a sensible value. Of course, it may need tuning for certain servers if they’re running SSRS, SSAS, or any other software, but for a general SQL Server config, this is a good starting point. I’ve tested it on multiple versions of SQL from 2005RTM upwards, with memory from sub-4GB to 128GB+ machines.

A couple of options for running this quickly on multiple servers – either use the SQL Server Management Studio Registered Servers to run a query against multiple servers, or, for a little more control, open the query in SQLCMD Mode in SSMS, put a :CONNECT <> line at the top, and just change the servername.

Disclaimer: Don’t run code you don’t understand. No, really. Do not open it. Apparently this one is pretty nasty. It will not only erase everything on your hard drive, but it will also delete anything on disks within 20 feet of your computer.

It demagnetizes the stripes on ALL of your credit cards. It reprograms your ATM access code, screws up the tracking on your VCR and uses subspace field harmonics to scratch any CD’s you attempt to play. It will re-calibrate your refrigerator’s coolness settings so all your ice cream melts and your milk curdles. It will program your phone autodial to call only your ex-spouses’ number. This virus will mix antifreeze into your fish tank. It will drink all your beer. It will leave dirty socks on the coffee table when you are expecting company. Its radioactive emissions will cause your bellybutton fuzz (be honest, you have some) to migrate behind your ears. It will replace your shampoo with Nair and your Nair with Rogaine, all while dating your current boy/girlfriend behind your back and billing their hotel rendezvous to your Visa card. It will cause you to run with scissors and throw things in a way that is only fun until someone loses an eye. It will give you Dutch Elm Disease and Psitticosis. It will rewrite your backup files, changing all your active verbs to passive tense and incorporating undetectable misspellings which grossly change the interpretations of key sentences. It will leave the toilet seat up and leave your hair dryer plugged in dangerously close to a full bathtub. It will not only remove the forbidden tags from your mattresses and pillows, but it will also refill your skim milk with whole milk. It will replace all your luncheon meat with Spam. It will molecularly rearrange your cologne or perfume, causing it to smell like dill pickles. It is insidious and subtle. It is dangerous and terrifying to behold. It is also a rather interesting shade of mauve. These are just a few signs of infection. Bad times, indeed