The cost of SSAS metadata management
This post will explain how we found ourselves in a situation where when processing a partition the metadata checks took 50 times longer than the actual partition and how to identify what time penalty you are incurring for the metadata checks.
The system i was investigating had around 80 cubes with around 100 partitions per cube all hosted in one database and we would add 1 partition per cube per day. The partitions in each cube were small but were taking far to long to process and as each day passed they would get a bit slower and inch their way that bit closer to breaking our SLA’s.
We know that too many partitions can hurt performance and this is documented in a SQLCat blog where it states “Partitions have some metadata and when the number exceeds several thousand, the cost of managing this metadata becomes apparent”.
To our detriment we had always believed that this would apply at the cube level since a partition can not be related to a partition in another cube. Wrong! When you process a partition SSAS will check for dependencies across every partition in the database. In our case this translated to checking for dependencies across > 8000 partitions and a processing delay of > 4 minutes!!! The actual processing itself only took a few seconds.
So, how do you work out the cost of metadata checking?
Start a profiler session and include command begin and progress reports. The first event after command begin will be Build Processing Schedule. The time difference between these two events is the “Cost of SSAS Metadata management”. In the screen shot below you can see the cost is 3 minutes 9 seconds.
So, now you have identified this overhead how do you address it? Unfortunately there is no magic bullet so the workarounds are below.
- Reduce the number of partitions in the database through merging partitions or splitting into multiple databases.
- You could move to SQL 2008 which does perform slightly better and reduces check time by about 25%. Still not good enough really……. (and yes that means you have a problem in 2005 & 2008)
- Wait until Microsoft address the issue. Well its not currently going to be addressed for 2005 or 2008 but we might see it in 2008 R2.
I hope you find this helpful and if you are being hurt by this issue then let Microsoft know because then we might see it addressed sooner. :)
Update
Since this problem is by design i have opened an improvement suggestion on Connect which you can find here. If you agree that Microsoft should implement a design change to improve performance please vote for for the connect item.