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
    .
William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles