Last page insert contention is common in a busy SQL Server environment where multiple users are inserting data simultaneously in a table and that table has clustered index on an identity column. When multiple sessions try to add data in the same data page file, the data page becomes "hotspot". As a result, processes queue up to acquire access to the last page, which leads to latch contention, high wait times, and overall reduced performance. Understanding why last page insert contention arises and its mitigation techniques are crucial for handling workloads with rapid inserts.
Figure-1: Last page insert contention
Last Page Insert Contention
A table with clustered index on an identity column creates a sorted data structure. As a result, newly inserted rows will be stored at the end of the clustered index page sequentially until that page is filled. When a session wants to add a new row in the table, it acquires Exclusive (X) locks on the row which prevents others to add rows in the same page. In an insert intensive workload, all sessions will wait until (X) lock is released from the last page. PAGELATCH_EX wait type will be prevailed in the system. This can be explained by SQL Server's steps to accomplish this:
- Navigate through B-tree to locate the correct page to add the new record.
- Use PAGELATCH_EX lock to prohibit others from modifying it, and place PAGELATCH_SH on all the non-leaf pages.
- Record a log entry that the row has been modified.
- Add the row to the page and mark the page as dirty.
- Unlatch all pages.
PAGELATCH_XX
These latches are lightweight and non-configurable internal locks used by SQL Server to protect concurrent access to in-memory pages. For example, when the storage engine wants to access a data page in the buffer pool to send to the relational engine, it must first request a latch on that page. The latch is released once the operation is finished, and the duration is usually dependent on available memory. These latches do not involve disk I/O—they are purely in-memory access synchronization. These are buffer latches e.g. Page Free Space (PFS), Global Allocation Map (GAM), Shared Global Allocation Map (SGAM) and Index Allocation Map (IAM) pages.
Symptoms
Below symptoms may lead to last page contention:
- High PAGELATCH_EX waits type
- Specific to an user table
- The table has clustered index on an identity column
- Lots of INSERT/UPDATE operations, and
- Not on PFS, GAM, SGAM, or system pages
Simulation
Let's simulate the last page insert contention by creating a table and simulating concurrent data insertion using SQLQueryStress tool.
DROP TABLE IF EXISTS dbo.Test;
GO
CREATE TABLE [dbo].[Test](
[pk_id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NOT NULL,
CONSTRAINT [Test__pk_id] PRIMARY KEY CLUSTERED ([pk_id] ASC) ON [PRIMARY]
)
GO
Snippet-1
Run Snippet-1 and create a table and use Snippet-2 query in SQLQueryStress's query window (Figure-2). Click on Database of SQLQueryStress and connect with your database accordingly. Also set appropriate value for iterations and threads. Then press "GO" button to start the simulation.
SET NOCOUNT ON
INSERT INTO TEST([name]) VALUES
('MEHEDI')
Snippet-2
Figure-2: SQLQueryStress
Once simulation starts, run the Snippet-3 query and monitor the system to identify the resource bottlenecks (Figure-3).
SELECT DB_NAME(der.database_id) DatabaseName,session_id, der.wait_type, der.wait_time, der.wait_resource,der.status,
der.wait_resource,
deqt.text,
deqp.query_plan
FROM sys.dm_exec_requests der
CROSS APPLY sys.dm_exec_sql_text (der.sql_handle) deqt
CROSS APPLY sys.dm_exec_query_plan (der.plan_handle) deqp
WHERE SESSION_ID > 50
Snippet-3
Figure-3: Last page insert contention
You will see lots of PAGELATCH_EX wait type are in suspended status (Figure-3) and waiting for the same wait_resource (10:1:223460). This indicates that sessions are waiting for a particular page that is not accessible at that time. Here,
- 10 - is database id
- 1 - is file id and
- 223460 - is page id
If you want to know the exact bottleneck objects' name, run Snippet-4 query supplying page id. You will get the table name, index name etc (Figure-3).
SELECT
pa.allocated_page_file_id AS FileID,
pa.allocated_page_page_id AS PageID,
OBJECT_NAME(p.object_id, DB_ID()) AS TableName,
i.name AS IndexName,
p.index_id
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, 'DETAILED') pa
JOIN sys.allocation_units au ON pa.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.hobt_id
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE pa.allocated_page_page_id = 223460
Snippet-4
Figure-4: Last page insert contention
Enabling OPTIMIZE_FOR_SEQUENTIAL_KEY
SQL Server 2019 CTP 3.1 introduced a new index option, OPTIMIZE_FOR_SEQUENTIAL_KEY to address last page insert contention. There are some other methods to mitigate this issue however most of them involve making changes to either the application or the structure of the index level. This option limit the number of thread access for data insertion.
Now, execute Snippet-5 query ON the OPTIMIZE_FOR_SEQUENTIAL_KEY settings on the table.
ALTER INDEX [Test__pk_id] ON dbo.Test SET(OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);
GO
Snippet-5
Run SQLQueryStress again and run the query Snippet-3. You will see a result similar to Figure-5. This time there is no PAGELATCH_EX but BTREE_INSERT_FLOW_CONTROL wait type.
Figure-5: After applying OPTIMIZE_FOR_SEQUENTIAL_KEY
How Does OPTIMIZE_FOR_SEQUENTIAL_KEY Work
When OPTIMIZE_FOR_SEQUENTIAL_KEY is on, a flow control mechanism is enabled. So all the threads that request to enter the critical section of SQL Server code is protected by a page latch. This mechanism controls the flow of traffic based on conditions like which CPU the thread is on, the state of the thread, and/or the rate at which the thread is completing work. OPTIMIZE_FOR_SEQUENTIAL_KEY does two things:
- Controls the number of threads allowed to request the latch to one per CPU core. It would reduce time spent in the runnable queue once the latch is acquired.
- Favors threads that are more likely to complete their tasks in a single quantum over threads that will undergo several context switches while holding the latch.
This unfairness may cause some threads additional latency. However, overall improvement will be achieved as less time will be spent waiting for other resources while holding the latch and blocking other threads.
Performance Gain Due to OPTIMIZE_FOR_SEQUENTIAL_KEY
Though you will see BTREE_INSERT_FLOW_CONTROL wait type, it will still improve the performance. However, you should compare the performance with other solutions of last page insert contention. Remember that it will not improve individual insert latency.
Final Words
Last Page Insert Contention is a common challenge when workloads insert rows into a table with a sequential clustered index value, causing multiple sessions to compete for the same last data page. With OPTIMIZE_FOR_SEQUENTIAL_KEY option, DBAs can reduce contention without redesigning indexes or changing application logic. While this feature does not eliminate contention completely, you should measure improvements with other solutions of last page insert contention..
References
Going Further
If SQL Server is your thing and you enjoy learning real-world tips, tricks, and performance hacks—you are going to love my training sessions too!
Need results fast? I am also available for 1-on-1 consultancy to help you troubleshoot and fix your database performance issues.
Let’s make your SQL Server to take your business Challenge!
For any queries, mail to mamehedi.hasan[at]gmail.com.