The latch convoy problem arises when there is contention for a specific data page, particularly the last page at the end of an index. This contention can result in high waits and reduced performance for insert operations. Microsoft introduced the OPTIMIZE_FOR_SEQUENTIAL_KEY index option in SQL Server 2019 as a solution for this problem.
Recently, I finally encountered and successfully resolved the “last-page insert contention” or “latch convoy problem” in a production SQL Server environment. This issue, although rare, can have a significant impact on performance when it occurs.
[…] And then – the customer decided to try out the OPTIMIZE_FOR_SEQUENTIAL_KEY index option (embarrassingly, that recommendation didn’t come from me).
The index that they had to recreate was massive. It halted their entire environment for half an hour because the affected table was so central to their operation
But, eventually, it was completed.
And then – over the period of an hour or so – the contention got reduced… more… and more… lower, and lower… until there was no contention anymore!
Read the full blog post at the official Madeira Data Solutions Blog
Reflecting on this experience, I realized that recognizing the Last Page Insert Contention scenario is far from straightforward. It’s not something you can discern by merely examining SQL Wait graphs or resource consumption. The key lies in analyzing the contention per resource, identifying the specific table and page affected—the last page at the end of the index. It was a humbling experience, one that taught me to be better prepared and not underestimate the potential impact of this elusive problem.