Memory Settings on SQL Server and Analysis Services

I’ve recently been working in a development environment in which database services and Analysis Services are sharing the same server.  We’re in the fortunate position of having a hugely powerful machine to run the services on (16 cores and 128 gigbytes of RAM), but it got me thinking about the different ways the services manage memory, and how you can configure each of them to leave your server responsive under all circumstances.

First, a quick summary of the available options.

SQL Server

The two settings are Minimum Server Memory and Maximum Server Memory.

• Minimum Server Memory is the least amount of memory SQL Server will use.  In effect, that memory will be reserved for its use.  The default value is zero.
• Maximum Server Memory is the highest amount of memory SQL Server will use.  The default value is an unfeasibly high number.

Analysis Services

Memory is handled very differently in Analysis Services (predictably!).  Instead of setting usage values in absolute size, there are “percentage of total memory” parameters which control how Analysis Services works with memory.

• LowMemoryLimit.  Analysis Services will consume as much memory as it needs until it has reached this point.  When it uses memory above this limit, it will begin to clean up its memory.  The default varies between versions but is either 65% or 75%.
• TotalMemoryLimit.  When Analysis Services’s memory usage reaches this point, it starts to clean up memory much more aggressively.  The default value is 80%.

Points to Note

• If SQL Server’s minimum server memory is set too low, other applications can snatch so much memory that SQL Server will become unsresponsive.
• If SQL Server’s maximum server memory is set too high, it can take more memory than the operating system and other applications can afford to give it – making other applications or the whole server unresponsive.

So – set these values after careful consideration.  Make sure you leave enough memory available for the server to accept inbound remote desktop connections in case of emergencies.  Make sure you leave enough memory available for any other services that run on the machine.  On the other hand, make sure you set the minimum memory high enough that SQL Server will continue to perform adequately under all the expected load you’re going to put on it.

On a dedicated SQL server, it’s common for minimum and maximum to be set to the same value – generally almost all the server’s total physical memory leaving just enough for the OS to stay upright when SQL’s under really heavy load.  But if the box is shared, you’ll probably want to have different values here – a minimum which is the least you can work with and a maximum which the server can grab if it really needs to.

• If Analysis Services’s LowMemoryLimit is set too low, it will start clearing down memory before it needs to.  This will be a performance penalty.
• If Analysis Services’s TotalMemoryLimit is set too low, it will start very aggressively clearing memory before it needs to .  This is a very serious performance penalty.
• If Analysis Services’s TotalMemoryLimit is set too high, it will fail to clear down memory leading to unresponsiveness in the server or other applications.  Analysis Services doesn’t take any notice of the memory required by other applications on the machine, it just happily keeps on grabbing more memory within these limits.
• If the values are set too close together, SSAS doesn’t spend enough time gently disposing memory before it has to start aggressively clearing it.

An Example

Too illustrate, imagine a server with 16 gigbytes of memory, running SQL and Analysis Services, wanting to allocate memory relatively equally between them.  To be absolutely certain not all memory got used you could set:

• SQL Server Minimum to 6 gigabytes and maximum to 8 gigabytes.  So SQL can take half the total memory if it needs to and it’s available, but it can release a couple of gigbytes if required.
• Analysis Services total memory to 60% (9.6 gigabytes) and low memory to 45%.

This ensures the maximum amount of total memory that could be used is 15.6 gigabytes, even when SQL and SSAS are doing their hardest work.  Analysis Services has a 15% (2.4 gigabyte) usage band whereby it will try to clear memory gently before it’s forced to get more aggressive.  Of course, 0.4 gigabytes isn’t a lot to leave for the OS to keep running so I’d be keeping a very careful eye on what was going on if I were stressing the server.

Configuring Memory in SQL Server

Having been frequently confused by all the options in boot.ini and SQL Server for tuning the memory usage, I gathered together all the resources I could find, did my best to understand them, and came up with this flowchart which explains the various choices that lead to the right combination of settings.  I *think* this is all correct, but I would definitely like input from anyone that has greater insight into the mysteries of locked pages, AWE, /3GB and /PAE …

SQL Server Memory Configuration Flowchart

(Click the image for the full size version.)

Forcing a table lock in SQL Server

A client wanted to debug their application, there seemed to be an issue that caused incorrect records being inserted by an automated process in some cases.  It was eventually tracked down to a locked table causing a time-out in the application, but how could we prove this by locking the table instantly at any point, then unlocking it?

Quite easily, after a minute’s thought.  Begin a transaction, execute a statement including the TABLOCKX hint, then don’t commit the transaction.  So:

begin transaction;

select * from Sales with (tablockx, holdlock) where OrderID = 1;

Then you can unlock the table with a ROLLBACK.

ALTERing Objects in a Replicated Database

There’s a limit to what changes you can make to tables and other objects in a database which is being replicated.  Fortunately the restrictions aren’t that great; here’s a quick reference to what is and is not supported.

– Supported syntax
alter table Test1 alter column RowValue varchar(20) — Changing column definition
alter table Test1 add NewColumn int null — Adding a nullable column to the end of the table
alter table Test1 drop column NewColumn — Dropping a column
alter table Test1 add NewColumn int not null default 0 — Adding a non-nullable column with a default
alter table Test1 drop constraint DF__Test1__NewColumn__5DCAEF64 — Dropping a default constraint
alter table Test1 drop column NewColumn — Dropping a column after dropping its default constraint
alter view vwTest1 as select RowID from Test1 — Changing a view definition
alter procedure uspTest1 as select RowID, RowValue from Test1 — Changing a stored procedure definition
– alter function and alter trigger are also supported

– Unsupported syntax
alter table Test1 add NewColumn int not null — Adding a not-null column with no default
alter table Test1 add NewColumn int identity — Adding an identity column (Also an identity column cannot be dropped)
alter table Test1 drop constraint PK__Test1__FFEE74517F60ED59 — Dropping a primary key
alter table Test1 add constraint PK_Test1 primary key clustered (RowID) — Adding a primary key

Some things to remember:

• Writing your own ALTER statement will allow you to do more than generating a script from the table designer (which often likes to create a temp table, move the data into it, drop the table then rename the temp).
• Always execute your code on the publisher, not the subscriber!