locked
Cube is running slow RRS feed

  • Question

  • Hi Experts,

    We are working on a support project on SSAS, the cube we are supporting is on production from a very long time more than 6years. It was running fine till last week but now the queries have become very slow and if we run large MDX it just keeps on running. We are not able to analyze what change has caused this issue as our team has not done any change on the cube. Kindly let us know if anyone has any suggestions as this is very urgent as this is on production environment.

    Thanks in Advance

    --Noor

    Monday, May 21, 2012 5:32 AM

Answers

All replies

  • You'll need to find out what has changed on the environment.

    Are there any new application/services that are competing for memory/disk io resources?

    Regards,

    Jason


    MCITP BI Developer - MCTS SQL Server (http://bichopsuey.wordpress.com/)

    Monday, May 21, 2012 7:38 AM
  • Hello NoorBi,

    To troubleshoot this issue, we typically consider using SQL Server Profiler and Windows performance monitor. In addtition, the factors causing performance issue of the cube process are not only cube design but also process option choice. Here are some articles for your reference, please see:
    SQL Server 2008 R2 Analysis Services Operations Guide(including how to use SQL Server profiler and Window performance monitor)
    http://msdn.microsoft.com/en-us/library/hh226085.aspx
    Analysis service 2008 performance guide including cube design tips.: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=17303
    Analysis services process best pratices: http://msdn.microsoft.com/en-us/library/cc966525.aspx

    Regards,
    Bin Long


    Bin Long

    TechNet Community Support

    • Marked as answer by Elvis Long Tuesday, June 5, 2012 5:30 AM
    Thursday, May 31, 2012 11:19 AM
  • previous one week back which changes you made.

    otherwise any server problem.please check it once.


    indu

    Thursday, May 31, 2012 1:09 PM
  • Try a full reprocess.

    I've seen two instances over the years where a SSAS database just becomes very slow for no apparent reason - both in processing and querying. The only way I've been able to resolve is to perform a full reprocess on the entire database.

    If it's not possible to run a full process I'd clear indexes then rebuild them.

    Thursday, May 31, 2012 10:49 PM
  • There are so many things to look at here. Since there have been no changes to the cube and the cube was fast before,  I'd focus on things outside of the cube.

    Is it just the cube, or is the server slow? A quick check may be to restart the server and see if that fixes. I've seen crappy console apps without garbage collection and CLRs referencing unapproved namespaces both cause memory leaks that brought servers to their knees.

    Have you ran performance counters and compared to the baseline? Is there a baseline?

    What does memory look like for both the system and Analysis Services when things are slow? What are the high and low limits for AS?

    What other services are running on the server? If there are relational databases on the same server, have any of them grown or experienced increased processing volume? Is the relational engine starving the system of ram? High and Low limits for sql server?

    Greg Galloway had an excellent pre-con session at SQL Rally Dallas titled 99 tips on tuning and enhancing analysis services. Many of his tips involved understanding how to interpret AS dmvs, aggregation hits, perfmon counters, and utilizing profiler traces. Unfortunately, there were 99 tips (8 hours well spent), so I can't begin to list them here. He did such a great job, that I'd recommend anyone facing AS performance problems attend.


    • Edited by buddylee17 Friday, June 1, 2012 2:38 AM
    Friday, June 1, 2012 2:32 AM