I have been doing some index evalaution on a table and this is the first time I have ever looked at how well indexes are performing so and would like some advice on a certain index.

The non clustered index for the last two months has the following statistics

Reads:301550 Writes: 946158

This give a reads per writes ratio of 0.32

I also see these stats for the last few weeks which aren't good

Row lock waits: 7; total duration: 1 minutes; avg duration: 12 seconds; Page lock waits: 103; total duration: 6 minutes; avg duration: 3 seconds; Lock escalation attempts: 32; Actual Escalations: 0.

This index is also on 95 columns which is the whole table, surely an index that contains every column in the table is pointless or am I wrong? What would you experts do with this index? my thought is to remove it, but would like some guidance or confirmation that my conclusion is right or wrong. Thank you.

  • does the NC index INCLUDE all the fields or is it keyed on all the fields? There's a big difference there. – JNK Feb 27 '13 at 15:54
  • You can post the index definition (at least. The table definition would be good to add, too). I guess that the index has a number of columns and then includes all the rest. Something like (col1, col2, ..., colX) INCLUDE (all the rest columns) – ypercubeᵀᴹ Feb 27 '13 at 15:55
  • Thise index definition is too long to post. It contains 10 columns as keys and then the rest of the fields as "include" – kdis Feb 27 '13 at 17:00
  • 1
    85 included columns, I think this is a new record. – Aaron Bertrand Feb 27 '13 at 17:16
  • I thought as much, the indexing has gone out of control. – kdis Feb 28 '13 at 10:02

Instead of having a non-clustered index containing all the columns of your table, you would be better off having a clustered index for the table. Find the column(s) that uniquely identifies each row and use that as your clustering key.

I am assuming that you do not already have a clustered index on that table seeing that it was decided to create a non-clustered index containing all the columns instead.

  • The table does contain a primary key as well and 16 other non clustered indexes. – kdis Feb 27 '13 at 16:59
  • 2
    17 indexes? Wow. Do all 16 non-clustered indexes have 95 columns? – Aaron Bertrand Feb 27 '13 at 17:15
  • Just for case study sake, how many rows are there in this table. – StanleyJohns Feb 27 '13 at 19:40
  • There 3.6 million rows in this table, None of the other indexes have this many columns. There some with 11 and 8 and 7, all the other indexes are terrible as well as they all have a reads per writes ratio under 0.10. With 17 indexes this is the smaller index table that I am tidying up(:o). The order detail table has over 45 indexes on 2.5 million rows! – kdis Feb 28 '13 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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