Overview of the Query log
and Operation of the Query Log
As we have seen in
other articles, and have emphasized in the sections above, the Query Log lies
at the heart of Usage Analysis for the Analysis Services Database and
some of its child objects, including cubes. As we have also previously noted,
the Query Log captures details about the queries that have been enacted
upon the server by client applications. We have prepared the Query Log
for examination in this section, having selected the creation and maintenance
of the MSSQL Server table incarnation versus the file option, which we
mentioned was possible in passing earlier.
A study of the MSSQL
Server table, which we named ANSYS049_MSASQueryLog, reveals some
differences over its counterpart in the previous version of MSSQL Server
Analysis Services. Unlike its SQL Server Analysis Services 2000
predecessor (by default an MS Access database named msmdqlog.mdb),
we can name the Query Log table whatever we direct in the Properties
settings for the Analysis Server under consideration, and begin
with an MSSQL Server table (rather than converting an MS Access
table to one) as we saw above.
A look at
the ANSYS049_MSASQueryLog table reveals the nature of the data within
the Query Log. Let's look at the layout of the table from SQL Server
Management Studio, before examining its contents on a more specific basis.
From within Object
Explorer, right-click the ANSYS049_MSASQueryLog table (housed within
the Tables folder of the AdventureWorksDW relational database, as
we saw earlier).
from the context menu that appears, as shown in Illustration 30.
Illustration 30: Opening
the ANSYS049_MSASQueryLog Table Schema ...
table schema appears on a new Browser tab (adjacent to the Adventure
Works [Browse] tab we opened earlier), within the SQL Server Management
Studio, as depicted in Illustration 31.
Illustration 31: The ANSYS049_MSASQueryLog
As we noted in Usage-Based
Optimization in Analysis Services 2005, the Usage-Based
Optimization Wizard relies upon the Query Log to support its
operations. As we can see, the log is composed of several relatively
straightforward fields. The fields, together with their respective
descriptions, are summarized in Table 1.
Table 1: The Fields of
the Query Log
name of the Analysis Services database used in the query
name of the cube used in the query
name of the user that ran the query
numeric string indicating the level from each dimension used to satisfy the
time the query began
length of time (in seconds) of the query execution
In lockstep with a
review of the fields from a description perspective, we can view the actual
data in the table from the SQL Server Management Studio, as well.
From the Object
Explorer, right-click the ANSYS049_MSASQueryLog table, once again.
Table from the context menu that appears, as shown in Illustration 32.
Illustration 32: Opening
the ANSYS049_MSASQueryLog Table ...
table appears on another new Browser tab (adjacent to the tabs we have
already opened), within the SQL Server Management Studio, as partially
depicted in Illustration 33.
Illustration 33: Partial View of
the ANSYS049_MSASQueryLog Table
the fields has significant potential, with regard to analysis and reporting
utility. I have even created cubes from this table, which, coupled with
performance, sizing and other such information from various sources, can
support all manner of administrative and similar analysis. The fourth column, Dataset,
can be highly useful with regard to the specific information that it reveals
about cube usage. The somewhat cryptic records within this column represent
the associated levels accessed for each dimensional hierarchy within the
query. An example of the Dataset field appears (enclosed in a red
rectangle), within a subset of a sample row, as shown in Illustration 34.
Illustration 34: Example
of the Dataset Field
won't go into a detailed explanation in this lesson, I expect to publish a
prospective article that outlines the interpretation of the digits in the Dataset
field (we will trace an example Dataset field's component digits to
their corresponding components in the respective cube structure), along with
more information regarding report writing based upon the Query Log in
general. Our purpose here is more to expose general options for using the Query
Log directly to generate customized usage analysis reports.
fields provide rather obvious utility in analyzing cube usage, together with
performance in general. The fields present information which, particularly in
combination with Dataset, helps us to report precisely on the exact
points at which queries interact with the cube. These combinations can provide
excellent access and "audit" data. To some extent, they can confirm
the validity of cube design if, say, a developer wants to verify which
requests, collected during the business requirements phase of cube design, are
actually valid, and which, by contrast, might be considered for removal from
the cube structure based upon disuse, should the time arrive that we wish to
optimize cube size and performance by jettisoning little-used data.
StartTime and Duration provide the
ingredients for evolved performance trending (via relational reports,
OLAP reports, KPIs assembled from either or both, and more), and act as useful
statistics upon which to base (or filter) numerous types of administrative
reports, including information that will help us to plan for heavy reporting
demands and other cyclical considerations.
Query Log Capture
have seen, the usage-based analysis and optimization processes provided via the
their functions based upon statistics captured in the Query Log. From within
the Analysis Server Properties dialog box, which contains options
that control user interfaces, server environment, processing, logging, and
add-ins, we can make the query logging process more frequent than the default
of one-in-every-ten queries, as we saw earlier. Moreover, from the Properties
settings, we can:
Stop and start
logging (we can leave it stopped entirely, if we desire);
Clear, idle or
restart the log;
Create a new "prospective"
log, while retaining its predecessor for archiving, etc.;
We have also mentioned that we can further manage the
automatic logging of processing messages by directing the server to enable the
logging of the messages to a log file for which we can designate a file path
While setting the logging interval too low might degrade
performance in a busy production environment, the logging interval setting for
a development environment might typically be a lower number than 10. This would
obviously allow us to capture more voluminous statistics to support intensive
analysis of cube performance and use, prior to optimizing it for final delivery
to information consumers. To cite an example of the utility of lowering the Sampling Rate
that most of us might find intuitive, a fairly straightforward approach to
optimizing performance based on usage is to create partitions with zero
aggregations, adjust query logging to log every query for a period of time to
capture typical usage patterns, and then use the Usage-Based Optimization Wizard to design aggregations
appropriate to the usage. Once this is done, we can likely afford to raise the
Sampling Rate frequency again, to lower the overhead inherent in its
Server Management Studio when ready.
we have a basic understanding of the workings of the Query Log, we will
discuss options for producing customized reports to meet our business needs.
Direct Reporting for Sophisticated Utilization Analysis
As many of us probably
can see, we can rely upon the fact that the options for reporting choices for
cube utilization analysis are plentiful, whether we maintain the Query Log
in an MSSQL relational table or a file, as we have discussed, or otherwise move
/ transform the data, and house it in an administrative reporting warehouse /
mart, or some other such arrangement. We will find that many common packages
can be used in identical fashion to report from these stores via ODBC and other
channels. I have even created cubes (both for Analysis Services and other
popular OLAP cubes) for larger clients from the statistical information
regarding cube usage, together with statistics that come from other diagnostic
sources, generating robust and useful mechanisms for tracking cube usage and
performance from many perspectives. (I expect to publish articles that
detail more of the specifics of some of these administrative database and
reporting options, in later articles in this series, as well as my MSSQL Server Reporting Services series, here
at Database Journal).
Regardless of the
reporting application, the concepts are essentially the same in reporting from
the Query Log. The general steps include the following:
of a connection to MSSQL Server, and the database containing the Query
Log table (or a connection to the respective location of the data if it has
been housed elsewhere);
Creation of a
query / dataset against the data source;
Creation of a
report or other layout file within which to accumulate / present the selected
deployment of the report / other layout in a manner whereby it becomes
accessible to the intended audience(s).
As I have stated, our "medium"
for reporting can be selected from a wide array of applications. I have created
similar reporting processes in the past using a wide range of business
intelligence tools, including MSSQL Server Reporting Services, Cognos, Business
Objects, Crystal Reports, ProClarity, MicroStrategy, and Microsoft Office, among
other less common tools.
Because reporting typically
entails more than the mere entrainment of the data from the Query Log,
for use in a report or reports within any of a multiple choice of applications,
we will not be able to examine report writing and formatting considerations,
aggregating the data in the report or elsewhere, etc., in this article. Many
of us will know the steps required to create robust and attractive reports
within our own choices of solutions. Other of my articles elsewhere deal with
the specifics of working with various reporting options. The demand is evident,
in discussions with my clients and readers, for the capability to trend cube
performance, create user access reports, and so forth, and I will focus later on
some of those needs, together with ways to meet them. I will attempt to
accomplish this over the months to come, with different reporting tools after
we have exposed other components of the statistical data source, of which the Query
Log is only a part.
In this article,
we introduced the general need for more sophisticated analysis based upon usage
analysis statistics that we can collect surrounding our Analysis Services
cubes. Our primary focus was a closer examination of the source of Analysis
Services Database (and predominantly cube) performance statistics, the Query
Log, discussing its location and physical structure, how it is populated,
and other characteristics.
We first created a copy of a sample Analysis Services
database for use in our practice exercise, and then enabled the Analysis
Server Query Log to capture query statistics. We then processed our cube,
before manipulating data to create Query Log entries, to complete
preparation for our overview of the Query Log.
moved into the focus of our session, examining the Query Log contents,
discussing the various statistics captured. Finally, we discussed reporting
possibilities, commenting on both relational and OLAP options. As a part of
our exploration of Query Log statistics capture, we touched upon various
means of customizing Query Log capture to meet local needs.
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.