Database Reference
In-Depth Information
Figure 10-9.
Query tuning recommendations
The first time through, the Database Engine Tuning Advisor suggested dropping most of the indexes on the
tables being tested and a bunch of the related tables. This time it suggests creating a covering index on the columns
referenced in the query. As outlined in Chapter 4, a covering index can be one of the best performing methods
of retrieving data. The Database Engine Tuning Advisor was able to recognize that an index with all the columns
referenced by the query, a covering index, would perform best.
Once you've received a recommendation, you should closely examine the proposed T-SQL command. The
suggestions are not always helpful, so you need to evaluate and test them to be sure. Assuming the examined
recommendation looks good, you'll want to apply it. Select Actions
Evaluate Recommendations. This opens a
new Database Engine Tuning Advisor session and allows you to evaluate whether the recommendations will work
using the same measures that made the recommendations in the first place. All of this is to validate that the original
recommendation has the effect that it claims it will have. The new session looks just like a regular evaluation report.
If you're still happy with the recommendations, select Actions
Apply Recommendation. This opens a dialog box
that allows you to apply the recommendation immediately or schedule the application (see Figure
10-10
).
Figure 10-10.
Apply Recommendations dialog box
If you click the OK button, the Database Engine Tuning Advisor will apply the index to the database where you've
been testing queries (see Figure
10-11
).
