First published on MSDN on Jan 16, 2007
Over the past several months PSS has received several reports of processing for cubes in SQL Server 2005 Analysis Services hanging or becoming unbearably slow. Most of the reports have involved high end 64-bit servers with 4 or more processors and 8GB or more RAM. In many instances the processing can be “jump started” again by connecting and issuing a simple MDX query.
This problem turns out to be due to a change in the way Analysis Services configuration is handled in SQL Server 2005 vs SQL Server 2000 and a well meaning attempt to prevent Analysis Services from creating more threads than necessary.
With SQL Server 2000 the Process and Worker thread pool sizes were dynamically adjusted at install time based on the number of logical processors detected. The Worker thread pool was set to a maximum value of 30 times the number of processors, while the Process thread pool was set to a maximum value of 40 times the number of processors. For more details see the following
Registry Entries for Microsoft SQL Server 2000 Analysis Services
. With SQL Server 2005 Analysis Services the configuration settings for the thread pools sizes are set to static values of 10 for the Worker thread pool and 64 for the Process thread pool (see
SQL Server 2005 Analysis Services (SSAS) Server Properties
).
During early beta builds of SQL Server 2005 Analysis Services the thread pool sizes were treated as a hard limit. It was discovered that this behavior could result in server hangs when additional threads above the thread pool limit were needed due to job dependencies, child job operations, multiple parallel processing operations, or query activity. As a compromise the thread pool management was changed to allow thread creation even if the current number of threads exceeded the target thread pool size; however the thread creation was delayed 30 seconds in order to allow the server time to “catch-up” in the event that the request was simply caused by a short term spike in activity.
The default settings are fine for most SQL Server 2005 Analysis Services installations because the Memory Quota Manager will limit the number of parallel operations based on available memory. However, on high end servers with large amounts of RAM available the Memory Quota Manager is likely to allow a much higher number of parallel operations to occur, resulting in the number of process or query threads quickly exceeding the default thread pool sizes. The delay encountered for each thread created above the thread pool size can quickly accumulate and give the appearance that processing has slowed or completely stalled.
The following symptoms are indicators that you are encountering this behavior:
1. Low CPU for the msmdsrv.exe process.
2. Low disk activity for the msmdsrv.exe process.
3. The sum of the values of the Threads:Processing Pool Idle Threads, Threads:Processing Pool Busy Threads, and Threads:Processing Pool Job Queue Length performance counters is greater than the configured max threads for the processing thread pool.
If a system is not exhibiting a combination of symptoms 1 and 2 or symptoms 1 and 3 then you are likely not encountering this problem and the thread pool sizes should not be adjusted. However, if you do believe you are encountering the behavior described here you should try the following approach to resolving the behavior:
· Sum the values of the Processing Pool Idle Threads, Processing Pool Busy Threads, and Processing Pool Job Queue Length performance counters and then add 10.
· Increase the processing thread pool max threads setting to the value calculated above. The server should check this setting within 30 seconds, detect the change and allow the additional needed threads to be created.
Although PSS has not received any reports of delayed query results which can be tied to the query thread pool size, it is easy to see how the same behavior could result in delays answering queries. Because queries use thread from both the Query (formula engine) thread pool and the process (storage engine) thread pool the query delays could be a result of either a shortage of query or process threads. The same pool adjustment mechanism can be applied for the query thread pool as those for the process thread pool.
Establishing and maintaining a baseline set of Perfmon logs for processing and query performance is key to properly tuning your SQL Server 2005 Analysis Services server. Investing the time to do this can be a tremendous help in quickly identifying potential causes for changes in system performance in the future.
Wayne Robertson
Escalation Engineer
SQL Server Analysis Services
