Professional Microsoft® SQL Server® 2008 Administration

7.4. Analysis Services Performance Monitoring and Tuning

Successful use of Analysis Services requires continual monitoring of how user queries and other processes are performing and making the required adjustments to improve their performance. The main tools for performing these tasks include the SQL Profiler, performance counters, and the Flight Recorder.

7.4.1. Monitoring Analysis Services Events Using SQL Profiler

Chapters 13 and 14 provide detailed coverage of how to use SQL Profiler, so here we focus on what is important about using this tool for monitoring your Analysis Services events. The capabilities related to using SQL Profiler for Analysis Services were vastly improved in the 2005 release and are now quite useful for this purpose. With SQL Server Profiler, you can review what the server is doing during processing and query resolution. Especially important is the ability to record the data generated by profiling to either a database table or file in order to review or replay it later to get a better understanding of what happened. You can also now either step through the events that were recorded or replay them as they originally occurred. Last, you can place the events side by side with the performance counters to spot trends affecting performance.

Our main focus here is tracing the Analysis Services server activity and investigating the performance of the MDX queries submitted to the server in order to process user requests for information.

The useful event categories ...