Administration and Optimization: SQL Server Profiler for Analysis Services Queries

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.