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!