Databases Reference
In-Depth Information
optimizer itself to help find which missing indexes would benefit existing queries. In this
scenario, the physical design tool would use the optimizer to evaluate the cost of queries
given a set of candidate indexes. An additional benefit of this approach is that, as the
optimizer cost model evolves, any tool using its cost model can automatically benefit
from it.
SQL Server was the first commercial database product to include a physical design tool,
in the shape of the Index Tuning Wizard which shipped with SQL Server 7.0, and
which was later replaced by the Database Engine Tuning Advisor (DTA) in SQL Server
2005. Both tools use the Query Optimizer cost model approach and were created as
part of the AutoAdmin project at Microsoft, the goal of which was to reduce the total
cost of ownership (TCO) of databases by making them self-tuning and self-managing.
In addition to indexes, the DTA can help with the creation of indexed views and
table partitioning.
However, creating real indexes in a DTA tuning session is not feasible; its overhead could
impact operational queries and degrade the performance of your database. So how does
the DTA estimate the cost of using an index that does not yet exist? Actually, even during
a regular query optimization, the Query Optimizer does not use indexes to estimate
the cost of a query. The decision on whether to use an index or not relies only on some
metadata and the statistical information regarding the columns of the index. Index data
itself is not needed during query optimization but will, of course, be required during
query execution if the index is chosen.
So, to avoid creating real indexes during a DTA session, SQL Server uses a special kind of
indexes called hypothetical indexes, which were also used by the Index Tuning Wizard.
As the name implies, hypothetical indexes are not real indexes; they only contain statis-
tics and can be created with the undocumented
WITH
STATISTICS_ONLY
option of the
CREATE
INDEX
statement. You may not be able to see these indexes during a DTA
session because they are dropped automatically when they are no longer needed.
However, you could see the
CREATE
INDEX
WITH
STATISTICS_ONLY
and
DROP
INDEX
statements if you run a SQL Server Profiler session to see what the DTA is doing.
