Behind the Scenes on OPTIMIZE_FOR_SEQUENTIAL_KEY

Rmwin Thanks for the feedback! 

Let me address your comment about page splits first. When a new page is added to the end of any level of an index (could be leaf or intermediate level), we do not move any rows around, we simply add a new empty page. The only time pages are left 50% full is when a new page is added in the middle of the level. You can see this in action by creating an empty table with a clustered index on a sequential key and inserting just enough rows to fill one page, then add one more row and look at the resulting pages. You'll see three pages: the first full page, the new empty page, and a new root page that points to the two other pages which now form the leaf level of the index. If you do this again with a clustered index on a non-sequential key such as a uniqueidentifier, after the page split happens you'll see that both the leaf pages contain 50% of the rows. I can post a demo script for this later if it would be helpful.

As I mentioned, we did discuss (at length, I assure you :)) many other options for solving this problem. While a Hekaton merge table seems like a straightforward solution, there are actually a lot of complexities to manage such as different transactional semantics, different indexing etc. that make this challenging to do automatically. This is something you can actually implement yourself, and we do have some customers that use a similar solution (it's often called the shock absorber). 

While you may not have encountered this problem frequently, we do see many customers facing this issue, particularly on servers with a high core count (e.g. 128 cores or more). The hope is that this new flow control mechanism may also be used to improve throughput in other areas of the engine that have a tendency to form convoys under contention.