Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
So, 17 indexes is fast and 18 will be slow? Is that what you are saying? –  ypercube Feb 14 '13 at 8:57
    
those 17 are also not fast...the 18th will slow down more .. –  user20160 Feb 14 '13 at 9:00
7  
What to do? First, include here the following: CREATE TABLE statement, index definitions, typical queries you try to accelerate with all those indexes, probably coupled with index usage statistics to see whether all those make any sense. All these are needed to assess whether you need this many indexes ('yes' is answer only in rare cases) and then being able to determine which ones to drop. Anyway, 'slow' means what exactly? An example would be useful here again. –  dezso Feb 14 '13 at 9:28
    
this question's a bit too open ended here for the se format. I did write an answer that should point you in the right direction though. –  DForck42 Feb 14 '13 at 20:01
    
If you edit in the details dezso mentions we can consider reopening? –  Jack Douglas Feb 16 '13 at 8:42
add comment

closed as not a real question by Paul White, Jack Douglas Feb 16 '13 at 8:41

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers

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....

share|improve this answer
add comment

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.

share|improve this answer
add comment

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