SQL Server Performance

Mulitple indexes on one column

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by cnikirk, Jul 26, 2007.

  1. cnikirk Member

    I inherited a database that has a non-clustered index on a column and also has a clustered index on the same column. This table gets hit with a lot of inserts. I have noticed tons of locking lately. The locking is so severe that all reads and writes stop working.

    Due to the heavy inserting, I'm assuming the clustered index should be dropped. Any opinions on this?

    Thanks.
  2. bradmcgehee New Member

    If I understand correctly, the same column in the same table has both a clustered and a non-clustered index on it, AND these indexes are single indexes, not part of a composite index?

    If this is the case, your best bet is to drop the non-clustered index first and see what happens. Or, better yet, do an execution plan common queries that use this index and see which index they are using, keep that one, and delete the other.

    Your locking may also be caused by other issues, but this is a good place to start.

    --------------------------------------------------------------
    Brad M. McGehee, SQL Server MVP
    Technical Editor/Moderator www.SQL-Server-Performance.Com
    Director of DBA Education for www.Red-Gate.Com
    www.sqlbrad.com
    www.sqlHawaii.com
  3. ndinakar Member

    quote:Originally posted by bradmcgehee

    If I understand correctly, the same column in the same table has both a clustered and a non-clustered index on it, AND these indexes are single indexes, not part of a composite index?

    If this is the case, your best bet is to drop the non-clustered index first and see what happens. Or, better yet, do an execution plan common queries that use this index and see which index they are using, keep that one, and delete the other.

    Your locking may also be caused by other issues, but this is a good place to start.

    --------------------------------------------------------------
    Brad M. McGehee, SQL Server MVP
    Technical Editor/Moderator www.SQL-Server-Performance.Com
    Director of DBA Education for www.Red-Gate.Com
    www.sqlbrad.com
    www.sqlHawaii.com

    Hi Brad

    While I agree with you that the OP should drop the Non-clustered index first to see the performance diff, I think SQL always tries to use a clustered index if it can (for the same column). So most likely all the queries would be using the clustered index (under the assumption that this is the only column in the index).

    ***********************
    Dinakar Nethi
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  4. cnikirk Member

    You guys hit the nail on the head. I'm going to drop the clustered first to see if that helps. Thanks.
  5. ndinakar Member

    quote:Originally posted by cnikirk

    You guys hit the nail on the head. I'm going to drop the clustered Non-clustered first to see if that helps. Thanks.

    We meant dropping the NON-clustered index.

    ***********************
    Dinakar Nethi
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  6. satya Moderator

    While I agree with what Brad suggested, you have to see how the insert queries are handled. Say if the process can be controlled with smaller batches you would definetly have a gain in performance.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  7. merrillaldrich New Member

  8. MichaelB Member

    I have not heard how wide this column is and how incremental. I would, as a matter of design, always have a incrementing field (read: identity) as your clustered index field and have a non-clustered index on this field. since you complain inserts are killing it, it sound like a bad field to order the table by. Just my 2 cents.
    :)

Share This Page