Administration and Optimization: SQL Server Profiler for Analysis Services Queries
April 9, 2007
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.
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:
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: