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:
DBJ_AS2k5_Trace
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.
» 
Comment and Contribute
