Administration and Optimization: SQL Server Profiler for Analysis Services Queries - Page 2
April 9, 2007
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.
3. Select File -> New Trace ... within the newly opened SQL Server Profiler, as depicted in Illustration 2.
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.
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:
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:
The Save As dialog appears similar to that depicted in Illustration 4.
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.
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.
We will get some hands-on exposure to executing a trace in the section that follows.