• Choose a GOOD clustering key

• Create primary keys and unique keys

• Create foreign keys

>Manually index your foreign keys with nonclustered indexes

• Create nonclustered index for highly selective queries.

Only after these steps are done then you can consider adding index. Make sure to understand the ACTUAL workload as well as query properties.

• Monitor query performance.

> By duration and IO 

> Review cumulative costs of frequently executed queries.

• sys.dm_exec_query_stats (review query_hash)

• sys.dm_exec_procedure_stats (this is cumulative)

• Identify key performance problems

• Consider wider indexes through testing and analysis.

> Evaluate the impact to OLTP as a wider indexes are added

> Evaluate disk/memory costs for wider indexes 

• Eliminate any dead weight.

> Remove duplicate indexes.

> Consider removal or consolidation of redundant indexes.

• Must have good maintenance strategy.

> Analyze index fragmentation. (Use limited scan "No need for sampled or detailed".)

> Deal with statistics appropriately:

• Rebuild index with equivalent of full scan but does not update other stats.

• Reorganizing indexes does not update statistics at all

• consider column level statistics.

Only after these steps are done then you can consider adding index. Make sure to understand the ACTUAL workload as well as query properties.

Fragmentation depends on many factors:

• Usage patterns and table size

• Impacts to availability (online operation)

> INDEX REBUILD WITH (ONLINE=ON)

• Not if index has LOB column (2012)

• Not if you try to rebuild only a single partition (2014)

> INDEX REORGANIZE (always ONLINE)

• Reorganize always use FULL logging but the amount of log information generated depends on fragmentation

• There are trade off between  rebuilding and reorganizing in terms of log space, disk space, run-time, impact to tempdb and even benefits

---DMV Don't tell everything --- 

• Redundant index must be removed - This duplicate index can still show as used.

• Review manually

> Included Columns of filtered index

> sp_helpindex does not show these columns

• Don'te rely on sys.dm_db_index_usage_stats alone.

• Validate last occurrence:

> Seeks (singleton lookup or range scan)

> Scans (like "select")

> Lookups (bookmark lookup)

> Updates (insert, update & delete)

• The cache is flushed at shutdown same as, if AUTOCLOSE properties is enabled.

• sys.dm_db_missing_index_group_stats (probably the most detailed)

> user_seeks, user_scans

> last_user_seek and last_user_scan are both datetime

> avg_total_user_cost - higher cost determines w/c are costly to the system

> avg_user_impact - improvement percentage when you add the index

• sys.dm_db_missing_index_groups (Tie up together the index details and usegae stats)

• sys.dm_missing_index_details (details the table, key columns and included columns that you should consider)