Diagnosing and Resolving Last Page Insert Contention in SQL Server

Introduction

In high-concurrency environments, SQL Server databases may experience performance bottlenecks due to contention issues. One common issue is Last Page Insert Contention, which occurs when multiple sessions attempt to insert records into the last page of an index simultaneously. This contention can lead to significant wait times and reduced performance, particularly in tables with ever-increasing key columns.

The Problem

When numerous sessions concurrently insert new rows into a table that need to be placed on the last page of the table’s clustered index, they often end up waiting for one another. SQL Server allows only one session to modify a page at a time, leading to page-level locking. This contention manifests as PAGELATCH_EX waits in SQL Server’s wait statistics.

Tables that use identity columns or timestamps as primary keys are particularly susceptible. Since new records are always added to the end of the index, the last page becomes a hotspot, causing sessions to queue up for access.

Causes of Last Page Insert Contention

• Hotspot Creation: Multiple transactions targeting the last page compete for the same page latch, creating a hotspot.
• Identity Columns or Sequential Keys: Tables with sequential keys are prone to contention because inserts always occur at the end.
• High Insert Volume: A high volume of concurrent inserts increases competition for the last page.

Diagnosis Tools

• Dynamic Management Views (DMVs): Views like sys.dm_db_index_operational_stats help identify hotspots by displaying latch contention details.
• SQL Server Profiler and Extended Events: These tools capture and analyze insert patterns that lead to contention.

How to Diagnose

Monitor Wait Statistics

Check SQL Server’s wait statistics for high PAGELATCH_EX waits, indicating contention issues.

Utilize DMVs

Use sys.dm_db_index_physical_stats and sys.dm_os_waiting_tasks to pinpoint affected tables and indexes.

Extended Events

Set up an Extended Events session to capture the latch_suspend_end event. This detects when threads wait on latches, allowing you to filter for PAGELATCH_EX contention.

Setting Up the Extended Event Session

CREATE EVENT SESSION [Last_Page_Contention] ON SERVER
ADD EVENT sqlos.latch_suspend_end
(
    ACTION
    (
        sqlserver.database_id,
        sqlserver.sql_text,
        sqlserver.tsql_stack
    )
    WHERE
    (
        [package0].[equal_uint64]([latch_class], (28)) -- 28 corresponds to PAGELATCH_EX
        AND [package0].[greater_than_uint64]([duration], (1000)) -- Waits longer than 1 millisecond
    )
)
ADD TARGET package0.event_file
(
    SET filename = N'Last_Page_Contention.xel',
    max_file_size = (5),
    max_rollover_files = (5)
)
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);

Reading the Data

SELECT
    object_name,
    CONVERT(XML, event_data) AS event_data_XML
FROM
    sys.fn_xe_file_target_read_file('Last_Page_Contention*.xel', null, null, null);

This query returns the XML data for each event. From the XML, you can extract details like sql_text, duration, database_id, and more to understand where and why the contention is happening.

Note: Always test monitoring tools in a non-production environment first to understand the overhead and ensure you capture the desired information.

Solutions to Reduce Last Page Insert Contention

1. Partitioning

Implement table partitioning to alleviate contention. By partitioning on the primary key and ensuring that different sessions work with different partitions, inserts are spread across multiple partitions, reducing the likelihood of contention on any single page.

2. Use a Different Primary Key

Consider using a non-sequential key as the clustered index. Using a non-monotonic key, such as a random value or a GUID, distributes inserts across the index rather than always at the end. This can reduce contention but may introduce challenges like index fragmentation and larger index sizes.

3. Optimize the Fill Factor

Adjust the fill factor for indexes to leave extra space in each page, reducing the need for page splits and mitigating contention. Setting a lower fill factor allows more free space on each page, accommodating more inserts before page splits occur. Be cautious, as this can lead to increased disk space usage and may require more frequent index maintenance.

4. Row Compression

Implement row compression to allow more rows to fit on a page, delaying the need for new pages and reducing contention. Row compression can also improve I/O performance by reducing the amount of data read from and written to disk.

5. Increase Autogrowth Size

If the data file’s autogrowth setting is too small, frequent autogrowth events can exacerbate last page contention. Setting a reasonable autogrowth size ensures that data files grow less frequently and in larger increments, reducing overhead and contention during growth operations.

6. Use of Delayed Durability

Enable delayed durability to reduce log write contention, indirectly mitigating last page insert contention. Transactions are considered committed when the log records are written to the log buffer in memory, rather than being flushed to disk immediately. This can improve throughput but may risk data loss in the event of a crash.

7. SQL Server 2019’s OPTIMIZE_FOR_SEQUENTIAL_KEY

In SQL Server 2019, a new index option called OPTIMIZE_FOR_SEQUENTIAL_KEY was introduced to address last page insert contention. This option optimizes indexes for sequential key inserts, such as those using identity columns, by improving throughput under high concurrency without requiring significant structural changes to the application or database schema. It achieves this by adjusting the scheduling of threads waiting to access the last page, reducing contention and improving performance.

Implementing OPTIMIZE_FOR_SEQUENTIAL_KEY

CREATE INDEX IX_Table_Column
ON dbo.YourTable(YourSequentialKeyColumn)
WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);

Alternative Solution: Bit Reversal Using a Sequence and Function

To alleviate PAGELATCH_EX contention, particularly when experiencing high contention on insert operations, you can replace the use of an IDENTITY column with a SEQUENCE object and a user-defined function that distributes inserts more evenly across data pages. This approach helps mitigate the “hot page” problem where multiple transactions compete for the same data page.

Creating a Sequence

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.seqmysequence') AND type = N'SO')
    DROP SEQUENCE dbo.seqmysequence;

CREATE SEQUENCE dbo.seqmysequence AS int
    START WITH 1
    INCREMENT BY 1;
GO

Creating the Bit Reversal Function

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufn_mybitreverse]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[ufn_mybitreverse];
GO

CREATE FUNCTION ufn_mybitreverse (@InputVal int)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @WorkValue int = @InputVal;
    DECLARE @Result int = 0;
    DECLARE @Counter tinyint = 0;

    WHILE @Counter < 31 -- 63 for bigint
    BEGIN
        SET @Result = @Result*2;
        IF (@WorkValue&1) = 1
        BEGIN
            SET @Result = @Result+1;
            SET @WorkValue = @WorkValue-1;
        END
        SET @WorkValue = @WorkValue/2;
        SET @Counter = @Counter+1;
    END

    RETURN @Result;
END;
GO

Creating the Table and Index

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.mytable') AND type = N'U'))
    DROP TABLE dbo.mytable;

CREATE TABLE dbo.mytable (
    ID int NOT NULL,
    col1 VARCHAR(50) NOT NULL
);
GO

CREATE UNIQUE CLUSTERED INDEX CIX_BitReversal
ON dbo.mytable (ID);
GO

Inserting Data Using the Bit Reversal Function

DECLARE @value int;
DECLARE @i int = 1;

WHILE @i <= 100000
BEGIN
    SELECT @value = NEXT VALUE FOR dbo.seqmysequence;
    INSERT INTO dbo.mytable (ID, col1)
    SELECT dbo.ufn_mybitreverse(@value), 'testing heavy';
    SET @i += 1;
END;
GO

This method generates integer values using bit reversal, which are used during inserts to distribute them across different pages. This helps to spread out the inserts and reduce contention on any single page.

Conclusion

Last Page Insert Contention can significantly impact SQL Server performance in environments with high insert volumes and sequential key usage. By understanding its causes and implementing strategies like partitioning, adjusting fill factors, or modifying application logic, you can effectively reduce contention and enhance overall database performance.