SQL Server Performance Tuning: Dealing with Last Page Contention

Last Page Insert Contention in SQL Server

Last Page Insert Contention, commonly referred to as “last page contention,” is a specific type of contention in SQL Server related to the last page of an index, typically a clustered index. This happens because SQL Server uses a mechanism where multiple sessions try to insert records on the last page of an index, leading to contention.

The Problem

When multiple sessions are trying to insert new rows into a table, and those rows need to be placed on the last page of the table’s clustered index, they can end up waiting for each other because only one session can modify that page at a time. This contention manifests as PAGELATCH_EX waits in SQL Server wait statistics.

This is particularly prevalent in tables with ever-increasing key columns, such as those using an identity column or a timestamp as the primary key. Since new records are always added to the end, it becomes a hotspot for contention.

Causes of Last Page Insert Contention

• Hotspot: When multiple transactions try to insert into the last page of a table, they compete for the same page latch, creating a hotspot.
• Identity Columns or Sequential Keys: Tables with identity columns or sequential keys are particularly prone to this issue because new rows are always added to the end of the table.
• High Insert Volume: High volume of concurrent inserts exacerbates the contention, as more processes compete to access the last page.

Solutions to Reduce Last Page Insert Contention

• Partitioning: Partitioning the table can spread the inserts across multiple partitions, reducing the likelihood of contention on any single page.
• Use of GUIDs: Using globally unique identifiers (GUIDs) as keys can distribute inserts more evenly across the table’s pages. However, this can lead to other performance issues due to index fragmentation.
• Trace Flags: In some SQL Server versions, specific trace flags can be used to mitigate contention by altering the way pages are allocated.
• Padding and Fill Factor Adjustments: Adjusting the fill factor for indexes can leave free space in each page, reducing the need for page splits and mitigating contention. However, this can lead to increased disk space usage.
• Application Changes: Changing application logic to reduce the frequency of inserts or to batch inserts can help mitigate contention.
• Use of Delayed Durability: In SQL Server, enabling delayed durability can reduce the log write contention, indirectly mitigating the last page insert contention.

Diagnosis Tools

Dynamic Management Views (DMVs): DMVs like sys.dm_db_index_operational_stats can help identify hotspots by showing latch contention details.

SQL Server Profiler and Extended Events: These tools can help identify and analyze insert patterns leading to contention.

How to Diagnose

Monitor SQL Server’s wait statistics. If you see high PAGELATCH_EX waits, it’s an indication of this contention. You can use DMVs such as sys.dm_db_index_physical_stats and sys.dm_os_waiting_tasks to pinpoint which tables and indexes are experiencing this contention.

To monitor Last Page Insert Contention using Extended Events in SQL Server, you can set up a session that captures the latch_suspend_end event. This will help you detect when threads are waiting on latches, and you can then filter for the specific type of latch contention you’re interested in, such as PAGELATCH_EX.

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)) — Filter only for waits longer than 1000 microseconds (1 millisecond)
    )
)
ADD TARGET package0.event_file
(
    SET filename = N’Last_Page_Contention.xel’,
    max_file_size = (5), — Set max file size to 5 MB
    max_rollover_files = (5) — Set max number of rollover files
)
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    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 returns the XML data for each event. From the XML, you can extract details like sql_text, duration, database_id, etc., to understand where and why the contention is happening.

Remember, whenever setting up Extended Events or any other monitoring tools, always test in a non-production environment first to understand the overhead and ensure you capture the desired information.

How to Resolve

Partitioning: Implementing table partitioning can alleviate this contention. If you partition on the primary key and ensure that different sessions are working with different partitions, you can mitigate the last page contention issue.

Use a different primary key: If feasible, consider using a non-monotonic key as the clustered index. This can distribute inserts across the index rather than always at the end.

Optimize the Fill Factor: By setting a lower fill factor on the clustered index, you can leave more free space on each page, allowing for more inserts before page splits occur. However, this can also lead to more frequent page splits in the long run, so it’s a trade-off.

Row Compression: Implementing row compression can allow more rows to fit on a page, which can delay the need for new pages and reduce contention.

Increase Autogrowth Size: If the data file’s autogrowth setting is too small, frequent autogrowths can exacerbate last page contention. Set a reasonable size for autogrowth to ensure that it doesn’t happen too frequently.

In SQL Server 2019, a new index option was added called OPTIMIZE_FOR_SEQUENTIAL_KEY that is intended to address an issue known as last page insert contention. Most of the solutions to this problem that have been suggested in the past involve making changes to either the application or the structure of the contentious index, which can be costly and sometimes involve performance trade-offs. Rather than making major structural changes, OPTIMIZE_FOR_SEQUENTIAL_KEY addresses some of the SQL Server scheduling issues that can lead to severely reduced throughput when last page insert contention occurs.

Conclusion

When addressing last page insert contention, it’s essential to balance the trade-offs of each solution. For example, while GUIDs can reduce contention, they can increase fragmentation and affect performance in other ways. It’s often beneficial to test different strategies in a development environment to assess their impact on your specific workload.