It is also why adding RAM to a server stops being advantageous after a point. See my blog Why SSAS Doesn't Cache The Entire Cube? This is the most significant performance limitation I have come across with large Multidimensional cubes and the reason I strongly recommend solid state drives for very large cubes.
This is particularly noticeable with cubes larger than 4GB and smaller than the available memory. It also relies on the Windows file cache, which doesn't appear to offer much more caching ability. The caching in multidimensional service has several components, but the aggregation cache doesn't appear to exceed 2GB. The Multidimensional service will not hold a large cube in memory even though there is plenty of RAM to do so. I had to shut down Tabular when testing Mulitdimensional, so that it had a fair go at the RAM.įor all the effort Microsoft has put into creating the Tabular model, I am left questioning why they didn't enhance Multidimensional's memory management to use memory when available. Something else you should be aware of, Tabular service will hold onto memory even though there are no queries active and memory is short, which could degrade other services on the same server. This is a bugbear of mine, that the Multidimensional service doesn't hold onto more memory, when it is available. The Multidimensional service was only holding 280MB and had a peak working set of 4.9GB (peak during processing). You can see from the Task Manager, above, that at idle, the Tabular service was holding most of the 8GB of memory (peaking at 10.7GB during processing), leaving very little for any other service (SQL Server was squashed down to 350MB). The resulting databases, on disk, were not too dissimilar in size, Tabular=7.5GB, Multidimensional=5.4GB. Both models had only one partition, and I was interested to see that some Tabular queries were able to consume all 4 CPUs concurrently. Note, my server did not have a solid state drive, which would have assisted the Multidimensional model. But it still returned results in less than 2 seconds for almost all random queries. It was a little slower for queries on data that hadn't been queried recently (answer not in cache). The Multidimensional model tended to be just as fast when the submitted query required similar data to a recently executed query (answer in cache). It was also noticeably faster at distinct count (calculated) queries.
However, it would be fair to say that the Tabular model returned random results faster, which, was largely due to it having all data in memory. Both models were able to satisfy my random queries quickly. The Multidimensional service was performing IO and its memory usage was fluctuating with the queries and managing its cache. Now, when I ran queries on the two models, Tabular was able to answer all queries with negligible IO. In processing both models completed in less than 60 minutes, with the Tabular model about 20% faster. I find this a bit annoying, but, it might be good feedback to a less experienced designer. I created equivalent Tabular and Multidimensional cubes with the same fact measures and 11 dimensions.ĭuring the design process, Tabular model imports and massages the data. I had 8GB which translated to a fact table of about 450 Million rows from my WebLogs database. Since Tabular is entirely in-memory, I had to experiment with a cube that didn't require more memory than I had available in my 圆4 server. To test the memory aspects of Tabular versus Multidimensional, I created a large-ish cube in both. So why would you use Tabular? There might be several answers, but the one I am most interested in is Tabular models are held entirely in memory, providing exciting performance potential.
Tabular is simpler than Multidimensional, it does not support features such as write-back, complex calculations, named sets, many to many relationships and very large cubes.
0 kommentar(er)
