Optimizing Microsoft SQL Server Analysis Services: Optimization Tools: Usage-Based Optimization Wizard
The Select a Partition dialog is required with a partitioned cube, because we can perform Usage-Based Optimization on only one partition at a given time. Were we using the Usage-Based Optimization Wizard with a single-partition cube, we would automatically skip this dialog altogether. Because we are using the Wizard on a partitioned cube, we are being provided with an opportunity to select the partition upon which we wish to perform optimization via this tool.
4. Accept the default, SSPerf_Warehouse, in the Partition: selector of the dialog.
The Select a Partition dialog appears as depicted in Figure 16.
Figure 16: Select SSPerf_Warehouse in the Select a Partition Dialog
5. Click Next.
The Select the queries you want to optimize dialog appears.
We have, within this dialog, five options for query selection criteria; we can use one or more of these to narrow the selection of logged queries upon which we wish to base our optimization. The five criteria, together with descriptions, are summarized in Table 1.
Query Selection Option
Optimization Selection is Based Upon:
|Queries within date ranges||Date range for queries. Date range type can be selected from:|
* Between – All queries between a desired start date and end date, inclusive
* Before – All queries on or before a desired date
* After – All queries after a desired date
|Queries by specific users||Users and Groups of Users, as defined by User Roles|
|Numbers of times a query has been run||Queries that were executed more than a certain number of times. (Number indicated in the box provided)|
|Length of time that a query takes to return a result set||Queries whose execution time exceeded a certain amount of time. (Number of seconds / minutes indicated in the boxes provided)|
|Queries sent to one of the specific data storage types used with the cube||Queries executed against a specific data storage object type: |
* MOLAP Cube
* ROLAP Tables
* Server Cache
Table 1: The Five Query Selection Criteria – Usage-Based Optimization Wizard
6. Click the checkbox to the left of Queries that ran more than: on the dialog.
7. Leave the number selector at the default of “0.”
8. Click Next.
The Select the queries you want to optimize dialog appears as depicted in Figure 17.
Figure 17: The Select the Queries You Want to Optimize Dialog
9. Click Next.
The Review the results dialog appears, as shown in Figure 18.
Figure 18: The Review the Results Dialog, Status Bar Circled
The Review the results dialog allows us to view the query selection that results from the criteria we imposed in the Select the queries you want to optimize dialog. Optimization, through the modification of existing aggregations, will be based upon these results.