Post reply

SSAS Multidimensional Configuration

  • June 18, 2024 at 11:48 am

    #4429382

    Hi Everybody,

    I could really use some help. My company uses SQL Server Analysis Services 2019. We have a 75gb multi-dimensional cube. 95% of our report data is sourced from the cube. We also have several teams that refresh their excel workbook connections. For the past couple of months, we have been plagued with CPU & Memory issues. We have changed our server several times over the past 2-3 years due to the issues, but I feel that (this is an uneducated assumption) our server size might not be the issue, but how we have SSAS configured.

    On days with high utilization of the reports / excel refreshes we could have 125-300 users at a time.

    Server has 128 GB, Windows Server 2019

    SQL Server Enterprise installed

    Now, last week we did make some changes to Memory Allocation.

    • HardMemory is now 120259084288 (default value was 0)
    • LowMemory is now 96636764160 (default value was 65)
    • TotalMemory is now 120259084288 (default value was 80)

    SSAS 2019 Configuration

    Making the memory changes has helped, but we are usually hitting 100% CPU on Monday's and Tuesday's because of the usage. This is obviously effecting all users as well as causing our report servers to have high CPU.

    Are there any other recommended changes that we should make to resolve this issue or do we really need to move to the next server size?

  • June 18, 2024 at 4:21 pm

    #4429458

    I don't know what settings would help, but have you looked at the aggregation design recently? If many users are running the same reports regularly it may be possible to create aggregations to support them. It might be worth trying the usage based optimization wizard.

  • June 18, 2024 at 8:19 pm

    #4429518

    We have about 1k calculated measures. Which means tons of aggregations. I'll do some research on usage based optimization wizards. Thank you Ed!

  • June 19, 2024 at 10:48 am

    #4429665

    I wouldn't change any configuration properties unless I have a better understanding of the root cause. If the CPU is high, then it is likely due to either a refresh or a bad query.

    I would recommend that you enable the Query Log or use extended events to get an idea of what is running at the time you are experiencing the resource issues. Identify the root cause(s) of the resource utilization spike first, and then act accordingly.

  • June 19, 2024 at 11:46 am

    #4429682

    Thank you for the advice Martin! We will do that.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply