Share via

Why is a perfectly normal cube starting to perform dreadfully suddenly?

Mamet, Eric (GfK) 21 Reputation points
2021-07-30T15:32:27.767+00:00

We have an on premise SSAS cube of about 2.4Gbs giving us very good performance.

Our users query our cubes through Excel Spreadsheet pivot tables.
Once in a while they may want to change filters or <Refresh All> and this is not a problem.
The spreadsheets are reasonably big but refreshing takes around 30 seconds, which we are happy with.

During a good <Refresh All>, I can see my server CPU jumping to ~90% and about 65% of it used by the SSAS service.
But this lasts for about 30 seconds. No problem.

Now, sometimes things start not working at all with exactly the same spreadsheet.
In that case, during the <Refresh All>, I can see the Server CPU hitting almost 100% for a very long time, with about 75% of it being the SSAS Process.
And this lasts indefinitely until I restart the SSAS Service.

I tried detaching / re-attaching the particular cube.
When I do the CPU goes down but as soon as the user tries to refresh again, my CPU jumps back to the roof...

I cannot see any memory pressure.

I "may" have managed to reproduce such a problem by re-processing the cube (full processing on each table) while trying to refresh the spreadsheet at the same time.
Processing the cube finished (in 40mns) but the spreadsheet seemed stuck and then I even struggled to stop the SSAS Service.

Any idea what I could try / look into?

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
0 comments
Answer accepted by question author
  1. Eric Mamet 365 116 Reputation points
    2021-08-05T07:42:08.06+00:00

    HI CarrinWu

    I watched the video you suggested which was very interesting.
    This led me to https://www.sqlbi.com/articles/optimizing-memory-settings-in-analysis-services

    Checking our settings, I found that some important memory settings had been modified quite drastically from the defaults and I have reset them.

    120660-image.png

    I am now waiting for feedback from my users...

    I know I should/could be more pro-active and monitor using extended attributes (and/or profiler) but I have many other things urgent to do so I'll just wait and see for the time being.

    Many thanks for your help, this led me to these interesting articles.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. CarrinWu-MSFT 6,891 Reputation points
    2021-08-02T03:03:21.657+00:00

    Hi @Mamet, Eric (GfK) ,

    Welcome to Microsoft Q&A!

    Sorry for the late reply. Please use SQL Server Profiler to trace queries and commands. And also you could use Dynamic Management Views (DMVs) to return information about model objects, server operations, and server health: 

    select * from $system.discover_sessions;  
select * from $system.discover_commands;  
select * from $system.discover_connections;

    Best regards,
    Carrin

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  2. Mamet, Eric (GfK) 21 Reputation points
    2021-08-02T12:45:02.39+00:00

    Ok...
    I tried to collect evidence this time.
    I used profiler with a default trace and Dax Studio to run the DMVs and export to Excel.

    I took 3 sets of files based on your suggestions

    • 08:45 the cube was non responding and my Excel refresh hanging
    • Cancelled the Excel refresh
    • 08:52 I had restarted my SSAS service, my profiler but not restarted my Excel refresh
    • 08:54 I had then restarted my Excel refresh
    • 08:55 My Excel refresh had finished normally and I stopped the second trace

    Best Regards

    Eric

    [119778-2021-08-02-0845-standard-tracetrc.txt][1][119779-2021-08-02-0845-commandscsv.txt][2]119912-2021-08-02-0845-connectionscsv.txt119780-2021-08-02-0845-sessionscsv.txt119882-2021-08-02-0852-commandscsv.txt119857-2021-08-02-0852-connectionscsv.txt119921-2021-08-02-0852-sessionscsv.txt119816-2021-08-02-0854-commandscsv.txt119817-2021-08-02-0854-connectionscsv.txt119808-2021-08-02-0854-sessionscsv.txt
    [119729-2021-08-02-0855-standard-tracetrc.txt][11] [2]: /api/attachments/119779-2021-08-02-0845-commandscsv.txt?platform=QnA

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.