17

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.