Database Tuning Advisor Index Recommendations

Question

How does DTA make an index recommendation? What is the technology behind that? If we can use same on our side then we can shorten the whole process of Tuning recommendation:

1. creating trace file using SQL Profiler
2. Use that trace file in DTA
3. Then look for recommendations
4. Follow the recommendations & see the results in the Query Execution Plan.

I just wanted to know if it is there any way to create a predictable index.

Answer 1

How does DTA make an index recommendation? What is the technology behind that?

You can find the answers in a detailed paper on the Database Tuning Advisor published by Microsoft (click on the PDF link on that page to download the complete document).

The missing index DMVs are not a good substitute for a full DTA analysis. The DMVs show only very limited information opportunistically gathered during query optimization. DTA performs a much more thorough analysis, though you should not automatically create every index it suggests. You need to assess each recommendation on its merits.