4 05 2012
SSAS 2008 R2 Processing Performance Tips
I haven’t posted on this for a while as I’ve been neck-deep in a fairly decent sized Business Intelligence project in SQL Server Analysis Services 2008 R2, but have subsequently learnt an enormous amount with regards to performance. So I thought I’d share a list of the biggest things I’ve learnt from various places (mostly Books Online and TechNet) to help make things that bit quicker when you have a large OLAP cube to process.
Reduce the number of attributes where ever possible in your dimensions
This is particularly important if you have Intermediate Dimensions used for Many to Many relationships. Because these tables can grow enormous relative to the size of the other dimensions, you want to minimize the number of columns that SSAS needs to worry about. If you are using Intermediate Dimensions, remove all but the keys used in the joins, and don’t try to join to other tables. The Intermediate Dimension shouldn’t be visible to the end user anyway (use Perspectives).
Remove unnecessary attributes from your dimensions where possible.
Yes, I’ve listed this twice, as it really does help performance. It can also reduce the final footprint of the database on the filesystem!
Script out the process job.
Use two separate jobs, one to issue a ProcessData command and the other to do ProcessIndexes. By default, a ProcessFull is issued (unless you do an Update), which does both of these, but some other (seemingly unnecessary stuff) and adds about 10% to the overall processing time. If you try to do this with a single job with a Parallel task it’ll probably error out (trying to process indexes on data that doesn’t exist yet).
As well as the above, put the two jobs into a SQL Job as two separate steps.
This lets you execute these on the server and log off your session, leaving the server precious resource to do its thing.
You’ll be doing this as you do the above two steps anyway. In SQL Server Management Studio open up the Template Explorer (from the View menu), switch to Analysis Services (by clicking the Cube at the top of the panel) and play with the different XMLA templates available (careful where you execute the DROP command 😉 )
If at all possible, have the source relational database on separate physical volumes to where the analysis services databases will end up.
Not only will this help processing performance, but if your SQL Database instance is used for other things, queries against your cubes shouldn’t aversely affect the performance of that SQL instance. Even better, have it on a separate server and free up those resources! Just make sure you have a VERY good network connection between the two (to speed up reads. Try and get at least 50-100MB/sec transfer rates between the boxes).
Add more RAM
Lots of RAM. Max out the box. Seriously. As the process happens, it queries SQL Server for the relational data, and this works faster the more RAM it has (buffers fill faster than they can be flushed, it seems), and Analysis Services will grind to a halt if it doesn’t have enough RAM. An easy way to spot if SSAS is low on memory during a process is to spin up Resource Monitor from Task Manager (under Performance Tab), go to the Disk tab, and see what files are currently being requested. If you see minimal CPU usage and many .khstore files being trickled (at 300kb/sec) off disks, SSAS doesn’t have enough RAM (it’s doing what’s known as multipass processing, which in my experience is very, very slow – we’ve seen times go from 12 hours down to 90mins just because we upped a box form 32GB of RAM to 64GB).
Make sure the Lower Memory Limit is set properly
And by properly, keep it around 15-20 below what the Max Limit is set. When SSAS runs after a restart it needs to allocate memory as it needs it, I’m somewhat almost convinced things run faster if it’s already preallocated itself everything when it first starts up. That might just be me though. Just make sure it has enough RAM regardless 😛
Restart the SSAS Service before you process
You can either do this or just flush the cache of everything in the instance before you kick off the Process. What you want is for SSAS to have as much RAM as possible for the Process tasks as it can, and for that precious memory not to be taken up with useless* cached queries and aggregations it has hogging up all that juicy memory.
Have a separate physical box just for processing changes…
… and backup/restore the newly processed cubes onto your staging/production server. This alleviates performance issues on the live cubes to keep users happy. Also this’ll prevent their horrendously complex queries swallowing up the RAM you need to reduce the chance of your Process going into multipass mode. You’ll need a fat wallet for this though. But you’re on a BI project, so you probably knew this fact already, right? 🙂
*everything out of hours is useless, right? And you are doing processing out of hours, right? 😉
This can probably go on and on, but these are the big ones that jumped out at me. I’ll probably add a ‘Part Two’ at some point with more tips when they come to mind.