Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 9, 2007

Administration and Optimization: SQL Server Profiler for Analysis Services Queries - Page 3

By William Pearson

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.

Illustration 7: Opening SQL Server Management Studio

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.

Illustration 8: Exposing the Object Folders in the Database ...

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.

Illustration 9: The Cubes Appear ...

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. Let’s 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.

Illustration 10: Select Process for the Adventure Works Cube ...

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.

Illustration 11: Click Close to Dismiss the Process Progress Viewer When Complete ...

18.  Click the Pause Selected Trace button within the trace viewer toolbar, as shown in Illustration 12.

Illustration 12: Select the Pause Selected Trace Button

For purposes of our practice exercise, let’s 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.)

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM