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

Production
DBMS

Tuning
DBMS

[Export metadata
and statistics]

[Create statistics] [Tune]

Configuration

Tuning Tool

Workload

FIGURE 7.2 Tuning production servers with limited overhead.

test servers in terms of processors, memory, and disk capabilities. For that
reason, it might not be even possible to copy the full databases from the pro-
duction server into the test server. Additionally, since the tuning process relies
on the optimizer to arrive at a recommendation and that in turn is tied to the
underlying hardware, this can lead to vastly different results on the test server.
We next discuss an interesting approach to exploit a test server for physical
design tuning that does not require copying database tables from the produc-
tion server. The key observation that enables this functionality is that the
query optimizer relies on metadata and statistics when generating execution
plans for input queries. We leverage this observation to enable tuning on the
test server as follows (see also Figure 7.2):

1. Copy the metadata of the relevant databases from the production server
to the test server, without importing actual data from tables. Metadata
can be imported using scripting capabilities that are available in current
DBMS. This is generally a very fast operation as it processes only system
catalog entries, which is independent of data size.
2. Tune the workload on the test server. For getting the same plan on the
test server as we would have obtained on the production server, we re-
quire two important functionalities from the database server. First, the
tuning tool might need certain statistics to be present during what-if
optimization calls. However, statistic creation procedures (even those
that use sampling) require access to the actual data, which is stored on
the production server. Therefore, whenever the tuning tool determines
that certain statistics are needed, it communicates with the production
server and imports such statistical information (note that this step might
require the production server to actually create such statistics on de-
mand). Second, the hardware parameters of the production server that
are modeled by the query optimizer when it generates a query plan (e.g.,
the amount of memory or number of central processing units [CPUs])