Administration and Optimization: SQL Server Profiler for Analysis Services Queries

Monday Apr 9th 2007 by William Pearson
Share:

Use SQL Server Profiler to “look behind the scenes” within Analysis Services 2005. BI Architect Bill Pearson leads a hands-on introduction to determining resource utilization effectiveness for both processing and query performance with profiling.

About the Series ...

This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services (“Analysis Services”), with each installment progressively presenting features and techniques designed to meet specific real-world needs. For more information on the series, please see my initial article, Creating Our First Cube. For the software components, samples and tools needed to complete the hands-on portion of this article see Usage-Based Optimization in Analysis Services 2005, another article within this series.

About the Administration and Optimization Articles ...

When it comes to optimization, query responsiveness is one of the most palpable system attributes to information consumers. Sluggish performance is one of the worst scenarios that can arise with regard to end-user satisfaction. From an administrative perspective, critical success factors for Analysis Services are the efficient processing of dimensions and cubes. The purpose of the Administration and Optimization subset of my Introduction to MSSQL Server Analysis Services series is to focus on techniques for optimizing resource utilization within Analysis Services in particular, and within the integrated Microsoft business intelligence solution (from the perspective of Analysis Services) in general.

Analysis Services 2005 witnesses the introduction of a collection of server configuration properties which we can readily access and tune to optimize resource usage throughout the diverse environments and workloads that occur within the enterprise, and the business environment in general. Many of these properties are set for us during installation, based upon hardware configurations, and the new, dynamic nature of these properties tends to go a considerable distance toward maintaining optimal settings. Situations still arise, however, where administrator intervention can assist in the optimization effort. Throughout the Administration and Optimization subseries, we will discuss some of the scenarios where “fine tuning” might prove advantageous, and we will point out tools and methods that Analysis Services 2005 makes available to help us to ascertain the effectiveness with which our local implementation is utilizing the resources that are available.

Because every environment is different, and because many variables interact to produce each unique backdrop, no one method or process that we discuss can truly be considered in a vacuum. The purpose of the Administration and Optimization subseries is to expose possibilities and considerations, not to simply declare any one tool or method to be a “silver bullet” in resolving suboptimal performance. Nor are these articles intended to provide instant relief from the more deep-rooted problems that arise from inadequate knowledge of, and experience with, Analysis Services 2005. Familiarity with the innovative and powerful structural underpinnings of the system, as well as the confident and correct use of the tools that are made available, are critical to truly optimizing the performance of Analysis Services.

Introduction

One of the tools that help us to see some of the “action behind the scenes” of Analysis Server is familiar to those of us who have worked with the Database Engine / RDBMS side of MSSQL Server. SQL Server Profiler, a graphical tool that allows system administrators to monitor events within an instance of Microsoft SQL Server, can assist us in monitoring events within Analysis Services, as well. Moreover, the Profiler can aid us in using the captured information to determine the effectiveness with which our local Analysis Services installation is utilizing resources, and to investigate the conditions that result in suboptimal performance. Using SQL Server Profiler to perform monitoring of this nature is a great way to examine query performance from both the SQL and MDX perspectives, both of which come into play within the combined processing and querying cycles through which an Analysis Services cube moves.

SQL Server Profiler is a particularly flexible monitoring tool, in that it allows us to focus our efforts upon the events in which we are interested. Another feature, the capability to capture events for later playback by oneself or others, can also be potentially useful while adding, perhaps, an extended degree of convenience. SQL Server Profiler allows us to create a template to define the data we wish to collect. In turn, we collect the data by executing a trace on the events defined within the template. While the trace is running, the specified event classes, together with the data columns containing event data descriptions, are displayed in SQL Server Profiler.

While the possible uses of SQL Server Profiler are legion, common tasks within which we can benefit from its employment include:

  • Isolating and diagnosing suboptimal queries;
  • Stepping through suboptimal / nonfunctioning queries to ascertain causes for the issues in evidence;
  • Capturing a series of MDX or SQL expressions that is known to have an other-than-optimal result, or which fails in some other way to meet its intended outcome, for systematic deconstruction / atomic examination;
  • Monitoring the performance of the Analysis Server to facilitate tuning of workloads (which tend to change over time, with changes in transaction volume, structural and population size changes, the introduction of new users and business requirements, and the like);
  • The correlation of performance counters to diagnose – and even to foresee – problems and challenges from various perspectives;
  • Support of audits and other security-oriented practices of the respective enterprise administrator(s).

In this article, we will gain some hands-on exposure to the use of SQL Server Profiler within an Analysis Services context. Our examination of this highly useful monitoring and troubleshooting tool will include:

  • A discussion surrounding the use of the of SQL Server Profiler as a performance optimization and monitoring tool for Analysis Services;
  • Performing a connection to the Analysis Server with SQL Server Profiler;
  • Defining a trace to monitor examples of processing and queries within Analysis Services;
  • Using a trace to monitor Analysis Services events, including steps to:
    • Execute the trace;
    • Add a filter to the trace;
    • Examine processing and query events within the Profiler trace;
  • Amplifying comments throughout regarding details surrounding the usage of the SQL Server Profiler trace.

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.

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.)

