How to set PreAllocate parameter in SQL Server Analysis Services and something more

I suppose you think that changing a parameter in SQL Server Analysis Services engine is quite easy stuff. And probably you are right, because I also thought so before I had to face some reconfiguration request. So straight to the point – changing this parameter is possible ONLY by manually editing the msmdsrv.ini file (the SSAS configuration file in the Config folder). After opening the file for editing just browse to the <Memory> section and you will find it on the bottom of it:

<PreAllocate>0</PreAllocate>

If you set a value between 0 and 100 then this will be converted to percentage of the Total Physical Memory that SSAS is configured to use. If the number is higher than 100 then it will be treated as absolute value in bytes. This setting is actually serving two purposes – warming up the SSAS memory and tacking an issue with memory allocation for SSAS on Windows Server 2003 (several reference articles can be found here and here).

So far so good. But this setting was quite new to me so before reaching the point of changing it I had to go through several different articles and whitepapers. Such are:

SQL Server 2008 White Paper: Analysis Services Performance Guide

SQL Server 2005 Analysis Services (SSAS) Server Properties

Based on the entire task for setting this and some other parameters I learned quite a few things for SSAS configuration. Some of them are:

1. There are two types of settings on SSAS instance level – basic and advanced. Advanced, just as the ones in SQL Server Engine, are hidden by default. In order to display and edit them you should check the box “Show Advanced (All) Properties” on the Analysis Services Properties window.

image

2. You can set absolute values for memory parameters – values between 0 and 100 are treated as percentage of the total physical memory available to SSAS (not the server itself). If the value is greater than 100 – then it is treated as an absolute value in bytes.

Advertisements

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