Share via

SQL 2016 documentation

John Hammond 0 Reputation points
2024-08-01T16:48:14.62+00:00

When looking at the 2016 documentation in the create index options the option OPTIMIZE_FOR_SEQUENTIAL_KEY is listed however in later in the docs it states below. This is extremely confusing as when you look it up unless you read thru all the options descriptions you will not know that it doesnt apply to 2016. Please consider rectifying if you can.

Sequential keys

Applies to: SQL Server (Starting with SQL Server 2019 (15.x)) and Azure SQL Database

Last-page insert contention is a common performance problem that occurs when a large number of concurrent threads attempt to insert rows into an index with a sequential key. An index is considered sequential when the leading key column contains values that are always increasing (or decreasing), such as an identity column or a date that defaults to the current date/time. Because the keys being inserted are sequential, all new rows will be inserted at the end of the index structure - in other words, on the same page. This leads to contention for the page in memory which can be observed as several threads waiting on PAGELATCH_EX for the page in question.

Enabling the OPTIMIZE_FOR_SEQUENTIAL_KEY index option enables an optimization within the database engine that helps improve throughput for high-concurrency inserts into the index. It is intended for indexes that have a sequential key and thus are prone to last-page insert contention, but it may also help with indexes that have hot spots in other areas of the B-Tree index structure.

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories


2 answers

Sort by: Most helpful
  1. Erland Sommarskog 134K Reputation points MVP Volunteer Moderator
    2024-08-01T21:42:02.3066667+00:00

    As you may guess maintaining the SQL Server documentation is a complex task. Not only are there different versions of SQL Server like SQL 2016, 2017 etc. But then you have Azure SQL Database, Synapse, Fabric warehouse and the list just goes on.

    I have also noticed that if I select the documentation set for SQL 2019 that I see features that were added in SQL 2022. I can't say I like it, but I can see the challenge.

    I have some contacts with the SQL Server writers, so I will alert them about this thread. Just don't expect anything to happen. Not the least when SQL 2016 is a version that no longer is mainstream support.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments No comments

  2. Olaf Helper 47,616 Reputation points
    2024-08-01T16:57:00.82+00:00

    I don't get the point of your post; sorry.

    Several enhancement was intorduced in SQL Server 2016 SP 2; which build are you running?

    SELECT @@VERSION
    

    Was this answer helpful?

    0 comments No comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.