Analysis Services Preallocate Memory Setting – Insight
Q: What is Preallocate Setting?
Answers: Preaallocate Setting specifies a certain percentage of memory be allocated to Analysis Services when the service starts (memory preallocation). This configuration setting was introduced because the Microsoft Windows Server 2003 operating system did not scale well with many small virtual memory allocations due to memory fragmentation and inefficiencies in retrieving information already located in memory.
Q: What is generally the best setting for PreAllocate?
To determine this value, monitor the peak value for the Process: Private Bytes counter found in the Performance Monitor tool for the msmdsrv instance. The peak value establishes the maximum value for memory preallocation that you need to set. When you cannot set the preallocation value this high, set it as high as possible without starving other processes and the operating system.
If you use memory preallocation with SQL Server 2008 (or SQL Server 2005), use a value that is low enough to ensure that sufficient memory remains for other processes on the computer (avoiding paging) and high enough for Analysis Services (use the peak value for the Process: Private Bytes counter for the msmdsrv instance to establish this value).
Ensure that you will configure TotalMemoryLimit greater than Preallocate else Analysis Server will go in aggressive mode.
If the memory used by Analysis Services is above the value set in the Memory\TotalMemoryLimit property, the cleaner cleans until the memory used by Analysis Services reaches the Memory\TotalMemoryLimit. When the memory used by Analysis Services exceeds the Memory\TotalMemoryLimit, the server goes into an aggressive mode where it cleans everything that it can. If the memory used is mostly non-shrinkable (more information on non-shrinkable memory is included in the next section), and cannot be purged, Analysis Services detects that the cleaner was
unable to clean much. If it is in this aggressive mode of cleaning, it tries to cancel active requests. When this point is reached, you may see poor query performance, out of memory errors in the event log, and slow connection times.
Rule of thumb
Value should be lower Memory Low .
Secondly, Other processes on same server shouldn’t starve for memory which means if you have multiple processes running on same server for example SQL Engine or Reporting Services or Integration Services then use a value that is low enough to ensure that sufficient memory remains for other processes on the computer (avoiding paging) and high enough for Analysis Services.
Q: Are there any other memory settings that could conflict with PreAllocate? No.
Q: How do you know if PreAllocate is working?
Test1:
Windows 2003 SP3
Ram 16 GB
SQL 2008 Analysis Services
No activity is going on Analysis Services.
Default Setting:
<Memory>
<TotalMemoryLimit>80</TotalMemoryLimit>
<LowMemoryLimit>65</LowMemoryLimit>
<PreAllocate>0</PreAllocate>
</Memory>
Perfmon Counters (Average Columns)
Memory Limit High 13420851 KB (Around 80% of 16 Gb)
Memory Limit Low 10785914 KB (Around 65% of 16 GB)
Memroy USage Limit 63704 KB (Hardly 637 MB - Actual used by Analysis Services)
Changed Configuration:
<Memory>
<TotalMemoryLimit>80</TotalMemoryLimit>
<LowMemoryLimit>65</LowMemoryLimit>
<PreAllocate>30</PreAllocate>
</Memory>
Perfmon Counters (Average Columns)
Memory Limit High 13420851 KB (Around 80% of 16 Gb)
Memory Limit Low 10785914 KB (Around 65% of 16 GB)
Memory Usage Limit 4194304 KB (Around 30% of 16 GB)
This concludes we are getting right value as we have configured for Preallocate
memory setting.
Karan Gulati
Support Engineer, Microsoft SQL Server