SSAS – Synchronisation performance
I’ve always thought of SSAS synchronisation as a clever file mirroring utility built into SSAS and i have never considered the technology as bringing any performance gains to the table. So, its a good job I like to revisit areas…. 🙂
I decided to compare the performance of robocopy and SSAS Synchronisation between 2 Windows 2003 servers running SSAS 2008 SP1 CU7 with 1gb network links. For the robocopy of the data directory i used the SQLCat Robocopy Script. The results are shown below.
SSAS Sync | Robocopy | |
Full DB (138gb) | 58 min | 96 min |
Single Partition (10gb) | 3 min 37s | 7 min 46s |
The Full DB copy is as it says, no data is present so synchronise the lot.
Single Partition is where after the full db is processed i then process a single partition (and yes the partition is around 10gb) and then run the synchronisation.
The results left me gobsmacked! At first i simply could not believe that the SSAS sync could copy the data that much faster than robocopy but i repeated my tests a number of times and it does!
Yes, SSAS Synchronisation is between 100% and 50% faster than Robocopy… I’ve got to say that when Microsoft did the rewrite for 2008 they certainly did a fine job!
Before I finish up with this blog entry I must say there are always considerations as to whether synchronisation is appropriate for you and i will be posting soon about all the different options and the considerations.