Index are created on table or view that provides a fast way of retrieving the data based on the defined columns.
These keys (Clustered or Non-clustered) are in B-tree level structures to easily find the rows associated with the key column values quickly.
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.
--- What could happen if you falsely remove the index ---
Stored procedure or queries that use index hints will ERROR if the index no longer exists. - This is the reason why SQL Server allows duplicate indexes to be created in general.
Plan guide might no longer work - they will be invalidated and a new plan will be used
Plan could change - whether good or bad
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)
Sources and good reads:
IEPTO2 - SQLSkills immersion event docs (Recommended Training "https://www.sqlskills.com/sql-server-training/ ")
https://www.sentryone.com/blog/aaronbertrand/sql-sentry-v10-index-analysis