SSAS MSMDSRV.INI Settings for Multi-User Environments

SSAS MSMDSRV.INI Settings for Multi-User Environments

change text size: A A A

• Comments (0)

posted 7/2/2013  by MMilligan -  Views: [4869] 

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. 

I have been setting memory\lowmemoryLimit to 45, and Memory\TotalMemoryLimit to 60. 

You can also take advantage of shared memory when the Analysis Services instance is on the same server as the relational data warehouse instance.  More information here:  Analysis Services: 2 Quick Tweaks to Speed Up Cube Processing

-------------------------------------------------------------------------------

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