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.

  • A Comment. If we have a software, doing different selects on the table, Do I need to create different indexes to handle these different selects? Thanks. – Racer SQL Mar 4 '15 at 17:37
  • 2
    I would start by running sp_BlitzIndex and dealing with the unused ones on a case by case basis. – Kin Mar 4 '15 at 17:42
  • @kin Thank you again. I'm on this site right now looking for an answer. Will test this proc and post results here. – Racer SQL Mar 4 '15 at 17:45
  • 1
    I agree with @Kin, a good place to start would be to determine what indexes aren't being actively accessed, then start finding out which indexes are just duplicates of other already existing indexes, see what you can consolidate and go from there. – Kris Gruttemeyer Mar 4 '15 at 17:45
  • See this, this (and its duplicates), and this. – Aaron Bertrand Mar 4 '15 at 17:47
up vote 5 down vote accepted

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.

  • Awesome. I will study about those glenn berry's querys. But a question. I need to pay attention with the "total reads" collumn. right? if i want to see used indexes. – Racer SQL Mar 4 '15 at 18:05
  • 1
    Exactly. Indexes that are heavily written and seldom read are the most likely candidates for deletion. – spaghettidba Mar 4 '15 at 18:08

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.

  • thanks for your answer. yes we have a lot of updates in those tables. daily. I will study those queries of the spaghettidba's answer. And yes , there's a lot of indexes with same fields. – Racer SQL Mar 4 '15 at 18:08
  • Yes it looks like the same query as the SSMS report, but of course that's just listing those indexes where there's more updates than reads: user_updates > (user_seeks + user_scans + user_lookups) – James Z Mar 4 '15 at 18:33

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.