I recently found a problem blocking processing AS Cube. At first, we tend to think that the processing just takes longer than usual. But when it is 12 it turns to an operation that normally takes 1 hour, there's still enough to ask questions.
By observing the performance counters, we find that in my case, the body is still trying to calculate the indexes of my scores, and it's long it lasts:
Green curve: number of lines read
Blue curve: number of lines "processées"
Green curve: number of lines written
yellow curve: Number of partitions in which the indexes and aggregations are calculus course (right axis)
And in the same time at the CPU consumption, we find that larger thing happens, which is absolutely not normal because usually, the steps of indexing / aggregation are consuming the most CPU resources during the processing :
Green curve: consolidated CPU%
This situation occurs mainly on large server configurations in 64 bits. An article by Wayne Robertson explains here perfectly why and how.
It's actually a problem of allocation of threads to AS when their number exceeds the property value ThreadPool Process Max. There is an empirical calculation of the value to be imposed on this property, and is also explained in the article above. I'll still recall here:
Sum the values of the following performance counters (values recorded during the hang of processing):
- Processing Pool Idle Threads
- Processing Pool Busy Threads
- Processing Pool Job Queue Length
Note: attention both to changing this parameter. Other properties of AS are taken into account in order to maintain system balance. Info are available in the very good performance guide white paper you will find here (see page 65: Executing Processing jobs).
And to go further, an entire chapter is devoted to managing threads in the book SQL Server 2008 Analysis Services Unleashed (Chapter 28, p 521).
