Impact of Page Size on Ram Consumption for Day based Cubes

Document created by breuber Employee on Oct 10, 2018Last modified by breuber Employee on Oct 11, 2018
Version 2Show Document
  • View in full screen mode

About the Solution/Project: 

About the solution

This is not a real solution, more a hint/best practice for handling Databases with large Day based Cubes

 

Solution/Project info

Info:

Most times Users/Partners/Consultants don't think about possible optmization for DataBases with Day based cubes, so I just wanted to show the Impact of the "Page setting" for clear all cubes

 

The testcase was a ~10gb DB (on HDD) using ~60gb in RAM. On customer side there were also different other "bigger" cubes (with a similar structure, but I assumed that they perform in a similar way)

To reduce the testscenario I reduced the cube-number to 4 to see the impact of this settings (and every cube needed about 10 Minutes to read, so every testcase was about 1 hour):

 Engine RAM consumption after RestartDB Size on DiskEntRelSparseCube1Cube2Cube3Cube4TOTAL GBRam to Disk ratio
Testcast using Customer DB (reduced to 4 cubes)19.055.xxx K3,240,90,40,543,94,14,2185,555555556
after Clear all cubes with Page Size          
Small19.048.xxx K3,231,20,40,33,54,24,14,217,95,541795666
Medium06.302.xxx K2,341,30,30,41,40,90,61,162,564102564
Large04.920.xxx K2,341,20,60,110,60,50,44,41,88034188

 

Afterwards I also did some very short performance tests to see, if we also had an impact here. I only tested original DB and new DB with Page Size set to large.

 

 

 

 

Cube002 V001C002 V003
Testcast using Customer DB (reduced to 4 cubes)15s7s
Large12s

4s

As you can see there were also a great performance benefit cause a very simple report having only one cube and day in by row was deduced by 3sec, so the performance improvement (in my case) was for this short test case between 20% and 42.8%

Summary

For a large RAM Consumption (compared to DB size) it is allways useful to check, if maybe a different PageFile Setting would be helpful. But please keep in mind, that there is no "best" option, cause these settings are very connected to your data. So setting to to Large was very helpful in my case, but maybe it isn't the best approach for you.

Furthermore I would suggest every consultant working with DayBased cubes to identify the best/most fitting setting for their DB in an early Project stage (and review it regular), case a change at a later project stage might be hard to do

 

Last Words:

No I won't explain the background for Page Size (yet), cause it's almost the same as from every other system using a PageSize concept

8 people found this helpful

Attachments

    Outcomes