Databases Reference
In-Depth Information
The Usage-Based Optimization Wizard runs exactly the same aggregation design algorithm,
but it also enables you to influence your aggregation design by providing it with statistical
information that is collected in the query log. To launch the Usage-Based Optimization
Wizard, in SQL Server Management Studio, right-click the node for the partition you're
working on and select Usage Based Optimization from the resulting menu.
The wizard connects to a
query log
, a relational table where Analysis Services logs statistical
information about user queries. It enables you to select a subset of the query log that will
automatically be entered into the aggregation design algorithm. This information enables
the algorithm to design better suiting aggregations for your cube.
The Usage-Based Optimization Wizard runs a slight variation of the aggregation design
algorithm. The algorithm moves gradually through the space of potential aggregations. It
starts from the top—the aggregation that contains grand totals across all dimensions—and
gradually makes its way down to the lower-granularity aggregations. As it progresses
through the aggregation space, it considers each aggregation to determine whether adding
it to the existing the set of aggregations. Greater weight is given to aggregations that will
improve the performance of the subset of queries you selected in the wizard.
Interesting to mention a new option in the SQL Server 2008 version of the Usage-Based
Optimization Wizard: If you run the wizard, on the “Completing the wizard” page you will
find an option to merge new aggregations to the one existing already. For example: Run the
Usage-Based Optimization Wizard for
Inventory Fact 1997
partition in the
Warehouse
measure group, and you will be able to merge new aggregations with aggregations of an exist-
ing aggregation design for that partition. This option enables you to incrementally update an
existing aggregation design with new aggregations based on the usage of aggregations by the
queries.
Setting Up a Query Log
To run the Usage-Based Optimization Wizard, you need to configure Analysis Services to
write statistical information into a query log. A query log is a table in the SQL Server data-
base. To set up a query log, you must change several server properties, described in Table
23.2.
TABLE 23.2
QueryLog
Server Properties
Name
Description
Defines the frequency with which informa-
tion is logged to the query log. The default
value is
10
; that is, every tenth query is
logged. To log every query, set this property
to
1
.
Log\QueryLog\QueryLogSampling
Search WWH ::
Custom Search