Log-In to post
See Also: Main_Page - Database Administration - Analysis Services - Optimizing Performance of MSAS - Optimizing Querying Performance
Memory utilization model has changed dramatically from MSAS 2000 to MSAS 2005. In early releases MSAS loaded all dimension members into memory when the service was started. Furthermore, if you used dimension level security, then a replica of the dimension was loaded into memory for each security role. With this type of architecture the size of dimensions was limited to the memory available on the server. It wasn't uncommon to see Analysis Service fail to start because it didn't have sufficient memory. With MSAS 2005, dimension members are loaded into memory on demand, and security is implemented through bitmap indexes. Role based security has much smaller memory footprint with MSAS 2005. When you start MSAS 2005 service, it only loads metadata objects into memory. The more databases, dimensions, cubes, partitions and other objects you have, the more memory will be acquired during startup.
With 32-bit MSAS (any version) you can only use 2GB of memory, unless you add the /3GB switch to the boot.ini file, in which case MSAS can use up to 3GB. For large implementations, 3GB of memory for loading all dimensions is not sufficient. When using 64-bit MSAS the amount of memory the software can use is only limited by the total amount of memory available on the server.
MSAS uses <LowMemoryLimit> and <TotalMemoryLimit> properties as the guidelines for memory use. However, if MSAS doesn't need much memory when there is no processing and no querying taking place, you can see MSAS utilizing less memory than the lower limit. Similarly, if during heavy processing or querying MSAS determines that it needs more memory than the <TotalMemoryLimit>, it will attempt to reserve additional memory.
MSAS uses a background thread referred to as the "cleaner thread" to continuously check current memory utilization and release the memory back to the operating system by removing objects from cache that are no longer being used. The more memory MSAS uses, the more aggressive the cleaner thread becomes. MSAS has shrinkable and non-shrinkable memory sections. For example, various caches are shrinkable memory holders and they can be "cleaned" when memory is needed for more important tasks; such as processing. The cleaner thread can only shrink the shrinkable memory holders. Non-shrinkable memory holders provide memory to various internal subsystems and cannot return memory until that subsystem releases it. If MSAS memory utilization is below <LowMemoryLimit>, the cleaner thread is inactive.
Disk resources can make a huge difference in processing performance. Disk resources can also become a bottleneck if the queries scan huge number of partition files. It is recommended to use RAID 5 or RAID 10 for disk arrays hosting MSAS data. When using MOLAP, Analysis Services reads data from the relational warehouse and writes it into multidimensional format. In addition, the Analysis Services data must be read in order to create bitmap indexes and aggregations. When processing numerous partitions the disk can easily become a bottleneck.
In addition to investing into a faster disk subsystem and purchasing additional controllers, you can take a few other steps to improve disk utilization. First, ensure that MSAS has sufficient memory for processing tasks. With sufficient memory resources MSAS should never generate temporary files for aggregating and indexing data. You can also create multiple paging files and spread them across multiple disks to spread disk operations. Exploit remote partitions to distribute the disk operations across multiple disk arrays. Finally, to further minimize the load on your disk subsystem you can turn off the Flight Recorder trace. Note however, that typically the benefit of having the Flight Recorder trace far outweighs the cost of additional disk operations.
MSAS uses processor thread pools for processing and querying operations. Querying thread pool is used for allocating worker threads that satisfy queries. One thread is used per each query. You can use <ThreadPool><Query><MinThreads> and <ThreadPool><Query><MaxThreads> properties to adjust minimum and maximum number of threads in the querying thread pool. If you have a multi-processor server and you wish to increase the number of concurrent queries that can be resolved in parallel you can adjust the <ThreadPool><Query><MaxThreads> property. It is recommended to set this value equal to twice the number of processors. For example, on an 8 processor server you should set this value to 16.
Processing thread pool is used for allocating worker threads for processing jobs. Processing thread pool is also used for retrieving data from the storage engine for satisfying query requests. You can tweak minimum and maximum number of threads in the processing thread pool by adjusting values of <ThreadPool><Process><MinThreads> and <ThreadPool><Query><MaxThreads> properties. If you have plenty of CPU resources you should consider bumping the number of maximum number of processing threads. The general recommendation is to set it to 10 times the number of processors. For example, on an 8 CPU machine you should set <ThreadPool><Query><MaxThreads> to 80. The default value of this property is 64 and you don't have to reduce it if you have fewer than 6 processors.
Another property that controls processor utilization is <CoordinatorExecutionMode>. The default value of this property is -4 which on an 8 CPU server means that up to 32 jobs, including processing and querying jobs, can execute in parallel. Negative values for this property dictate the total number of jobs that can be started per processor. Positive values indicate the absolute total number of jobs that can be started on the server. If you anticipate user queries while you're processing partitions, you should increase this value.