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.

How should I rely on 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? Thanks!!

share|improve this question
    
Assuming you mean SQL Server? –  FreshPhilOfSO Sep 27 '12 at 15:32
1  
Have you thought about using the missing index DMVs? For instance sys.dm_exec_missing_index_groups? see toadworld.com/platforms/sql-server/w/wiki/… for more info. –  Max Vernon Sep 27 '12 at 15:32
    
Yes SQL Server 2012. Sorry I should have been more specific. –  Dina Sep 28 '12 at 18:22
    
The reason DMVs are not particularly useful is because I am trying to think of Indexes for brand new tables. –  Dina Sep 28 '12 at 18:24
add comment

2 Answers

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.

share|improve this answer
add comment

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.

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.