How to Fix Latch Problems when Working with Ascending Keys in SQL Server

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:

#latch #video #indexes