Below are the steps I often use to identify the bottle necks.
1) Gather information from the user, to see which report user normally has to wait - either opening the report or perform filter actions.
2) Setup SQL Server Profiler trace to monitor the activity. Monitor the following events, Audit Begin, Audit End, Discover Begin, Discover End, Query Begin, and Query End.
3) On the Analyzer side, open the problem report, you should see Audit, Discover, then Query events.
4) Notice the time when you click on the Execute link and the first time you see the Audit event. If you see a long delay then the issue is likely related to connecting or finding the target SSAS server, for example resolving the Server Name. If this is the case change the server name to IP Address to see if that resolves the issue.
5) If you notice a long delay in the Discover Events then the issue is gathering information from the target SSAS. While opening the report Analyzer tries to identify all the databases in all the data sources so it could be that one of the data sources it tries to connect has a problem causing long delays. This is only done at the opening of the report. If the performance problem is later where user performs filter actions then this is not the cause.
6) After the initial Audit and Discover events you should see many Query Begin and Query End events (however since there might be many events during opening of the report so we might want to catch/test the Query events differently later in step 7). Pay attention to the duration between Query Begin and End event to see if there are any long delays - the time between Query Begin and Query End are the time that took SSAS to process the query. The slowness could be many small Query Begin and End events that add up to be a long time, or it there could be one or two events that is taking a long time to process, if this is the case normally you will see the trace stopped at a Query Begin event but no Query End returned, this means that SSAS is processing the query. 80-90% of performance issues are related to SSAS process.
7) Sometimes the slowness may came from Analyzer itself (10-20%). To test this it's best to identify the sheet, the table or the action that caused the slowness in the report. Monitor the SQL Profiler at the same time after you click the mouse performing the slow action (for example if it takes a long time to filter then perform a filter action on the Analyzer side then watch what happen in the SQL Profiler). Observe the time after you click the mouse and the time you see the Query Begin shows up on the Profiler, the time between you click the mouse and the time Query Begin shows up is the time Analyzer took to render the query (plus the time to transmit the query to the SSAS). In some rare events Analyzer may generates super long query that causes slowness.
8) Finally monitor the time after between Query End and the time Analyzer renders the report. Sometimes due to large amount of data returned back to Analyzer that a) requires a network time to transmit the data to Analyzer, and b) Analyzer will take some times to render the report.
By following the steps above, step 4 identifies network problem (name resolving issue), step 5 identifies SSAS issue (permission), step 6 identifies SSAS performance issue (may be caused by calculation, fletching too many data, etc.), step 7 identifies Analyzer performance issue, and step 8 identifies network transmission issue and time to process the data.
0 Comments