Remove clustered index???

[wilbiew]

I have a table with hundreds of million of rows. This table is one of the frequently accessed tables in an OLTP database. It is not only used for SELECT queries, but records are constantly being inserted into this table.

The application occassionally throws timeout exceptions when attempting to insert records into this table, and one of the main reasons for this may be because the clustered index is a covering index (the index includes all of the columns). That is, every time a row is inserted into the table, SQL Server must re-sort the records according to the clustered index. Correct me if I'm wrong here, but this causes overhead and may delay additional records from being added into this table.

If this is the case, is it then better to NOT have a clustered index on this table and just a non-clustered index? Or, should the clustered index be reduced to one column?

I am also trying different methods, such as removing data from this table. This table can also be partitioned, but this causes administrative overhead and if designed properly, partitioning may not be needed (feel free to disagree, but please let me know why).

How would you resolve such a problem?

Thanks!

[rmiao]

I'll change cover index to non-clustered one and put clustered index on another single column.

[wilbiew]

If I remove the clustered index, is it more expensive for SQL Server to maintain the non-clustered or clustered index? That is, which causes more overhead to maintain for an OLTP system.

[rmiao]

Depends on how you use the table.

[jezemine]

Generally you want to have a clustered index on columns such that data is always inserted at the "end" of the table. that is, such that new rows do not end up in the middle of the clustered index. If they end up in the middle with such a large table, you get fragmentation and timeouts.

I would remove the clustered index and make it non-clustered.

Also I question the need to have an index that has every column in it - seems strange to me to put every column in one index. On the other hand, I haven't seen the query that uses this index, or the table DDL for that matter, so it's hard to say.