Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?

Question

Our databases consist of lots of tables, most of them using an integer surrogate key as a primary key. About half of these primary keys are on identity columns.

The database development started in the days of SQL Server 6.0.

One of the rules followed from the beginning was, as you find in these Index Optimization Tips:

Avoid creating a clustered index based on an incrementing key.
For example, if a table has surrogate integer primary key declared as IDENTITY and the clustered index was created on this column, then every time data is inserted into this table, the rows will be added to the end of the table. When many rows will be added a "hot spot" can occur. A "hot spot" occurs when many queries try to read or write data in the same area at the same time. A "hot spot" results in I/O bottleneck.
Note. By default, SQL Server creates clustered index for the primary key constraint. So, in this case, you should explicitly specify NONCLUSTERED keyword to indicate that a nonclustered index is created for the primary key constraint.

Now using SQL Server 2005 and SQL Server 2008, I have the strong impression that the circumstances changed. Meanwhile, these primary key columns are perfect first candidates for the clustered index of the table.

Answer 1

The myth goes back to before SQL Server 6.5, which added row level locking. And hinted at here by Kalen Delaney.

It was to do with "hot spots" of data page usage and the fact that a whole 2k page (SQL Server 7 and higher use 8k pages) was locked, rather then an inserted row

Found authoritative article by Kimberly L. Tripp

"The Clustered Index Debate Continues..."

Hotspots were something that we greatly tried to avoid PRIOR to SQL Server 7.0 because of page level locking (and this is where the term hot spot became a negative term). In fact, it doesn't have to be a negative term. However, since the storage engine was rearchitected/redesigned (in SQL Server 7.0) and now includes true row level locking, this motivation (to avoid hotspots) is no longer there.

The link in lucky7_2000's answer seems to say that hotspots can exist and they cause issues. However, the article uses a non-unique clustered index on TranTime. This requires a uniquifier to be added. Which means the index in not strictly monotonically increasing (and too wide). The link in that answer does not contradict this answer or my links.

On a personal level, I have woked on databases where I inserted tens of thousands of rows per second into a table that has a bigint IDENTITY column as the clustered PK.

Answer 2

To sum it up, in modern SQL Server versions a clustered key on an identity column is the preferred option these days.

I said preferred, not required. For normal applications that make up 98% of the databases in the world a clustered key on an identity column works just fine.

Answer 3

Kimberly Tripp has a fantastic blog post about just this topic. I could paraphrase, but trust me, I wouldn't do it justice. Have a read.

OK, so the final criteria I look for in a clustering key is: an ever-increasing pattern!
If the clustering key is ever-increasing then new rows have a specific location where they can be placed. If that location is at the end of the table then the new row needs space allocated to it but it doesn't have to make space in the middle of the table. If a row is inserted to a location that doesn't have any room then room needs to be made (e.g. you insert based on last name then as rows come in space will need to be made where that name should be placed). If room needs to be made, it's made by SQL Server doing something called a split. Splits in SQL Server are 50/50 splits – simply put – 50% of the data stays and 50% of the data is moved. This keeps the index logically intact (the lowest level of an index – called the leaf level – is a douly-linked list) but not physically intact. When an index has a lot of splits then the index is said to be fragmented. Good examples of an index that is ever-increasing are IDENTITY columns (and they're also naturally unique, natural static and naturally narrow) or something that follows as many of these things as possible – like a datetime column (or since that's NOT very likely to be unique by itself datetime, identity).

While there, check out some of her other posts on the topic of clustering keys. There is a good wealth of knowledge to be had from her site.

Answer 4

Check this post:

Monotonically increasing clustered index keys can cause LATCH contention by Amit Banerjee of Microsoft.

Creating a clustered index based on an incrementing key may create hot spots that are bad for performance.

Answer 5

Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?

It's still a relevant consideration, perhaps more so now than ever as core counts increase. It would be much too strong to say you should always avoid this practice.

SQL Server 2019 introduced the OPTIMIZE_FOR_SEQUENTIAL_KEY index option to help mitigate the page latch contention and latch convoy behaviour that can occur. It is not a complete solution.

There is rarely a single consideration that dominates all others. If you choose a non-sequential index key, you may have to accept page splits and lower average data density as a trade-off for the potentially increased scalability.

Pam Lahoud of Microsoft wrote an excellent article explaining why OPTIMIZE_FOR_SEQUENTIAL_KEY is necessary and how it works, included in the references below:

• Behind the Scenes on OPTIMIZE_FOR_SEQUENTIAL_KEY by Pam Lahoud
• PAGELATCH_EX waits and heavy inserts by Pedro Lopes
• Resolve last-page insert PAGELATCH_EX contention in SQL Server in the product documentation
• Sequential keys in the CREATE INDEX documentation