I have table with 17 Indexes if i add 1 more index for search in two columns [closed]

Question

I have table with 17 Indexes if i add 1 more index for search in two columns insert , update and delete queries will be slow.I dont want them to slow...what to do

Answer 1

Indexes are always a tradeoff of read performance vs write performance. In the end you have to determine which indexes to keep and which ones to discard. This is a tradeoff which has to be guided by your specific needs and there is no single right answer here necessarily.

In general, the first thing you need to do is go through and find out which indexes you can throw out. MS SQL has a decent planner and I would be surprised if all indexes are required for good performance. Once you throw out the indexes you don't need then add new indexes you do.

It is possible though highly unlikely that in fact you do need all these indexes. However to get 17 indexes on a given table, I think you need to be trying to justify so many indexes in the first place....

Answer 2

The answer, as Chris said, is to figure out why you have that many indexes, and which are duplicates or are unnecessary. BrentOzar.com has a couple of videos as to how to clean up indexes.

My first suggestion is to find the unused ones. This video does a good job of explaining how to do this. I run a slightly tweaked version of the code used there:

select
        o.name,
        indexname = i.name,
        i.index_id,
        reads = user_seeks + user_scans + user_lookups,
        writes = user_updates,
        rows = (
                select
                        sum(p.rows)
                    from
                        sys.partitions p
                    where
                        p.index_id = s.index_id
                        and s.object_id = p.object_id
               ),
        case when s.user_updates < 1 then 100
             else 1.00 * (s.user_seeks + s.user_scans
                          + s.user_lookups) / s.user_updates
        end as reads_per_write,
        'DROP INDEX ' + quotename(i.name) + ' ON '
        + quotename(c.name) + '.'
        + quotename(object_name(s.object_id)) as 'drop statement'
    from
        sys.dm_db_index_usage_stats s
        inner join sys.indexes i
            on i.index_id = s.index_id
               and s.object_id = i.object_id
        inner join sys.objects o
            on s.object_id = o.object_id
        inner join sys.schemas c
            on o.schema_id = c.schema_id
    where
        objectproperty(s.object_id, 'IsUserTable') = 1
        and s.database_id = db_id()
        and i.type_desc = 'nonclustered'
        and i.is_primary_key = 0
        and i.is_unique_constraint = 0
        --and i.name not like '%unique%'
--AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
order by 7

I would modify that code and add a where clause to narrow it down to the table you're working on. Flat out remove any indexes that have a reads_per_write of 0, they're not used.

After removing (if any) of the unused indexes, I would examine the ones that have a reads_per_write of 1 or less. These are indexes that are read less than or equal to how much theiy're written to. Figure out why these were added, and if those query could be modified to match an already existing highly used index.

After that comes combining and simplifying indexes. That topic's a bit detailed and complicated for an answer here. The best move forward is to watch the index videos on BrentOzar.com.