Implementation and contraindications for OPTIMIZE_FOR_SEQUENTIAL_KEY

Question

SQL Server 2019 CTP3.1 introduced an optimization to address last-page insert contention. This takes the form of an index option called OPTIMIZE_FOR_SEQUENTIAL_KEY.

One imagines this could be an adaptation of Bw-Tree or Bz-Tree. However, these rely on variable-sized pages whereas the current storage engine requires fixed-size pages.

How is the optimization implemented? How are current B-Tree algorithms altered by this optimization? In what circumstances would I choose not to deploy this option?

Research

A patent for a reverse key approach.

I had a quick look using DBCC PAGE, comparing 2017 to 2019 and 2019 with and without OPTIMIZE_FOR_SEQUENTIAL_KEY on an unique clustered index for an int IDENTITY column. There was nothing that obviously explained the new behaviour. This makes me think its an algorithmic thing, rather than a structural thing, which makes sense.

A blog post from MS.

This feature seems to centre on the detection and avoidance of convoys.

Answer 1

The optimization is implemented by applying flow control on the workers trying to insert to reduce heavy contention and convoys. The idea is based on an unfair insert mutex per scheduler and it can help avoid latch waitlist buildup and convoys as a result. When users opt-in per index with the option, OPTIMIZE_FOR_SEQUENTIAL_KEY, and then we do traffic/flow control on insert workers. Essentially, using an unfair-mutex per scheduler (at a HoBt level where the optimization is on) the flow control will allow only one worker per scheduler to enter the insertion code path that contends for page latches, so that we can reduce convoys and improve the scalability of concurrent inserts. Pam Lahoud provides all this detail in her blog post and I can find nothing in the functional spec or design doc that indicates changes to the current B-Tree algorithms. Other solutions that were considered (one of which was leveraging an optimized form of reverse-key indexes you referenced in your question) have potential drawbacks, and most would involve fundamental changes to the engine. These other solutions may be considered in the future, but this solution provided a significant improvement without fundamentally altering the engine.

You wouldn't want to deploy this where you are not expecting or experiencing last page insert contention.