Results 1 to 5 of 5

Thread: Remove clustered index???

  1. #1
    Join Date
    Nov 2004
    Posts
    66

    Remove clustered index???

    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!

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    I'll change cover index to non-clustered one and put clustered index on another single column.

  3. #3
    Join Date
    Nov 2004
    Posts
    66
    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.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Depends on how you use the table.

  5. #5
    Join Date
    Aug 2006
    Posts
    57
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •