What to do with a table with too many indexes?

Question

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.

Answer 1

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.

Answer 2

If the table doesn't have many inserts or updates, then it's basically just wasted space (+ maybe longer maintenance / backup time)

But for every insert and update in case of the indexed fields, then each of the indexes must be maintained, and that's then slowing down the operations.

At least I would recommend looking first at the index usage statistics report in SSMS. It will tell you if the indexes are being actually used or not. The figures are since the last time the server was started. If the indexes aren't being used, then it's quite safe to drop them, the only issue of course being index hints in SQL statements.

After this I would also look if the indexes are more or less duplicates of each other. 50 indexes sounds so much that I would guess there's a lot of indexes that have (almost) the same fields in different order. When dealing with composite indexes, remember also that there can be statements that only use one or more of the leading columns of an index, and that's why not all statements can use the other index even if it has the exact same columns but in different order.

You can of course drop everything, but depending on what environment this actually is and how many rows there are in the table, it could cause quite a bad situation due to locking, blocking and deadlocks.