Procedure
Enable
Query Logging to Gather Statistics
Usage-based
optimization is
based upon usage. We therefore must capture usage statistics to
have a basis for optimization. We do this by enabling query logging
within the Analysis Server, as we shall see in the steps that follow.
1.
Right-click
the Analysis Server with which we are working.
2.
Select Properties
from the context menu that appears, as depicted in Illustration 10.
Illustration 10: Select
Properties from the Context Menu ...
The Analysis Services
Properties dialog appears. As we have noted in other articles, this is a
busy place, indeed: many settings of a default and operational nature are
maintained here. (My advice is to learn all that we can about each entry
within this table-like dialog, so as to know of its existence when the time
comes to address the utility that it offers). Here we will enable the logging
of query statistics.
3.
Scroll down to
the Log \ QueryLog \ CreateQueryLogTable entry in the table within the
dialog.
4.
Change the Value
setting for the entry, which is defaulted to false, to true,
via the selector provided, as shown in Illustration 11.
Illustration 11:
Changing the CreateQueryLogTable Setting Value to True ...
We next need to direct Analysis
Services as to where we wish to house the Query Log. Analysis
Services 2005 offers us a great deal of flexibility (and improves upon Analysis
Services 2000's provision of an MS Access database for this purpose,
which could be migrated to MSSQL Server). We will direct that the Query
Log database be created within the sample AdventureWorksDW
relational database, which underlies our new clone Analysis Services
database, ANSYS049 Adventure Works DW. This is a convenient place to
put the Query Log for this session, but we are free to put it into any OLE-DB
/ .NET compatible data source.
5.
In the Log
\ QueryLog \ QueryLogConnectionString row, immediately below the CreateQueryLogTable
row (where we assigned the value of true above), click the Value
box, and then click the box that appears to its immediate right (marked "..."),
as shown in Illustration 12.
Illustration 12: Beginning
Connection Setup for the Query Log Data Source
Connection Manager appears.
6.
Leave the Provider
setting at its default of Native OLE DB \ SQL Native Client.
7.
Select / type
the appropriate Server or Server / Instance combination into the Server
name box.
(Mine is MOTHER1\MSSQL2K5,
as we see in illustrations throughout recent articles of the series. )
NOTE:
If we are working in an environment wherein a side-by-side installation of MSSQL
Server 2000 and MSSQL Server 2005 has been performed, the Server
Name / Instance will be required ("Localhost" / the
Server name alone will not be assigned to the MSSQL Server 2005
instance, by default).
8.
In the Log on
to the server section of the Connection Manager dialog, make the
authentication selections appropriate to your environment.
I am using Windows
Authentication, and therefore select the respective radio button.
9.
In the Connect
to a database section in the lower half of the dialog, in the selector box
labeled Select or enter a database name, select the AdventureWorksDW relational
database.
The Connection Manager
dialog appears, with our input, similar to that depicted in Illustration 13.
Illustration 13: Connection
Manager Dialog, with our Input
10.
Click the Test
Connection button to ascertain connectivity to the database.
Assuming correct
settings, confirmation of connectivity appears in a message box, as shown in Illustration
14.
Illustration 14: "Test
Connection Succeeded"
11.
Click OK
to close the message box.
12.
Click OK
to accept settings and to close the Connection Manager dialog.
We are returned to the Analysis
Services Properties dialog, where we see that the Value box of the Log
\ QueryLog \ QueryLogConnectionString row now contains a connection
string, courtesy of the Connection Manager. We have enabled the collection
of query statistics and established the location of their collection.
Next, we will make a couple of additional adjustments before examining and then
populating the Query Log table.
13.
In the Log
\ QueryLog \ QueryLogSampling row, which appears three rows below the Log
\ QueryLog \ QueryLogConnectionString row, change the Value from the
default of 10 to 1.
Here we are merely
increasing the sampling rate from the default of "capture
statistics from every tenth query" to "capture statistics for each
query," a step similar to those we took in previous articles with the Usage-based
Optimization Wizard as it existed in Analysis Services 2000.
(Although the setting was managed a bit differently, in the Write to log once
per [number] box within Analysis Services 2000, our intent then, as
it is now, was to simply allow the log to capture enough data to make the
procedural steps of our practice exercise meaningful.)
14.
In the Log
\ QueryLog \ QueryLogTableName row, which appears in the row immediately
below the Log \ QueryLog \ QueryLogSampling row, modify the Value
from its default of OLAPQueryLog to the following:
ANSYS049_MSASQueryLog
It might be useful to note,
too, that we could have specified a file rather than a table for the intended destination
for usage statistics collection. If we chose a file versus a table, we can
specify the location of the file in the Log \ QueryLog \ QueryLogFileName row,
which appears in the row immediately below the Log \ QueryLog \ QueryLogConnectionString
row.
Our settings, within the relevant
portions of the rows of the Analysis Services Properties dialog we have
visited, appear as depicted in Illustration 15.
Illustration 15:
Modified Settings within the Analysis Services Properties Dialog (Partial View)
15.
Click OK
to enact the settings we have made above.
The "Executing"
indicator appears in the bottom left corner of the dialog momentarily, and then
the Analysis
Services Properties
dialog closes. We are returned to the SQL Server Management Studio.
We can confirm the
creation of the new MSASQueryLog table within the AdventureWorksDW relational
database easily from the SQL Server Management Studio, where we can
access the relational world in combination with the OLAP world, as we shall see
in the next steps.
16.
Click the
Connect button atop the Object Explorer.
17.
Select Database
Engine from the menu that appears, shown circled in Illustration 16.
Illustration 16:
Connecting to the SQL Server 2005 Database Engine
The Connect
to Server dialog appears, just as we saw earlier when connecting to Analysis
Services.
18.
Enter the
correct Server or Server / Instance combination into the Server
name box.
19.
Select the
appropriate authentication mode into the Authentication box that
immediately follows (with related details in the Password box, as
appropriate).
The Connect to Server
dialog appears similar to that depicted in Illustration 17.
Illustration 17: The
Connect to Server Dialog Appears
We return to the SQL
Server Management Studio, where we see the instance of the Database
Engine appear within the Object Explorer, underneath the Analysis
Server with which we have been working, as shown in Illustration 18.
Illustration 18: The
Database Engine Instance Appears within Object Explorer
21.
Expand the Database
Engine Server by clicking the "+" sign to its immediate left, if
required.
22.
Expand the Databases
folder underneath the expanded server.
23.
Expand the AdventureWorksDW
database.
24.
Expand the Tables
folder within the AdventureWorksDW database.
The tables within the
folder appear. Among them, we see the new ANSYS049_MSASQueryLog table that
we created in our earlier steps, as depicted in Illustration 19.
Illustration 19: The
MSASQueryLog Table Appears in the AdventureWorksDW Database
The table is in place and
query logging is enabled. We are now positioned to generate some sample
queries, and to begin our examination of the Query Log.
» 
Comment and Contribute
