Usage-Based Optimization in Analysis Services 2005 - Page 4
January 9, 2006
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.
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
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.
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.
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.
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:
Our settings, within the rows of the Analysis Services Properties dialog we have visited, appear as depicted in Illustration 15.
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.
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.
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.
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.
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.