Last page contention happens when many concurrent threads are trying to modify the same page of a rowstore index (clustered or non-clustered). Only one thread can add or change data on a page at a time. While doing so a tread will take out an exclusive latch on the page making sure no other data changes. The name is given as “last page cointention” since the page with contention is often the last page in the rowstore index for indexes with high insert activity due to the use of a sequential key for the index.
The Last page contention issue has the oddity that it occurs more frequently on systems with a high core count, and thus have more concurrent worker threads available to do inserts on the same table at the same time. Microsoft has an article describing the issue and what might be done about it.
There at several methods of resolving this issue, and the article mentions 6 methods. The common trait they all share is that they all strive to spread concurrent inserts over several pages. The most straight forward way of doing this is to make sure you do not have a sequential sorting key for your indexes, even the clustered index. Examples of methods to consider are compound keys where the leading column is not sequential, GUID keys or abolish the keys altogether for your table resulting in a heap. Take care with your non-clustered indexes to not introduce contention there. If any of the non-clustered indexes need to lead with a sequential key, make the index as narrow as possible, preferably only on that key itself. Consider having a non-clustered primary key as well if you need it to be sequential.
So, I mentioned the H word. That is not to say heaps are always the answer to last page contention, far from it, but it should be considered as a proper alternative. However, heaps have some extra considerations and caveats to consider before taking that plunge. The topic of heaps deserves its own post so stay tuned for my thoughts on heaps.