Database Reference
In-Depth Information
Consider an instance of the physical design scheduling problem
(
C
0
,
C
f
,
B
)
,
and let
G
be a graph defined as follows. Each vertex in the graph rep-
resents an index in the search space,
V
=
(
V
,
E
)
={
v
|
v
∈
(
C
0
∪
closure
(
C
f
))
∧
(v)
≤
size
}
=
(v
1
,v
2
)
B
. Additionally, there is a directed edge
e
in
E
if the symmetric dif-
ference between
|
(v
1
−
v
2
)
∪
(v
2
−
v
1
)
|=
1). The weight of
e
is equal to the cost of creating the index in
v
2
−
v
1
starting
in configuration
v
1
(if
v
1
⊂
v
2
), or the cost of dropping the index in
v
1
−
v
2
(if
v
2
⊂
v
1
). The label of edge
e
is the corresponding
create
or
drop
action.
In that case, the solution for
v
1
and
v
2
has a single element (that is, if
)
is the sequence of labels of the
shortest path between
C
0
and
C
f
in the graph defined previously. While this
property does not directly lead to an ecient algorithm (i.e., the induced
graph has an exponential number of nodes in the worst case), it can be used
as a starting point to define search strategies.
(
C
0
,
C
f
,
B
7.8 A Case Study: Database Engine Tuning Advisor
We conclude this chapter with an example of a physical design tuning tool
that is part of a commercial DBMS. Specifically, we focus on the
Database
Engine Tuning Advisor
,or
DTA
for short, found in
Microsoft SQL Server
2005
and above (see Section 7.10 for information about physical design tools
found in other database systems).
DTA
uses
named sessions
to manipulate, store, and tune different scenarios
over time. Each set of input parameters, output configuration, logs, and re-
ports is gathered in a session, which is stored in the database server itself and
can be retrieved later by its name. Figure 7.4 shows three sessions (named
GLS
,
NREF
, and
TPCH22
). While the first two are previous sessions that
were already processed, the last one is being currently defined. In addition to
the session name, the figure shows that the input workload comes from a file
named
d:
tpch-all.sql
, and the database to be tuned is
called
tpch1g
. Alternatively, workloads can be obtained from profiled tables,
and the database administrator can choose subsets of tables to perform tuning
(implicitly discarding queries that refer to other tables).
Technically, this information is sucient to start a tuning session.
DTA
ex-
poses several additional tuning options, some of which are shown in Figure 7.5.
For instance, the DBA can set a time limit that specifies when the tuning ses-
sion has to finish (and return the best configuration found so far). To meet
the time constraint,
DTA
uses several techniques, including clustering-based
workload compression and a combination of caching optimization calls and
an incremental approach that progressively tunes workload fragments (as de-
scribed in Section 7.4). Additionally, different tuning modes can be set by the
user, as shown in the figure. For instance, we can choose to recommend only
non-clustered indexes and to additionally consider clustered indexes and also
\
\
\
dta
workloads
Search WWH ::
Custom Search