SQL Server Performance Forum – Threads Archive

Mulitple indexes on one column

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.

---

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/

---

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

---

We meant dropping the NON-clustered index. ***********************
Dinakar Nethi
***********************
http://weblogs.sqlteam.com/dinakar/

---

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.

---

You might also find these interesting: http://blogs.msdn.com/sqlprogrammab…g-overlapping-indexes-in-sql-server-2005.aspx
and for 2000
http://www.sql-server-performance.com/ma_finding_duplicate_indexes.asp

---

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.

---

]]>