Add a Filter to the Trace

Filters can be useful for several purposes, chief among which is the minimization of overhead incurred as a part of tracing in general. We are instituting a filter here simply for convenience, but filters can be useful anywhere we need to limit the events that our traces collect.

1.  Click the Properties button within the trace viewer toolbar, as shown in Illustration 13.


Illustration 13: Select the Pause Selected Trace Button

2.  Click the Events Selection tab on the Trace Properties dialog, which appears next.

As we may have noticed in our initial visit to this tab, we can select events and event columns individually by placing a check in the appropriate checkbox in the Event rows, or in the specific intersects of the events with the various column types, within the matrix, to achieve just the filter criteria we need. Moreover, we can filter the individual columns based upon conditions that we can impose, as we shall see in the next step.

3.  Click the Column Filters button in the bottom right corner of the Events Selection tab, as depicted in Illustration 14.


Illustration 14: Click the Column Filters Button ...

4.  On the Edit Filter dialog that appears next, select EventSubclass.

5.  Expand Equals in the context sensitive selection list that appears to the right of be EventSubclass selection.

6.  Type the following integer into the input space that opens (containing the cursor) underneath Equals:

25

The Edit Filter dialog appears, with our input, as shown in Illustration 15.


Illustration 15: Our Filter Input ...

Here we are simply applying shorthand for 25 – ExecuteSQL (only integers are accepted), one of the many EventSubclasses that are captured.

7.  Click OK.

We are returned to the Events Selection tab of the Trace viewer, once again.

8.  Click Run, once again, to restart the trace.

9.  Re-process the cube, following the same steps as before.

Examine Processing and Query Events in the SQL Server Profiler Trace

We are returned to the Trace viewer, where, beginning with the events generated by our second cube processing cycle (and a “Progress Report Beginevent), we see only the 25 – ExecuteSQL EventSubclasses (in addition to the blank EventSubclasses) appear.

10.  Click the first EventSubclass Column containing “25 – ExecuteSQL” in the respective row, as depicted in Illustration 16.


Illustration 16: Select the First Instance of 25 – ExecuteSQL

The following SQL query appears in the lower half of the trace viewer:

SELECT [FactSalesSummary].[SalesOrderNumber] AS [FactSalesSummarySalesOrderNumber0_0],[FactSalesSummary].[OrderDateKey] AS 
 [FactSalesSummaryOrderDateKey0_1],[FactSalesSummary].[ShipDateKey] AS [FactSalesSummaryShipDateKey0_2],[FactSalesSummary].[DueDateKey] AS 
 [FactSalesSummaryDueDateKey0_3],[FactSalesSummary].[ProductKey] AS [FactSalesSummaryProductKey0_4],[FactSalesSummary].[PromotionKey] AS 
 [FactSalesSummaryPromotionKey0_5],[FactSalesSummary].[SalesTerritoryKey] AS 
 [FactSalesSummarySalesTerritoryKey0_6],[FactSalesSummary].[SalesChannel] AS [FactSalesSummarySalesChannel0_7],[FactSalesSummary].[CurrencyKey] AS 
 [FactSalesSummaryCurrencyKey0_8]
  FROM 
   (
  
SELECT     ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, NULL AS CustomerKey, EmployeeKey, PromotionKey, CurrencyKey, 
                      SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
                      DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, 
                      'Reseller' AS SalesChannel, CONVERT(CHAR(10), SalesOrderNumber) + 'Line ' + CONVERT(CHAR(4), SalesOrderLineNumber) AS SalesOrderDesc
FROM         FactResellerSales
WHERE       OrderDateKey >= '915' AND OrderDateKey <= '1280'
UNION
SELECT     ProductKey, OrderDateKey, DueDateKey, ShipDateKey, NULL AS ResellerKey, CustomerKey, NULL AS EmployeeKey, PromotionKey, CurrencyKey, 
                      SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
                      DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, 
                      'Internet' AS SalesChannel, CONVERT(CHAR(10), SalesOrderNumber) + 'Line ' + CONVERT(CHAR(4), SalesOrderLineNumber) AS SalesOrderDesc
FROM         FactInternetSales
WHERE       OrderDateKey >= '915' AND OrderDateKey <= '1280'
   )
   AS [FactSalesSummary]
  ORDER BY [FactSalesSummary].[SalesOrderNumber]
  ASC

We can therefore see the syntax of each individual SQL query that is executed as part of our cube build. This is helpful in the isolation of suboptimal queries and other diagnostics and performance tuning exercises. Now let's take a look at something conceptually similar, the examination of an MDX query, but this time based upon query processing versus our cube build. To do so, we will first remove the 25 – ExecuteSQL filter we installed in the last few steps.

11.  Pause the trace, as before.

12.  Click the Properties button within the trace viewer toolbar, once again.

13.  Click the Events Selection tab on the Trace Properties dialog, as we did before.

14.  Click the Column Filters button in the bottom right corner of the Events Selection tab, again as we did earlier

15.  Select EventSubclass on the Edit Filter dialog that appears next, once again.

16.  Expand Equals in the context sensitive selection list, as required.

