Practical Aspects in Physical Database Design - Automated Physical Database Design and Tuning - page 131

FIGURE 7.6 Recommendation produced by DTA . (Used with permission

from Microsoft.)

other access paths (e.g., materialized views, which we cover in Chapter 8).
The user can also choose to keep all existing indexes in the current configu-
ration or just the clustered indexes or to start a new recommendation from
scratch. There are additional options not shown in the figure, which let DBAs
specify other aspects of tuning (e.g., storage bounds or the maximum number
of columns in any recommended index).
After the input session is defined, we can launch the tuning task proper.
DTA uses variations of the original greedy(m,B) technique discussed in Sec-
tion 6.1 (adapted to work in incremental mode). After the tuning is finished
(or the time bound is exceeded), DTA returns the best recommendation, as
shown in Figure 7.6. The figure shows an estimated improvement of 77%. This
is a quick report that quantifies how much the recommendation is expected
to speed up the workload. The improvement of a recommended configuration
C R over the current configuration C 0 for workload W is defined as

1

cost ( W , C R )

impro v ement ( C R , C 0 , W ) = 100% · −

cost ( W , C 0 )

Improvement values can be negative (when the recommended configuration
is less ecient than the current one due to stricter space constraints) but is
always smaller than 100%. Additionally, the figure shows the configuration
that was recommended by DTA . The recommendation includes both indexes