Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

DTA Summary Results

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.

share|improve this question
3  
Can you expand on the nature of the database/workload? If you run the trace through ClearTrace, how many unique query signatures does it identify? –  Mark Storey-Smith Feb 7 '13 at 8:31
    
@MarkStorey-Smith I haven't done a true analysis or used that tool, but I can very confidently say that there are a LOT of unique queries going on in there. This is a database in a line-of-business app for energy companies ranging from invoicing, quotes, customer management, and many other things. If it had 500k queries, I'd guess there would be at least 50k unique ones in there. It's quite a large system with a lot of different features. Just not a ton of users. –  Jaxidian Feb 7 '13 at 16:52
    
Also, if it had 50k unique queries, I'd say ~40-45k are derivatives of 5k "base" queries (just different WHERE clauses when users filter/sort tables of data by various columns and values). Not sure if this matters. –  Jaxidian Feb 7 '13 at 16:54
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
While I don't possess the skills to intelligently build indexes for our system, I was going to use what I do know to "interpret" the recommendations from DTA (not just blindly following them). I was going to go with maybe ~10% of its recommendations, re-analyze one more time to see if there are any obvious ones left to get, throw all of that against the wall, and see what sticks. I'll check out BlitzIndex to see what it thinks... –  Jaxidian Feb 7 '13 at 18:14
2  
That is exactly what you should do when index tuning! You just need to find the right tool with recommendations you can work with. DTA is rarely that tool. –  JMarx Feb 7 '13 at 18:16
    
I've been trying to do this but that tool apparently isn't very useful after our nightly maintenance scripts run. Now that I've had a couple days and have disabled those maintenance scripts to keep usage stats around, this is proving to be a great tool! Thanks for recommending it! –  Jaxidian Feb 8 '13 at 19:02
add comment

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)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.