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.

Monitoring Analysis Services Events Using SQL Profiler

Chapters 13 and 14 provide detailed coverage of how to use SQL Profiler, so we'll focus on what is important about using this tool for monitoring your Analysis Services events. The capabilities of using SQL Profiler for Analysis Services have been 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 to review or replay it later to get a better understanding of what happened. Furthermore, 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 impacting performance.

Our main focus will be 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 event categories that will be useful include:

• Command events provide insight into the actual types of statements issued to perform actions.

• Discovery events detail requests for metadata about server objects including the Discovery Server State events (such as open connections).

• Error and Warnings events

• Notification events

• Query events

Because of all the detail that a trace returns, you should use the Column Filter button to display only the activities sent to a specific Analysis Services database.

Creating Traces for Replay

Traces are important in that they allow you to determine various elements of status information for Analysis Services through certain counters. You start Performance Monitor by either selecting Administrative Tools from the Control Panel or by typing PerfMon at the command prompt. Two types of counters are used within Performance Monitor. The predefined counters measure statistics for our server and process performance, while user-defined counters are used to analyze events that may occur.

Just as in the monitoring of other SQL services, the CPU usage, memory usage, and disk IO rate are important counters to review in order to evaluate how Analysis Services is performing.

We'll now give you a better idea of how to configure these traces for replaying queries submitted to our Analysis Services server. Start the trace by opening SQL Profiler and selecting FileNew trace. When prompted, specify the Analysis Services server to connect to and configure trace properties.

To profile user queries, you have to ensure that the SQL Profiler is capturing the Audit Login event class, the Query Begin event class, and the Query End event class (see Figure 7-13). The information detailing who was running the query and other session-specific information will be able to be determined because you are including the Audit Login event class. The Query Begin and End event classes simply permit understanding of what queries were submitted by reviewing the text of the query along with any parameters that would have been used during query processing.

Figure 7-13

After you've set up the trace, you can start the selected trace. Now you can browse a cube within Management Studio to generate user activity involving the submission of queries to Analysis Services. The result in Profiler is a detailing of Events along with the Text Data recording the activities. As an example, in Management Studio, you can add the Customer and Date dimensions while also requesting that Order Count and Average Sales Amount be displayed. This activity all gets recorded with the Profiler, then showing the Query Begin as the EventClass and a Select statement recorded in the TextData (see Figure 7-14).

Figure 7-14

Using Flight Recorder for After the Fact Analysis

Often, administrators are disappointed when we cannot seem to find the cause of a particular problem. Mostly, we are stymied when we cannot reproduce reported problems. These situations arise as we attempt to recreate what happened to determine how things could have been handled differently to avoid the reported problem. Using the Flight Recorder, you may be able to replay the problem conditions that led to the reported problems. This Flight Recorder operates similar to a tape recorder; it captures the Analysis Services server activity during runtime without requiring a trace. In fact, each time the server is restarted, a new trace file is automatically started. Additionally, the recorder is automatically enabled and can be configured using the Analysis Services Server Properties.

Here's how you can use the trace file created by the Flight Recorder to replay server activity:

1. Open SQL Server profiler and open the trace file created by the Flight Recorder, by default located at C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Log and named FlightRecorderCurrent.trc.

2. Select Start Replay on the toolbar.

3. On the Connect To Server dialog, enter the server name and authentication information.

4. On the Replay Configuration dialog, you can set up the desired playback features such as replaying only statements issued to the server within a given timeframe (see Figures 7-15 and 7-16).

Figure 7-15

Figure 7-16

This replay is rather useful, as Analysis Services will begin to run the statements captured in the trace. As we all agree, factors such as number of open connections and even the number of sessions that existed at the time of the original problem are important to consider when troubleshooting problems. When you replay the traces made by Flight Recorder, these factors are simulated on your behalf.