Should I rely on Database Engine Tuning Advisor for creating indexes?

Question

How should I rely on SQL Server 2012 Database Engine Tuning Advisor for creating indexes and statistics? I find more often than not, it generates overly complex indexes that (I think) would be more costly in index maintenance than the benefit of having this index.

If I cannot solely rely on Database Engine Tuning Advisor, is there a straightforward method? A book an article perhaps?

The missing index DMVs are not particularly useful because I am trying to think of indexes for brand new tables.

Answer 1

It can certainly be a good place to start. I used to rely on it pretty heavily. Another good way to do it is to look at the execution plan for your query. Often I have found the suggestions that it provides are incredibly useful. Another tool to help with indexing is to run this

SELECT
mid.statement
  ,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,OBJECT_NAME(mid.Object_id),
  'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

This should give you an idea of some indexes you could be missing. Hope that helps.

Answer 2

Database Engine Tuning Advisor, like the DMVs such as sys.dm_db_missing_index_details and sys.dm_db_missing_index_groups give you a good place to start, as does the dmv sys.dm_db_index_usage_stats, to see which indexes are not being utilized and could be dropped.

However, these tools are never going to be as good as a skilled DBA making rational descisions about what indexes are really required. For example, DTA might recommend dropping an index that is hardly ever used and is expensive to maintain, but with business knowledge, you know that this index is key to running the monthly payroll.

In short - Use these tools to get a head start, but rely on your business knowledge, and performance troubleshooting skills as the final descision maker.