I am participating in a new big project which is going to use Sql Server 2012. There will be 10-15 Java servers connected to that database. We are using Hibernate 4.3 as an ORM.
I am investigating what type of a primary key to use to maximize performance. In the previous projects (for Sql Server 2005) I usually used id column with automatically generated identity and a clustered index over that column.
However, in Sql Server 2012 sequences were introduced. From what I have already read I know that:
- Sequences should be created with CACHE setting (for example CACHE 1000)
- Sequences should be created with INCREMENT BY > 1 setting (for example INCREMENT BY 50). That way Hiberante is going to use pooled algorithm
What worries me is that with multiple Java servers inserting to the table, data is not going to be always inserted on the end of clustered index. For example (with INCREMENT BY 50 and current sequence number = 1):
- Server A retrieves next value from sequence. 50 is returned from the sequence.
- Server B retrieves next value from sequence. 100 is returned from the sequence.
- Server B makes an insert with id = 51.
- Server A makes an insert with id = 1. This insert is not on the end of a clustered index.
Do you think that not inserting on the end of a clustered index is going to cause big performance drop? Can I somehow overcame it or in my scenario I should stick to the identity column instead of sequence?
Yes, it will be a HUGE performance hit. It will cause page splits, and possibly recursive page splits. I would suggest sticking to the identity column. There are options, padding, heap table instead of clustered index, etc, put each of those have a subsequent pitfall.