0

I have performance problems on tables that have a CLUSTER INDEX in INSERT's statements. If I change the index from CLUSTER to NON-CLUSTER then the performance is so faster.

Can I have all the tables of my sistem with only NON-CLUSTER INDEXES? In which cases the use of CLUSTER index on a table is really important or necessary?

Thanks,

Rod

markp-fuso
  • 28,790
  • 4
  • 16
  • 36
RGS
  • 23
  • 1
  • 3
  • if your table is using `allpages` locking and the clustered index is non-unique, inserts can suffer performance degradation if there are a lot of rows with duplicate (clustered index) keys ... this would be due to excessive overflow pages; see [this recent answer explaining overflow pages](https://stackoverflow.com/a/51310029/7366100) for more details – markp-fuso Aug 17 '18 at 23:07

1 Answers1

1

Do you have many reads from that table? Without getting deep in to page splits or index fragmentation, if you have low or no reads on that table, you can get away without clustered index. If you do have a lot of reads, then what ever clustered index you have (I am guessing it's a GUID), sql server make inserts in the middle somewhere, not in the end of the table/index and it leads to all kinds of extra operations. You can consolidate your clustered index, so insert would happen in the end, like using IDENTITY or SEQUENCE

Ruslan Tolkachev
  • 634
  • 4
  • 9
  • The tables have many readings but if I create a CLUSTER index (instead of NON-CLUSTER) the performance of the inserts on the tables is affected too much. I have looked at articles that say it is good that the CLUSTER index is defined on the primary key and other articles that say it is bad, because of the number of split's that occur on the last page .... What is the most convenient? Thanks again, Rod – RGS Aug 17 '18 at 21:41
  • 1
    99 percent of times CLUSTERED INDEX is beneficial, the question is - is it on the write column(s) and what data type you used for it. There is no set in stone rule that you can follow and it will work for you - it is always on very individual bases. The way all the indexes and tables used together - meter the most. It could be that you have page splits and that's why inserts are slow, you can set your FILL FACTOR settings in order improve in that regard. But if you are removing CLUSTERED INDEX in SQL Server systems - 99.9% you are doing something wrong. – Ruslan Tolkachev Aug 17 '18 at 22:15