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 2

By William Pearson

Procedure: Monitor Analysis Services Events with SQL Server Profiler

SQL Server Profiler can be accessed from the Start menu of the server / client we use to interact with MSSQL Server. (This assumes that the appropriate tools are installed on the respective machine(s), and that the user is appropriately assigned to the Analysis Server server role). It can also be started from within SQL Server Enterprise Manager. Those of us who have used SQL Server Profiler to monitor or troubleshoot MSSQL Server will find connecting to Analysis Services to be as straightforward as the process of connecting to MSSQL Server; those of us who are familiar with SQL Server Management Studio, but new to SQL Server Profiler, will find the connection process intuitive, as well.

Connect to the Analysis Server with SQL Server Profiler

We will open SQL Server Profiler, and connect to our Analysis Server, by taking the following steps:

1.  Click the Start button on the PC.

2.  Select Programs -> Microsoft SQL Server 2005 -> Performance Tools -> SQL Server Profiler from the cascading menus, as shown in Illustration 1.

Illustration 1: Opening SQL Server Profiler from the Start Button

3.  Select File -> New Trace ... within the newly opened SQL Server Profiler, as depicted in Illustration 2.

Illustration 2: Select File -> New Trace ... within SQL Server Profiler

The Connect to Server dialog appears.

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 shown in Illustration 3.

Illustration 3: Connecting to the Server ...

7.  Click the Connect button to connect with the specified Analysis Services server.

The Trace Properties dialog opens.

Define a Trace to Monitor Processing and Queries in Analysis Services

We touched upon the purpose of a trace in our introduction to SQL Server Profiler earlier, stating that a trace exists to capture data based upon events that we select. We use the Trace Properties dialog to select those events, and to set the properties that govern the trace. Among these properties, we have the capability to:

  • select a pre-existing template for trace creation;
  • save the trace to a file (along with specifying some of the file’s properties);
  • save the trace to a table (along with specifying maximum number of rows);
  • enable a trace stop time.

Once we create a trace, we can save it as a template, and then run it as a trace anytime we need to collect data surrounding the selected events. The generated trace data can thus be used within current analysis efforts, or it can be replayed at a later time, for deferred or repeated analysis.

1.  In the Trace Properties dialog that appears next, type the following into the Trace name box on the General tab:


2.  Leave the Use the template selector at its default of Standard (default).

3.  Place a check in the empty checkbox to the left of Save to file, to open a Save As dialog.

4.  Navigate to a convenient location, and type the following into the File name selector, at the bottom of the Save As dialog:

DBJ_ AS2k5_Trace

The Save As dialog appears similar to that depicted in Illustration 4.

Illustration 4: Saving the Trace File ...

5.  Click Save to save the trace and to dismiss the dialog.

We are returned to the General tab of the Trace Properties dialog, which appears similar to that shown in Illustration 5.

Illustration 5: The General Tab of the Trace Properties Dialog, with Our Input

6.  Click the Events Selection tab atop the dialog.

The Events Selection tab of the Trace Properties dialog affords us the opportunity to specify the events we wish to capture within the trace. Here we see the events selected for the Standard template we have chosen. We can modify our events selection, as well as the event columns that we wish to trace. Clicking on a specific event or column results in the display of a short description in the respective sections below the selection table.

For more information, the various columns are described in detail in the Books Online and other references. Many contain information to assist in the identification of various objects within the context of a respective event, as well as details about connections, the timing and duration of the event, and so forth.

We will leave all settings on the Events Selection tab at default for purposes of our practice session. The Events Selection tab of the Trace Properties dialog appears similar to that partially shown in Illustration 6.

Illustration 6: The Events Selection of the Trace Properties Dialog, with Our Input

We will get some hands-on exposure to executing a trace in the section that follows.

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