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