Databases Reference
In-Depth Information
tion that is based on query analysis and limited interaction with the query processor.
(Indexed views are not proposed in the Fast mode.) The Medium mode, which is the
default, proposes indexes and indexed views and is significantly faster than the Thor-
ough mode for large workloads. Although the Medium mode of operation searches
fewer possibilities, in many cases it is able to provide a respectable set of recommen-
dations. The Thorough mode consumes the maximum amount of time and
resources, but it also gives the highest quality recommendation for the workload.
Below is a comparison of the running times and expected improvements of the
[Database Tuning Advisor (DTA)] in different tuning modes for a sample workload
on a 1.2 GB database [Table 12.2].
In SQL Server 2005 a time limitation was added to the search options, extending
the Thorough, Medium, and Fast options to an explicit clock time constraint, very sim-
ilar to the DB2 approach.
Table 12.2
SQL Server DTA Characteristics