17.  Remove the integer “25,” which we placed within the Equals input box before (using the Backspace key works fine).

18.  Click OK.

We are returned to the Events Selection tab of the Trace viewer, once again.

19.  Click Run, on the Events Selection tab of the Trace viewer, to which we are returned once again, to accept our filter modification and to restart the trace.

We arrive again at the Trace viewer.

20.  Shift back to SQL Server Management Studio.

21.  Close the Process Progress viewer, from the last cube processing cycle, as appropriate.

22.  Click the New Query button, just above the Registered Servers and Object Explorer panes, within the upper left hand corner of the Management Studio, as shown in Illustration 17.


Illustration 17: Click the Start Selected Trace Button

23.  Type (or cut and paste) the following MDX query into the Query pane of the newly opened tab:

-- ANSYS059  MDX Query Profiling Example
SELECT
   {[MEASURES].[Internet Order Quantity]} ON AXIS(0),
   NON EMPTY {[Customer].[Customer Geography].[Full Name].MEMBERS}
      ON AXIS (1)
FROM
 
   [Adventure Works]

The Query pane appears, with our input, as depicted in Illustration 18.


Illustration 18: The Query Pane with Our Input

24.  Click the Execute button within the toolbar, as shown in Illustration 19.


Illustration 19: Click the Execute Button to Run the Query ...

25.  Shift immediately back to the SQL Server Profiler window.

26.  Pause the trace once again, as we did earlier.

27.  Scroll to the bottom of the rows in the viewer (the most recent events logged are based upon the MDX query we executed above), and select the row containing the “Query BeginEventClass / 0 – MDXQueryEventSubClass combination, as depicted in Illustration 20.


Illustration 20: Select the Query Begin Event for the MDX Query ...

The MDX query appears in the lower half of the trace viewer, as follows:

SELECT
   {[MEASURES].[Internet Order Quantity]} ON AXIS(0),
   NON EMPTY {[Customer].[Customer Geography].[Full Name].MEMBERS}
      ON AXIS (1)
FROM
 
   [Adventure Works]
<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
     <Catalog>Adventure Works DW</Catalog>
<SspropInitAppName>Microsoft SQL Server Management Studio -    
   Query</SspropInitAppName>
          <LocaleIdentifier>1033</LocaleIdentifier>
          <ClientProcessID>5820</ClientProcessID>
          <Format>Native</Format>
          <AxisFormat>TupleFormat</AxisFormat>
          <Content>SchemaData</Content>
          <Timeout>0</Timeout>
        </PropertyList>

The Query Begin event indicates the syntax that was dispatched to the Analysis Server. The Query Cube events that follow provide notification that the formula engine has initiated query execution, indicating code as it is executed. Moreover, subsequent events are logged (other than the core events, these can differ, depending upon query complexity) to report upon events that occurred in resolution of our query. The information provided can be highly useful when we need to diagnose issues, or if we otherwise want to understand details of the steps of query execution. We can filter for MDX queries just as we did for SQL queries, should isolation of this sort prove useful.

28.  Click the Stop Selected Trace button within the toolbar, as shown in Illustration 21, to stop the trace.


Illustration 21: Click the Stop Selected Trace Button ...

And so we see, through our brief overview of its capabilities, that SQL Server Profiler can serve as a formidable tool in assisting us in understanding the details of what is going on within Analysis Services. The trace mechanism that the Profiler provides us allows for sophisticated control over the selection of events for our analysis and diagnosis efforts. As we have discussed, the SQL Server Profiler trace can be highly useful in our efforts with tuning of processing and query performance within Analysis Services 2005. We employ the SQL Server Profiler within other articles of this series, as well is in my MDX Essentials Series, here at Database Journal.

29.  Experiment further with the trace, and with SQL Server Profiler, as desired.

30.  Select File -> Exit to dismiss SQL Server Profiler, when ready, as depicted in Illustration 22.


Illustration 22: Exiting SQL Server Profiler

31.  Within the SQL Server Management Studio, Select File -> Exit to leave the application, when ready, as shown in Illustration 23.


Illustration 23: Exiting SQL Server Management Studio

32.  Save the MDX query file, if desired, by navigating to a convenient location, and clicking the Save button, on the Save File As dialog that appears next, similar to that depicted in Illustration 24.


Illustration 24: Saving the MDX Query (If Desired) ...

Conclusion

In this article, we introduced the SQL Server Profiler, a monitoring and troubleshooting tool that is highly useful in performance tuning Analysis Services 2005, including SQL and MDX queries. Our examination of the Profiler began with a discussion surrounding its use, in preparation for gaining some hands-on exposure via our practice exercise. We performed a connection to the Analysis Server with SQL Server Profiler, and defined a trace to monitor examples of processing and queries within Analysis Services.

We next executed our trace to monitor Analysis Services events, adding a filter as we proceeded to examine sample processing events, and then moving on to monitor sample query events, all of which we initiated, for purposes of our practice session, via the SQL Server Management Studio, just as we might in many real-world scenarios. Throughout the steps of our practice session, we touched upon various details to consider surrounding the usage of the SQL Server Profiler trace within our respective business environments.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved