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

This process should take little time since no real what-if calls are made,
after which the session continues normally. However, the enumeration
strategy itself is repeated. More advanced alternatives need to cache
the state of the tuning session itself, so that at restart time the tuning
proceeds exactly where it was interrupted (the specific details depend
on actual implementations). For such pause/restart functionality to be
effective, no changes can occur while the tuning session is suspended.
For instance, if a table is dropped and the tuning session is restarted,
there would be metadata inconsistencies that prevent resuming the ses-
sion correctly. For that reason, when the tuning session is suspended,
all metadata information needs to be saved along with the state of the
session, which is checked at resume time to validate that the session is
able to continue.
7.5 Production/Test Tuning
Tuning tools perform complex search procedures invoking many what-if opti-
mization calls to the database management system (DBMS). For that reason,
tuning large workloads imposes a significant overhead on the server being
tuned and can severely reduce the performance of applications on production
servers [see Figure 7.1(5)]. In large installations, however, it is common to
rely on test servers in addition to production servers. A test server can be
used for a variety of purposes, including performance tuning and evaluation
of application changes before they are deployed on the production server. A
straightforward way to reduce the impact of tuning on a production server is
to use a test server as follows:
1. Copy the relevant databases from the production server to the test
server.
2. Tune the workload on the test server.
3. Deploy the recommendation obtained on the test server into the pro-
duction server.
The advantage of such a simplistic approach is that once the databases are
copied into the test server, there is no additional tuning overhead imposed on
the production server. However, this approach suffers from many drawbacks
that severely limit its applicability. First, databases can be very large (pro-
duction databases can be hundreds of gigabytes in size or more) or changing
frequently. In such situations, copying large amounts of data from production
to test servers for the purposes of tuning can be time-consuming and resource
intensive. Second, the hardware characteristics of test and production servers
can be very different. Production servers tend to be much more powerful than