Practical Business Intelligence

June 4, 2010

Memory Settings on SQL Server and Analysis Services

Filed under: Analysis Services, Configuration, Configuration, SQL Server — Duncan Sutcliffe @ 3:54 pm

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.

3 Comments »

  1. Here are this and some other articles on SSAS Memory:

    http://ssas-wiki.com/w/Articles#Memory

    Comment by Sam Kane — March 22, 2011 @ 7:37 pm

  2. So you only have to remember one single master password or select the key file to unlock
    the whole database. Sony Cybershot DSC S3000 reviews have made it a foremost choice for
    millions of mobile users. Waiting until
    the park is about to close is one way to get some privacy, as
    is visiting the park during Disneyland’s less busy times.

    Comment by online checking accounts — May 31, 2013 @ 9:53 pm

  3. Exceptional web site the following! Furthermore your web blog masses way up quickly! Just what variety do you think you’re the application of? Should i get those affiliate marketer web page link to your host? I drive this site crammed as quick as your own house ; )

    Comment by website security — September 4, 2013 @ 9:46 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Shocking Blue Green Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: