Index Evaluation

Question

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.

Answer 1

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.