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

share|improve this question
    
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
add comment

1 Answer

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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