Databases Reference
In-Depth Information
3. Once it finishes progress, you can see three new tabs on the main screen:
F
Progress
F
Recommendations
F
Reports
4.
In the Recommendations tab, it is showing two recommendations and also saying that
by implementing all two recommendations, we will get improvement of 84 percent:
5.
There is a column Definitions on the right, which gives the exact syntax for creating
index or statistics DTA suggests. In this case, it has suggested one statistics and
two indexes.
How it works...
DTA analyzes the workload submitted to it; in our case, there is only one query provided.
DTA checks the table structure, predicate provided in the query and statistics/histogram if
available for predicates, and try to find out best suited way to execute the query, if it finds
anything missing (such as index, statistics, and so on), DTA recommends it to create. Basically
after submitting workload (or query), DTA goes through each table and view available in
workload along with the predicate used. It searches for the available index, statistics, and
partition scheme too (if selected) in order to prepare the suggestion list. If it doesn't find
statistics on predicate, DTA suggests to create statistics and if statistic is out of date on a
predicate column, DTA suggests that we update it. DTA also look for all available indexes on
the predicate and if DTA finds any missing index, it will suggest to create it.
All suggestions given by DTA is completely dependent on the workload provided. If incomplete
workload is provided to DTA, there is a chance that DTA would suggest something wrong that
may harm the overall performance. So it's highly recommended that we check the suggestions
carefully before implementing it.
A word of caution: Before creating any index suggested by DTA, it is
recommended to check the table and column that DTA suggests because
index comes with little overhead on DML statement and index needs space
to maintain itself. So it is better to first check whether the column that DTA
suggests is worth the index or not.
Search WWH ::
Custom Search