Max Memory setting for SQL Server Analysis Services

Leave a comment (0) Go to comments

Can we set a MAX Memory setting for Microsoft SQL Server Analysis Services like Microsoft SQL Server Database Engine ?

How can control memory for SQL Server Analysis Services ?

Yes, we can control memory utilization of SQL Server Analysis Services. But this is also true that we cannot directly restrict the SQL Server analysis engine to specific memory and processor resources

Memory settings Microsoft SSAS  is different and we control SSAS memory by specifying "percentage of total memory" parameters which control how Analysis Services works with memory.

In SSAS (SQL Server Analysis Services) we don;t have direct option for setting a absolute size (Minimum Server Memory & Maximum Server Memory parameters as SQL Server Database Engine) for controlling memory.

Microsoft SQL Server Analysis Services (SSAS) has primarily following memory parameters.

Max Memory setting for SQL Server Analysis Services SSAS memeory setting SSAS max memeory setting SQL Server Analysis Services memeory settings


  • 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%.
  • Memory\TotalMemoryLimit - When Analysis Services’s memory usage reaches this point, it starts to clean up memory much more aggressively.  The default value is 80%.
  • Memory\HardMemoryLimit - The maximum targeted memory for Analysis Services. If memory use exceeds this value, the server will free all shrinkable memory, and if memory use still exceeds the limit after freeing all shrinkable memory, the server will kill all running commands. The default value, zero (0), means the HardMemoryLimit will be set to a midway value between TotalMemoryLimit and the total physical memory of the system; if the physical memory of the system is larger than the virtual address space of the process, then virtual address space will be used instead to calculate HardMemoryLimit.

Note: – if the value is any memory parameter is above 100, then it’s become an absolute value in bytes. So incase you want to hard cord the memory settings update specify the value in bytes.

For More Details check Microsoft BOL.

There are some other’s parameters are also available to manage SSAS memory.

    <Memory>
        <MemoryHeapType>1</MemoryHeapType>
        <HeapTypeForObjects>1</HeapTypeForObjects>
        <HardMemoryLimit>0</HardMemoryLimit>
        <TotalMemoryLimit>80</TotalMemoryLimit>
        <LowMemoryLimit>65</LowMemoryLimit>
        <MidMemoryPrice>10</MidMemoryPrice>
        <HighMemoryPrice>1000</HighMemoryPrice>
        <VirtualMemoryLimit>80</VirtualMemoryLimit>
        <SessionMemoryLimit>50</SessionMemoryLimit>
        <MinimumAllocatedMemory>25</MinimumAllocatedMemory>
        <WaitCountIfHighMemory>10</WaitCountIfHighMemory>
        <DefaultPagesCountToReuse>2</DefaultPagesCountToReuse>
        <HandleIA64AlignmentFaults>0</HandleIA64AlignmentFaults>
        <PreAllocate>0</PreAllocate>
        <PagePoolRestrictNumaNode>0</PagePoolRestrictNumaNode>
        <VertiPaqMemoryLimit>60</VertiPaqMemoryLimit>  # — This is new parameter in Denali
        <VertiPaqPagingPolicy>0</VertiPaqPagingPolicy> # — This is new parameter in Denali
    </Memory>

Let’s discuss an example where a server has 128 gigbytes of memory, running SQL and Analysis Services, and now we wanted to allocate memory relatively equally between them.  To be absolutely certain not all memory got used you could set:

  • SQL Server Minimum to 48 gigabytes and maximum to 64 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 50% (64 gigabytes) and low memory to 40% (51.2 gigabyte).

This ensures the maximum amount of total memory that could be used is 115.2 gigabytes, even when SQL and SSAS are fully utilizes all resources and working at MAX.  Analysis Services has a 10% (12.8 gigabyte) usage band whereby it will try to clear memory gently before it’s forced to get more aggressive. 

This Configuration, still left out 12+ GB of RAM for other OS and other resources.

How to Specify SQL Server Analysis Services MAX Memory Settings ?

You can change the memory settings, either my

  1. Making Changes in SSAS Configuration file named "msmdsrv.ini", this file is located in directory C:\Program Files\Microsoft SQL Server \MSQL\OLAP\Config\
  2. We can also change these setting using SQL Server Management Studio (SSMS).

Changing SSAS Services Settings using SSMS

1). Open SSMS and connect to Analysis Services

Max Memory setting for SQL Server Analysis Services SSAS memeory setting SSAS max memeory setting SQL Server Analysis Services memeory settings

2) Once you are connected, Right Click on Analysis Services Server and select properties.

Max Memory setting for SQL Server Analysis Services SSAS memeory setting SSAS max memeory setting SQL Server Analysis Services memeory settings   

3). In properties window, you have following options, where you can change these.

Max Memory setting for SQL Server Analysis Services SSAS memeory setting SSAS max memeory setting SQL Server Analysis Services memeory settings

Note : These changes doesn’t require restart, but in case you currently over utilizing the resources then definitely a a restart will be required.

EOF - Max Memory setting for SQL Server Analysis Services, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.