Monotonically increasing clustered index keys can cause LATCH contention

In this post, I shall explain why monotonically increasing or decreasing clustered index key columns can create hot spots for your index pages with the help of an example. A hot spot is simply an active portion of the table which is accessed more frequently than the rest of the table. A general thumb-rule (No there is no official documentation around this): If you have a monotonically increasing column for a table that is accessed very frequently for insert operations (eg. Transaction/user activity tracking table), then creating a clustered index on that particular column can lead to performance bottlenecks.

In the example below, I have created a database which has a tblUsers table and a tblTransactions table.

Table definitions

create table tblUsers

(userid int identity(1,1) primary key,

username varchar(255))

create table tblTransactions

(TranTime datetime,

username varchar(255),

sessionid int)

I have a stored procedure in the database which tracks all user activity in the tblTransactions table. The SP definition is as below:

create proc sp_latchtest

@id int

as

begin

insert into tblTransactions (TranTime,username,sessionid)

values (getdate(),'user'+CAST(@id as varchar(3)),@@spid)

end

Now I shall use ostress to generate concurrent sessions against the database to simulate user activity.

Command: ostress -E –S<server name> -d<database name> -Q"EXEC sp_latchtest 155" -n400 -r200 –q

My clustered index for the tblTransaction table is as follows:

create clustered index cidx_trantime on tblTransactions (TranTime)

While I am running the ostress session to generate 400 concurrent connections and execute the Stored Procedure 200 times, I shall also be running the SQL Server Perf Stats script to capture blocking statistics on the SQL Server instance. After inserting 400,000 rows in the tblTransactions table, I imported the SQL Server Perf Stats script output into SQL Nexus to perform post mortem analysis on the small test that I had executed on the server.

You will see from the above table (SQL Nexus: Bottleneck Analysis report), that the biggest bottleneck is the Page Latch(es). From KB822101, the definition of the above latch categories is as follows:

Buffer (BUF) latch: The buffer latches are used to synchronize access to BUF structures and their associated database pages. The typical buffer latching occurs during operations that require serialization on a buffer page, (during a page split or during the allocation of a new page, for example). These latches are not held for the duration of a transaction. These are indicated in the master.dbo.sysprocesses table by the PAGELATCH waittypes.

Non-buffer (Non-BUF) latch: The non-buffer latches provide synchronization services to in-memory data structures or provide re-entrancy protection for concurrency-sensitive code lines. These latches can be used for a variety of things, but they are not used to synchronize access to buffer pages.

If you collect a Perfmon during that for the performance object SQL Server:Latches, you will find that the Latch Waits/sec and Average Latch Wait Time (ms) counters show higher values than the observed values on the server.

Now, to the explanation of why we are experiencing such latch waits. Since we have a clustered index defined on the TranTime column which will always be monotonically increasing as I am using the current time to insert a new value into the table, SQL Server will always insert records on the last page of the table as the data is sorted in increasing order. So, this creates HOT pages on your table when performing the inserts as the IAM/PFS page and the last page for the table needs to be latched to perform the operations requested by the Stored Procedure. The table below gives the average wait time for each of the wait resources encountered during my test:

The pages referenced above are index pages and the reason the latch waits are occurring is that all the insert operations on the tblTransactions table are accessing the same index page for recording the changes made by the stored procedure. This is just a simple test. You can imagine what would happen if the stored procedure performing the inserts was part of some complex business logic, then the application performance would be drastically affected. Now if I change the clustered index to be on the sessionid column and change the fillfactor for the table to 70, then I will see a big difference. The reason for this is that the incoming concurrent inserts will no longer access the same IAM/PFS page(s). The page modifications will now be distributed based on the sessionid (which in my case will be the SPID). And two concurrent sessions cannot have the same SPID. If you look at the wait statistics table from the SQL Nexus report, you will notice a significant change:

The total wait time now has reduced to 62 ms from 189 ms in my previous test. The reduction of the waittime is by one-third which can significantly boost performance where wait times recorded are much higher.

Agreed that choosing a monotonically increasing clustered index key can help you reduce the fragmentation of the table but the choice of choosing such a key would depend on workload that your database table receives. There are many discussions on the web debating on this topic (both for and against) but if your workload involves a lot of inserts which has a similar design as mentioned above, then you would need to reconsider the table design as this can cause a serious bottleneck in your environment.

There was a fix KB940545 introduced in SQL Server 2005 which addressed some parts of the contention issue but the fact of the matter remains that for monotonically increasing clustered index keys, all concurrent inserts will end up accessing the same page due to the nature of the data.

System Specifications used for the test:

Operating System: Microsoft Windows Server 2008 R2 Enterprise (x64)

Machine: Hewlett-Packard HP Z800 Workstation

Processor: 2 Quad Core [Intel(R) Xeon(R) CPU E5506 @ 2.13GHz, 2128 Mhz, 4 Core(s), 4 Logical Processor(s)]

RAM: 16.0 GB

Hard Disk: Barracuda 7200.12 SATA 3Gb/s 500GB Hard Drive

SQL Server: SQL Server 2008 R2

Amit Banerjee,
SEE, SQL support