SQL Server – What to Do with a Table with Too Many Indexes

indexsql serversql-server-2008

I'm the new DBA for this company. I'm seeing some of their tables have a LOT of indexes; for instance some have over 50 or 60.

Is this a good thing? From my research, it seems this is not good for performance.

How would you manage this?

I would like to delete all the indexes and start from 0. I think the developers have been using Tuning Advisor without really understanding indexes.

Best Answer

Probably some of those indexes are used, so deleting them all is not a good idea.

You can review index usage stats and find bad NC indexes with this query (taken from Glenn Berry's Diagnostic Queries):

-- Possible Bad NC Indexes (writes > reads)  (Query 47) (Bad NC Indexes)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id, 
    i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,
    user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
    user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
    ON s.[object_id] = i.[object_id]
    AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
    AND s.database_id = DB_ID()
    AND user_updates > (user_seeks + user_scans + user_lookups)
    AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

You can also find recommended missing indexes using this query (again from Glenn Berry's diagnostic queries):

-- Missing Indexes for current database by Index Advantage  (Query 48) (Missing Indexes)
SELECT DISTINCT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage], 
    migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
    mid.equality_columns, mid.inequality_columns, mid.included_columns,
    migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact,
    OBJECT_NAME(mid.[object_id]) AS [Table Name], p.rows AS [Table Rows]
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
    ON mig.index_handle = mid.index_handle
INNER JOIN sys.partitions AS p WITH (NOLOCK)
    ON p.[object_id] = mid.[object_id]
WHERE mid.database_id = DB_ID() 
ORDER BY index_advantage DESC OPTION (RECOMPILE);

Instead of creating the recommended missing indexes as suggested, try to combine them with existing indexes and find a balance to keep the number of indexes low.

Related Question