Database Reference
In-Depth Information
Summary
As you learned in this chapter, the Database Engine Tuning Advisor can be a useful tool for analyzing the effectiveness
of existing indexes and recommending new indexes for a SQL workload. As the SQL workload changes over time,
you can use this tool to determine which existing indexes are no longer in use and which new indexes are required
to improve performance. It can be a good idea to run the wizard occasionally just to check that your existing indexes
really are the best fit for your current workload. This assumes you're not capturing metrics and evaluating them
yourself. The Tuning Advisor also provides many useful reports for analyzing the SQL workload and the effectiveness
of its own recommendations. Just remember that the limitations of the tool prevent it from spotting all tuning
opportunities. Also remember that the suggestions provided by the DTA are only as good as the input you provide to
it. If your database is in bad shape, this tool can give you a quick leg up. If you're already monitoring and tuning your
queries regularly, you may see no benefit from the recommendations of the Database Engine Tuning Advisor.
Frequently, you will rely on nonclustered indexes to improve the performance of a SQL workload. This assumes
that you've already assigned a clustered index to your tables. Because the performance of a nonclustered index is
highly dependent on the cost of the bookmark lookup associated with the nonclustered index, you will see in the next
chapter how to analyze and resolve a lookup.
