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

FIGURE 7.7 Additional reports produced by DTA . (Used with permission

from Microsoft.)

and supporting statistics that should be created. For instance, the first item in
the recommendation is an index on table customer over columns c nationkey
and c custkey , and the second item is a multicolumn statistic on columns
c custkey and c acctbal . Selecting any such item produces a preview script
with the Standard Query Language ( SQL ) statement that can be used to create
the index or statistic in the DBMS.
In addition to the recommended configuration, DTA produces reports that
give information back to the user. Figure 7.7 shows reports for the last tuned
session, including the time taken for tuning, the storage requirement and cost
improvement of the recommended configuration, and over a dozen customized
reports, similar to those discussed in Section 7.6. After analyzing the reports,
the DBA can deploy the recommended configuration or schedule a task to do
it at a later time.
DTA defines a public Extensible Markup Language (XML) schema for phys-
ical database design that forms the basis of input and output. The user inter-
face shown in Figures 7.4 to 7.7 is a front end that translates user requests
into input XML files and output XML files into recommendations and reports.
Figure 7.8a shows a fragment of the input XML file that was generated for
the tuning session described earlier, and Figure 7.8b shows a fragment of the
corresponding output XML file. Through customized XML input files, DTA
can use test servers for tuning and can evaluate arbitrary configurations for
workloads without tuning and other advanced features.