One topic that has come up occasionally over the years is whether there is any merit in using a RAM Disk with Analysis Services to improve performance. Certainly in the SQL Server world they seem to have some benefits (see for example Tony Rogerson’s post here: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx) but as far as I know no-one has ever done any testing for Analysis Services so when I found myself with a bit of free time and a customer who might potentially benefit I thought I’d do so myself.
The RAM Disk I used was RamDisk from Superspeed, which as far as I can tell is the same product that Tony used in his blog entry above. You can download a trial version here:
http://www.superspeed.com/ramdisk.php
http://www.superspeed.com/ramdisk.php
I then created a new RAM drive with 512Mb space on it, created a new directory and then made that new directory my local AS instance’s data directory. Incidentally it didn’t work if I tried to point AS to the root of the new drive – I don’t know whether there’s some kind of reason for this but I certainly wasted a long time finding this out. After that I was able to restore a (pretty small) backed up AS database so that the data was held on the RAM drive and compare performance with the same database on my local drive. And… I found that even on quite long-running queries(>20 seconds, where complex calculations forced multiple reads of the same partition) there was little or no difference between the two; perhaps the RAM drive was slightly faster but it wasn’t consistently so. Even though I was clearing the cache before each query I suspect that what I was really doing was comparing the performance of the RAM drive with the Windows file system cache, and in any case IO only represents a fraction of the amount of time taken by a query for AS on a properly tuned database.
That said, I could imagine that there would be scenarios where a RAM disk would have more of an impact: for example where you’re doing regular processing of a partition as with pro-active caching, or where you have a lot of memory available and you’re querying much larger partitions than I was. Some of Superspeed’s other products such as SuperCache and SuperVolume might be interesting to test out for these scenarios, and indeed they make some big claims for performance improvements on Cognos and SAS BI systems here:
http://www.superspeed.com/servers/business.php
http://www.superspeed.com/servers/business.php
…which makes me think further investigation could be worthwhile.
My thoughts (since I have some hardware experience):
1. For AS query performance, cache warm up you posted sometime back is a better solution than RAM disk. I guess, there is no workaround for good aggregation design and query cache-warm up design.
1a. Having said that, you might want to test RAM disk with 100/200 users (load testing) and see if that is any different – since they will have more IO activity than just 1 user.
2. We can still see some benefits from RAM disk during cube processing (by putting SQL Temp DB there). This will be useful when we do something like a "disntinct count" cube processing where SQL TempDB gets used a lot. In other words if your cube processing SQL query has an "order by" clause, it should work out better with TempDB on the RAM disk.
3. In order to improve AS computation speed – I found processors with high L2/l3 cache to be very helpful. "My" reasoning was that L2/L3 cache helps in reducing the context switching for the processing instructions and speeds up the ultimate MDX computations.