Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 9, 2006

Usage-Based Optimization in Analysis Services 2005 - Page 4

By William Pearson

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. 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). First, we need to 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.

Click for larger image

Illustration 11: Changing the CreateQueryLogTable Setting Value to True ...

We next need to direct Analysis Services as to where we wish to host the Query Log. Analysis Services 2005 offers us a great deal of flexibility (an improvement over 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, ANSYS043 Adventure Works DW. This is a logical 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\M1MSSQL2K5, as we see in illustrations throughout recent articles of the series.

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 putting the Usage-Based Optimization Wizard, which depends upon the Query Log to perform its work, into action.

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 "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:

MSASQueryLog

Our settings, within 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

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

20.  Click Connect.

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 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 positioned to conclude our preparations, by generating some sample queries, and to begin getting some hands-on practice with the Usage-Based Optimization Wizard.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM