wagnerblog.com

In the years since the storage engine was re-architected (SQL Server 7.0+) there’s been constant debate on how to appropriately choose the clustered index for your tables. I’ve generally recommended an ever-increasing key to use as a clustered index and many find that counterintuitive. The primary reason people feel it’s counterintuitive is that it creates a hotspot of activity. [If “hotspot” is not a familar term - a hotspot is solely an active place within your table.] Hotspots were something that we greatly tried to avoid PRIOR to SQL Server 7.0 because of row 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 row level locking, this motivation (to avoid hotspots) is no longer there. In fact, the opposite is true. Hotspots (specifically hot PAGES not hot ROWS) can be very beneficial because they; minimize the number of pages needed in cache, improve the likelihood of the required page already being in cache and in general, they minimize the overall amount of cache required. So, this is why many of us (SQL Server consultants) have changed our recommendation on where to create the clustering key. Instead of focusing on range queries we now focus on placing the clustering key on an ever-increasing key. In earlier releases focusing on range queries for the clustered index reduced hotspots for insert/update and this in fact was the PRIMARY motivation to choose them, NOT range query performance! But - there are even MORE reasons to choose an ever-increasing key and they are based on internals as well. These internals are based on the significant changes made in the storage engine for 7.0+.

Read Article.