SSAS MSMDSRV.INI Settings for Multi-User Environments

Here are excerpts from the SSAS Operations Guide for SQL Server 2008 R2 regarding the recommended settings for a multi-user environment.  If you want more information you must read the whitepaper.

——————————————————————————-

2.3.2.4    Heap Settings, DataStorePageSize, and DataStoreHashPageSize
During query execution, Analysis Services generally touches a lot of data and performs many memory allocations. Analysis Services has historically relied on its own heap implementation to achieve the best performance. However, since Windows Server 2003, advances in the Windows Server operating system mean that memory can now be managed more efficiently by the operating system. This turns out to be especially true for multi-user scenarios. Because of this, servers that have many users should generally apply the following changes to the msmdsrv.ini file.

Setting Default Multi-user, faster heap
<MemoryHeapType> 1 2
<HeapTypeForObjects> 1 0

It is also possible to increase the page size that is used for managing the hash tables Analysis Services uses to look up values. Especially on modern hardware, we have seen the following change yield a significant increase in throughput during query execution.

Setting Default Bigger pages
<DataStorePageSize> 8192 65536
<DataStoreHashPageSize> 8192 65536

——————————————————————————-

2.3.2.5    Clean Up Idle Sessions
Client tools may not always clean up sessions opened on the server. The memory consumed by active sessions is non-shrinkable, and hence is not returned the Analysis Services or operating system process for other purposes. After a session has been idle for some time, Analysis Services considers the session expired and move the memory used to the shrinkable memory space. But the session still consumes memory until it is cleaned up.
Because idle sessions consume valuable memory, you may want to configure the server to be more aggressive about cleaning up idle sessions. There are some msmdsrv.ini settings that control how Analysis Services behaves with respect to idle sessions. Note that a value of zero for any of the following settings means that the sessions or connection is kept alive indefinitely.

Setting Description
<MinIdleSessionTimeOut> This is the minimum amount of time a session is allowed to be idle before Analysis Services considers it ready to destroy. Sessions are destroyed only if there is memory pressure.
<MaxIdleSessionTimeout> This is the time after which the server forcibly destroys the session, regardless of memory pressure.
<IdleOrphanSessionTimeout> This is the timeout that controls sessions that no longer have a connection associated with them. Examples of these are users running a query and then disconnecting from the server.
<IdleConnectionTimeout> This timeout controls how long a connection can be kept open and idle until Analysis Services destroys it. Note that if the connection has no active sessions, MaxIdleSessionTimeout eventually marks the session for cleaning and the connection is cleaned with it.

If your server is under memory pressure and has many users connected, but few executing queries, you should consider lowering MinIdleSessionTimeOut and IdleOrphanSessionTimeout to clean up idle sessions faster.

——————————————————————————-

2.4.3    Multi-User Process Pool Settings
In multi-user scenarios, long-running queries can starve other queries; specifically they can consume all available threads, blocking execution of other queries until the longer-running queries complete.
You can reduce the aggressiveness of how each coordinator job allocates threads per segment by modifying CoordinatorQueryBalancingFactor and CoordinatorQueryBoostPriorityLevel as follows.

Setting Default Multi-user nonblocking settings
CoordinatorQueryBalancingFactor -1 1
CoordinatorQueryBoostPriorityLeve 3 0

——————————————————————————-

If your SSAS instance shares a server with anything else you may also want to read my good buddy Jorge’s blog post: Setting Memory Limits

——————————————————————————-

Did I miss any?  Do you have any additional tips to share?  If so, please sound off in the comments.

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