On a previous post I wrote about how the number of databases in an SSAS instance can make an impact on the overall performance of the instance. In this post I want to discuss another, more “explainable” property that you can set in order to improve cube processing performance.
Trying to optimize processing time of our databases I set up a performance monitor trace to identify if we are having any resource bottlenecks that we need to address. Looking at the “Physical Disk: Disk Writes Bytes/sec” counter I noticed that it recorded a fairly small numbers most of the times (between 1MB and 500KB of data written during a “dimension update” or partition processing” events). This is obviously way below the amount of data that can be written to disk (we use a very advance storage system that can cope with a much more intense disk write profile).
I also was looking at the SSAS counters “Threads:Processing pool busy threads”, “Threads: Processing pool idle threads”, “Threads:Processing pool job queue length” and “Threads:Processing pool job rate”. These counters show how the threads allocated by SSAS to perform the processing operations are behaving. I noticed that:
1) There was no job queue at all – which means all processing threads never waited – which is good.
2) The total number of threads (busy and idle) never exceeded 60.
In SSAS there is an (advanced) server property which tells the SSAS processing engine how many threads it can create for a processing operation, this is the Threadpool\Process\MaxThreads property. Its default value is 64 and that correlates with the number of threads I observed during processing. I increased this value to 128 and indeed the number of threads created during processing increased to approximately 120, but even better, I noticed that the disk write rate increased up to 15 MB/sec. There was still no job queue reading so I figured I have succeeded in this modification. Needless to say processing time decreased as well by 40%. I’ve increased this property to 256 but at this stage job queue was starting to increase from zero up to 15 and the number of threads created did not increase over 140 and I was not able to identify an increase in disk write rate as well. So, for me, 128 was the magic number for optimal processing performance.
If you are trying to optimize processing performance of your SSAS databases and are running your SSAS instance on a multiple CPU server, look to to the Threadpool\Process\MaxThreads property to see if this needs to be modified.
(This is post number 21 for week number 25 of my SQL blog challenge)