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

need to be appropriately simulated on the test server. This function-
ality might easily be incorporated into an existing query optimizer by
parameterizing hardware-dependent values and having the possibility of
importing/exporting hardware configuration across different servers.
3. Deploy the recommendation obtained on the test server into the pro-
duction server.
Note that the only overhead introduced on the production server during
tuning is the creation of additional statistics that are necessary as tuning
progresses (if any). The rest of the tasks, such as performing what-if opti-
mizations or the enumeration strategy proper, are done on the test server. In
practice, this approach results in drastic reductions of overhead in production
servers without compromising the quality of the resulting configurations.
7.6 Reports
After a physical design session finishes, it is useful to complement the result-
ing configuration with analytical reports [see Figure 7.1(6)]. This additional
information gives insights to DBAs, who can then either accept the recom-
mended configuration with no changes or slightly modify it until they are
satisfied. These reports provide information such as the cost of queries occur-
ring in the workload under the current and recommended configurations, the
fraction of queries that increase their cost under the new configuration, or
the relationships between queries and the indexes they reference. Figure 7.3
shows some examples of reports that are generated and returned along with
physical design recommendations.
7.7 Deployment Scripts
At the end of a physical design session, we obtain a new configuration to deploy
into the database server. This configuration might contain new indexes that
are not present in the current configuration and might also require removing
existing indexes. A common way to recommend a configuration is in the form
of a deployment script [see Figure 7.1(7)]. This script can be immediately
executed or postponed to a later time in which the system is not heavily
loaded. A simple deployment script contains one create-index statement for
each new index in the configuration and one drop-index statement for each