As I have blogged before http://richardlees.blogspot.com.au/2011/12/why-doesnt-ssas-cache-entire-cube.html large multidimensional cubes are not retained in memory even though there is plenty of RAM and msmdsrv is using less than LowMemoryLimit. Msmdsrv tends to hold about 2GB of aggregation cache, although this isn't a limit and it may be a result of the cache cleaner being too aggressive. Whatever the reason, the problem is that msmdsrv won't hold data in cache even though there is plenty of RAM. I have raised this as an issue with Microsoft, but I need your support to get this changed in the product. Please go to https://connect.microsoft.com/SQLServer/feedback/details/760107/multidimensional-cube-is-not-retained-in-memory-even-though-there-is-plenty-of-ram and vote for this enhancement.
This enhancement will mean that if you have a 20GB cube on a dedicated 100GB server, msmdsrv will retain the entire cube in memory and no further IO will be required. As the product is now, with a high query load, the IO throughput can be measured in terabytes during a busy day.
By the way, in SQL Server 2012 we have Tabular models, which will store the entire cube in memory, but these models don't have the functionality that multidimensional cubes have, and they only work if the entire cube can fit in memory.
Thank you for helping make SQL Server a better product.
No comments:
Post a Comment