Export (0) Print
Expand All

Optimizing Performance Based on Usage

SQL Server 2000

Optimizing Performance Based on Usage

To optimize partition performance based on patterns of logged queries, use the Usage-Based Optimization Wizard, which takes you through steps to specify options for optimization. For more information, see Usage-Based Optimization Wizard.

You can optimize some or all of the partitions for cubes that contain multiple partitions, but each partition must be individually optimized. If a partition contains existing aggregations, you can either add the new aggregations to the existing ones or replace them. You can also change the storage mode of the partition you are optimizing. You must process a partition after optimizing its aggregations, and the final step of the wizard allows you to process immediately or defer processing.

Important  Do not optimize partition aggregation designs if you might want to merge partitions in the future. You cannot merge partitions unless their aggregation designs are identical. For more information, see Partitions and Merging Partitions.

The usage-based optimization process designs aggregations tailored for query patterns recorded in the query log, which by default records every tenth query. You can adjust the content of the query log by changing this interval, stopping logging altogether, or clearing the log to restart logging. One approach to optimizing performance based on usage is to create partitions with zero aggregations, adjust query logging to log every query for a period of time to capture typical usage patterns, and then use the wizard to design aggregations appropriate to the usage. For more information, see Logging Tab (Properties Dialog Box).

To start the Usage-Based Optimization Wizard

Analysis Manager

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft