What am I doing wrong with the Database Tuning Advisor?

Question

My background: I'm a dev/architect, not a DBA. Sorry!

So we have a ~400 table 75GB database. I ran Profiler for ~24 hours ("Tuning" template minus LoginName) and have ~7GB of usage recorded in a trace file. I then ran the Database Engine Tuning Advisor on this trace file (no partitioning, keep clustered indexes, PDS Recommend: Indexes) against our production database (after hours). I gave it ~4 hours to analyze. And here are the summary results I got:

The things that stood out here to me that seem "wrong" are:

1. It only tuned ~9k out of ~530k queries
2. It recommended I drop a ton of indexes (in fact, most of them)
3. It recommended I create 0 indexes

Does this sound right to you guys? I expected it to drop most of my indexes but then to create a ton of new indexes. Also, if it takes 4 hours to analyze 9k queries, is it even feasible for me to get this to consider a normal day's worth of usage? Compared to most large databases, ours is fairly light on consumption (~50 users total).

I think I'm either misunderstanding something or am simply doing something wrong.

Answer 1

I wouldn't bother with DTA. It's a whole lot of work for results that are sketchy at best. Everyone that gets into index tuning eventually realizes it's both a science and an art, and there's a lot to it. You can use tools to get recommendations, but implementing those recommendations blindly almost always results in issues down the road.

Instead, I'd give BlitzIndex a try. It'll take advantage of the statistics SQL Server already maintains to determine where you can improve your indexing (and does so within seconds). It also provides very accessible documentation on how to use the results. Every result includes a link to the pertinent doc. This is how I maintain my indexes these days.

Answer 2

The BlitzIndex tool that @JMarx suggested is working great! However, I'm also finding this additional script to make some good suggestions as well. Not necessarily using all or even most of its suggestions, but cherry-picking from the top is proving very useful!

SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
  '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

(source)

Another piece of info I've discovered is that both BlitzIndex and this script are worthless after a few events happening:

If you’re running SQL Server 2008 R2 or prior, this is index usage since either of the following, whichever is more recent:

1. When the index was created
2. When SQL Server last started

If you’re running SQL Server 2012, this is index usage since any of the following:

1. When the index was created
2. When SQL Server last started
3. When the index was rebuilt (specifically the rebuild command not reorganize)

(source)