Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Toward More Sophisticated Analysis - Page 2

Customizing Query Log Capture

Both of the usage-based analysis and optimization processes provided in MSAS (via the Usage Analysis Wizard and the Usage-Based Optimization Wizard, as we learned in our last lesson) perform their functions based upon statistics captured in the Query Log. Within the MSAS 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. For that matter, from the Properties settings, we can:

• Stop and start logging (we can leave it stopped entirely, if we desire);
• Clear the log;
• Change the logging interval (Valid range is an integer between 1 and 10,000).

Although somewhat peripheral to our immediate considerations, it might be useful to mention that we can also manage the automatic logging of processing messages, directing the server to enable the logging of the messages to a log file for which we can designate a file path and name.

While setting the logging interval too low might degrade performance in a busy production environment, the logging interval setting for a development environment would 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.

Let's practice the steps involved in setting this property as if we were in a development scenario.

Our server(s) appear.

A Connecting to the Analysis Server message briefly appears, and then the server icon displays the small, green arrow to its lower right, indicating that a connection has been established, as shown in Illustration 5.

The Properties dialog appears, defaulted to the General tab.

NOTE: Making a backup of the msmdqlog.mdb before undertaking any of the modifications noted below is highly recommended to avoid issues with an operating MSAS environment, damaging a production log, etc.

Logging frequency is set in the Write to Log Once per [integer] Queries box.

Note that the Clear Log button is here, as well. We can choose to clear the log of all entries with this option.

A message box appears reminding us that we must restart Analysis Services for the logging frequency change to take effect.

Analysis Services closes.

To see the results of our change in logging frequency, we can now restart Analysis Services (performing steps 1 through 4 above will do the trick), and then run a test query or two via the MDX Sample Application. If we then look into msmdqlog.mdb, we can see the new entries, containing the integer "1" in the SamplingRate field, for the respective queries.

To give a common example of how lowering the Sampling Rate might be useful, let's consider a typical approach to optimizing performance based upon usage. We often begin the process by creating partitions with zero aggregations. We next adjust query logging to log every query for a period of time, to assist us in capturing typical usage patterns. We then use the Usage-Based Optimization Wizard to design aggregations appropriate to the usage. In short, we adjust the Sampling Rate to increase the size of the sample upon which we run the Optimization Wizard, presumably to more closely bring actual usage patterns into consideration within the process.

Now that we have an understanding of the workings of the Query Log, we will discuss options for producing customized reports to meet our business needs.