Using DTA vs. evaluating DMVs?

Question

Currently I am confronted with a production SQL Server database where someone had added almost all missing index proposals from DTA.

Form How to determine if an Index is required or necessary I have learned, that there are DMV views, which can be used to defer information about actual index usage as well as missing indexes.

The script from Jason Strate only uses the current information from that views, while Fun for the day - Automated Auto-Indexing! saves some information from those views into tables.

DTA on the other side requires planning when to run the profiler, select what to profile and has some impact on performance while running.

My impression is that using DTA as first step tuning tool is simply a waste of time and as its results don't cover the complete database usage, are hard to interpret by novices and can lead to adding too much indexes with negative impact on write performance, while Data collection via DMV requires little preparation and covers nearly the whole usage of the database since the last restart.

My question focuses on which strategy to propose to the management. I want to focus on evaluation the DMV views in the first step and ignore DTA completely.

Answer 1

I think it's smarter to build something that monitors the DMVs and tracks their data over time (maybe taking a snapshot of the index usage and missing index DMVs every night - might make sense to include procedure stats as well) than to run some tool for a short period whenever you think to do so. You'll get a much better picture over a longer period not only what they are right now but also how they've changed over time.

Be careful not to drop indexes that seem unused but may be hooked to some report that only gets called once or twice in a business cycle - it could be more important than its frequency might suggest.

Answer 2

DTA helps for adding indexes but not removing indexes. If you're faced with a database that already has more than, say, 5 indexes per table, the DTA isn't the answer. You'll want to use the DMVs to evaluate usage of existing indexes.

Here's a script with a video tutorial I did a while back:

http://www.toadworld.com/platforms/sql-server/w/wiki/10062.find-indexes-not-in-use.aspx

Answer 3

According to this ("It" is a DMV-based index query)

• It's not as smart as the Database Engine Tuning Advisor. If you have identified a query that you know is expensive and needs some help, don't pass up DTA just because the missing index DMVs didn't have any suggestions. DTA might still be able to help.

• The missing index DMVs don't take into account the overhead that new indexes can create (extra disk space, slight impact on insert/delete perf, etc). DTA does take this into account, however.
• The "improvement_measure" column in this query's output is a rough indicator of the (estimated) improvement that might be seen if the index was created. This is a unitless number, and has meaning only relative the same number for other indexes. (It's a combination of the avg_total_user_cost, avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.)
• The missing index DMVs don't make recommendation about whether a proposed index should be clustered or nonclustered. This has workload-wide ramifications, while these DMVs focus only on the indexes that would benefit individual queries. (DTA can do this, however.)
• Won't recommend partitioning.
• It's possible that the DMVs may not recommend the ideal column order for multi-column indexes.
• The DMV tracks information on no more than 500 missing indexes.

No experience of this and don't know of any alternate reference, sorry.

I tend to stick with DMVs because DTA you needs db_owner. On prod (as a Developer DBA, not a Production DBA) I don't have permissions to run this...