none
Performance Issue In Dundas Reporting Tool because of SQL & SSIS ? RRS feed

  • Question

  • Hi,

    We are facing performance issue in Dundas Reporting Tool, 

    From back end side, We created index based on time , product and location key,

    We did Cube Partition also for year by year ,

    We are using only summary sales data.

    If, more than 10 or 10 user access the Dundas reporting means, SSAS (analysis service is not responding) , after only thing we are doing is restrat the analysis server .

    Is there any more performance  related things we need to do in back end(SQL & SSIs)

    (OR)

     

    Any idea to Fix the issue in SSAS ?

    SSAS Cache clearing  will solve the isssue in SSAS?  

    (OR)

    Better ideas to Fix this Performance Issue is there ? ? 

     


    Abhisek K S
    Tuesday, October 18, 2011 5:38 AM

Answers

All replies

  • I would first check memory just to see if there is a memory problem there. Limiting memory usage on relational side (if on the same box) and memory properties on SSAS side would help then.

    Together with finding out if query could be served with less memory consumption (optimized) ofcourse.

     

    MC

     

    Tuesday, October 18, 2011 6:41 PM
  • Hi MC,

    In the SSAS Memory  properties ,

    1.Memory\LowerMemoryLimit Value is 75,75,75

    2.Memory\TotalMemoryLimit Value is 80,80,80.

    We need to change anything in the properties?

    But,hardware memories are 16 GB RAM, around D drive 300 GB , totally 500 GB, DoublewayDualcore processer is there.. Hardware side is more than enough i think so ?

    we cant increase hardware's memories. 

    what else need to change or update the properties in SSAS? ..

     


    Abhisek K S
    Wednesday, October 19, 2011 7:17 AM
  • Im not certain it is due to the memory, im just saying for starters, you should check how memory behaves when 10 clients use reporting. Then you can see if web is consuming too much mem (or not), SSAS...

     

    MC

    Wednesday, October 19, 2011 5:41 PM
  • Hi Abhisek K S,

    It seems a performance issue of Analysis Services, you might have to check many things such as  the Analysis Services has multiple processors and is processing in parallel, the IO subsytem for Analysis Services is fast enough, make sure you are not saturating the IO or CPU or memory of the AS server during processing and so on.

    So please try to use SQL Server Profiler to monitor events generated by an instance of Microsoft SQL Server Analysis Services. By using SQL Server Profiler, you can do the following:
    • Monitor the performance of an instance of Analysis Services.
    • Debug Multidimensional Expressions (MDX) statements.
    • Identify MDX statements that run slowly.
    • Test MDX statements in the development phase of a project by stepping through statements to confirm that the code works as expected.
    • Troubleshoot problems in Analysis Services by capturing events on a production system and replaying them on a test system. This approach is useful for testing or debugging purposes and lets users continue to use the production system without interference.
    • Audit and review activity that occurred on an instance of Analysis Services. A security administrator can review any one of the audited events. This includes the success or failure of a login try and the success or failure of permissions in accessing statements and objects.
    • Display data about the captured events to the screen, or capture and save data about each event to a file or SQL Server table for future analysis or playback. When you replay data, you can rerun the saved events as they originally occurred, either in real time or step by step.
    How To: Use SQL Profiler, please reference http://msdn.microsoft.com/en-us/library/ff650699.aspx

    More information about performance guide of Analysis Services, please reference as below:
    1. the performance guide document mentioned above and tune accordingly. If it still doesn't  process fast enough then let us know what you have tried and how performance has changed or not changed: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=17303
    2. Analysis Services Query Performance Top 10 Best Practices: http://msdn.microsoft.com/en-us/library/cc966527.aspx

    Hope it is helpful.


    Regards, Amber zhang
    Thursday, October 20, 2011 2:27 AM
    Moderator
  • Hi MC,

    Msmdsrv.exe (SSAS analysis services) is consuming memory upto 9 GB RAM (8,852,252 KB)and 50 % CPU usuage , while 10 clients accessing the reporting tool.

    Web  or network services consuming upto 1.5 GB .

    So, SSAS is consuming more memory.

    Also , now i started monitoring SQL SERVER Profiler for SSAS.

    How to fix the SSAS memory consuming issue?

     


    Abhisek K S
    Thursday, October 20, 2011 6:18 AM
  • Hi Abhisek K S,
    >>How to fix the SSAS memory consuming issue?
    what’s the version of SQL Server you are using?

    If you are using  SQL Server 2008 Analysis Services (SSAS 2008) or  SQL Server 2008 R2 Analysis Services (SSAS 2008 R2) and you created a local cube from a server-based cube. This issue occurs because SSAS 2008 or SSAS 2008 R2 caches all Multidimensional Expressions (MDX) sub-cube statements in memory. Therefore, the memory usage is high when you run the MDX query to create the local cube file.
    The solutions are:
    For SQL Server 2008 R2, please try to install Cumulative Update 6.
    For SQL Server 2008, please try to install Cumulative Update 11 for SQL Server 2008 Service Pack 1.
    For SQL Server 2008 Service Pack 2, please try to install Cumulative Update 2.
    More inforamtion, please refer this KB:http://support.microsoft.com/kb/2422081

    Meanwhile, you can try to increase the LowMemoryLimit parameter of the server.
    Hope it is helpful.


    Regards, Amber zhang
    Tuesday, October 25, 2011 4:12 AM
    Moderator
  • HI Amber zhang,

    We are using SQL server 2005 version ..We cant upgrade to SQL server 2008 now.. Please give the performance tuning process for 2005 ..



    Abhisek K S
    Thursday, October 27, 2011 4:51 AM
  • Hi Abhisek K S,

    >>Please give the performance tuning process for 2005

    Thank you for your update.  Based on your descriptions, I suggest you can try to monitor performance.

    For the performance running process for SQL Server 2005, please refer the session Tuning Processing Performance in Microsoft SQL Server 2005 Analysis Services Performance Guide: http://blogs.msdn.com/b/sqlperf/archive/2007/02/12/analysis-services-2005-performance-guide.aspx

    For more information, please refer Tune memory usage and Tune processor usage in Analysis Services Query Performance Top 10 Best Practices: http://sqlcat.com/sqlcat/b/top10lists/archive/2007/09/13/analysis-services-query-performance-top-10-best-practices.aspx

    Hope it is helpful.

    Regards, Amber zhang
    Thursday, October 27, 2011 8:08 AM
    Moderator
  • HI Amber zhang,
    We read all article links and performance guide. 
    Still we are lagging somewhere.
    Based on the performance guide , we did indexes , partitions, aggregations  etc.....

    Abhisek K S
    Thursday, October 27, 2011 12:47 PM
  • Hi Abhisek K S,

    Thank you for your reply. Regarding to the articles I provided, which are related to the performance issue of Analysis Services.
    If you need further troubleshooting, please link to SQL Server Analysis Services Forum, which might be much more helpful.
    Regards, Amber zhang
    • Marked as answer by Stephanie Lv Tuesday, November 1, 2011 8:14 AM
    • Unmarked as answer by Abhisek K S Tuesday, November 1, 2011 10:37 AM
    Friday, October 28, 2011 7:24 AM
    Moderator
  • Anything else needed here?

    Thanks!


    Ed Price (a.k.a User Ed), SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, January 23, 2013 1:41 AM
    Owner