Administration and Optimization: SQL Server Profiler for Analysis Services Queries - Page 3
April 9, 2007
Execute a Trace to Monitor Analysis Services Events
We will run the trace we have established, while simultaneously generating events for it to capture, in the steps that follow. First, we will open SQL Server Management Studio for easy access in generating example events.
Prepare SQL Server Management Studio to Generate Events
1. Leaving the SQL Server Profiler in its current state, click the Start button on the PC, once again.
2. Select Microsoft SQL Server 2005 within the Program group of the menu.
3. Click SQL Server Management Studio, as depicted in Illustration 7.
The Connect to Server dialog appears, as before, after the brief Management Studio splash screen.
4. Select Analysis Services in the Server type selector.
5. Type / select the server name (server name / instance, if appropriate) in the Server name selector.
6. Supply authentication information, as required in your own environment.
The Connect to Server dialog appears similar to that we saw earlier when connecting via SQL Server Profiler.
7. Click the Connect button to connect with the specified Analysis Services server.
The SQL Server Management Studio opens.
8. In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Databases folder (click the + sign to its immediate left), appearing underneath the Analysis Server within which we are working.
The Databases folder opens, exposing the detected Analysis Services database(s).
NOTE: The Analysis Services databases that appear will depend, of course, upon the activities that have taken place in your own environment. For purposes of this practice session, the Adventure Works DW database must be present. If this is not the case, consult the Books Online for the installation / connection procedures, and complete these procedures before continuing.
9. Expand the Adventure Works DW database.
The Database expands, exposing the folders for the various objects housed within the Analysis Services database, as shown in Illustration 8.
10. Expand the Cubes folder within the Adventure Works DW database.
The Cubes folder opens. Adventure Works is the sample cube with which we will be conducting our practice exercises. The cubes appear similar to those depicted in Illustration 9.
Execute the Trace
We are now ready to execute our trace, and then to takes actions in SQL Server Management Studio to examine as events within the SQL Server Profiler.
11. Leaving the SQL Server Management Studio in its current state, return to SQL Server Profiler.
12. Click the Run button in the bottom right corner of the Trace Properties dialog.
The trace viewer appears immediately, issuing primarily Server State Discover End and Begin, as well as Notification, events, assuming an idle Analysis Server. Lets monitor the action when we kick off a cube processing event.
13. Shift back to Management Studio and right-click the Adventure Works cube.
14. Select Process from the context menu that appears, as shown in Illustration 10.
15. Click OK on the Process Cube Adventure Works dialog that appears next.
The Process Progress viewer appears, as processing begins.
16. Leaving the cube processing, if necessary, shift immediately back to the SQL Server Profiler window.
Events began to log within the trace viewer almost immediately. Among them, we see several new EventClasses appear, all related to cube processing. Many of the EventClasses are of the Progress Report type, each of which generates an EventSubclass that details various steps surrounding the preparation and execution off the processing cycle.
17. Allow the cube to finish processing, if it has not already done so. As soon as the Process Progress viewer indicates that processing is complete (via the Process succeeded message in the Status bar of the viewer), click Close to dismiss the Process Progress viewer, as depicted in Illustration 11.
18. Click the Pause Selected Trace button within the trace viewer toolbar, as shown in Illustration 12.
For purposes of our practice exercise, lets filter our trace to display a couple of EventClasses, simply to make our steps a little more convenient. Because dimension processing involves an initial ExecuteSQL step, we will make this one of our filter criteria (we will thus achieve the tandem objectives of looking at an example each of monitoring cube processing and query processing). (We will later examine an example of an event surrounding an MDX query, as well, to illustrate our capabilities with regard to monitoring performance of our MDX queries.)