Traditionally, we want our Clustered Index to have the following attributes:
Narrow: So that our clustered index and the non-clustered indexes that point to it will be as slim as possible
Static: So that we don’t generate fragmentation in the clustered index and we don’t have to update the non-clustered indexes
Ever-increasing: So that we don’t generate a lot of page splits and fragmentation when we insert rows into the index
For the vast majority of systems, this will be a very good choice. The problem is that for a certain scale, this isn’t good enough. That’s because each insert has to acquire a latch on the last page of the index, and that hurts insert performance.
Watch the video to see how latch problems like this can be solved: