In high-concurrency environments, SQL Server databases may experience performance bottlenecks due to contention issues. One common issue is Last Page Insert Contention<\/strong>, 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.<\/p>\n 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 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.<\/p>\n Check SQL Server’s wait statistics for high Use Set up an Extended Events session to capture the This query returns the XML data for each event. From the XML, you can extract details like Note<\/strong>: Always test monitoring tools in a non-production environment first to understand the overhead and ensure you capture the desired information.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n In SQL Server 2019, a new index option called To alleviate 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.<\/p>\n 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.<\/p>\n","protected":false},"excerpt":{"rendered":" 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. We will look at page insert contention and give recommendations for improvement.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[104,12,324,5,91,73],"tags":[358,353,356,289,352,351,357,354,355,131],"class_list":["post-853","post","type-post","status-publish","format-standard","hentry","category-indexing","category-internals","category-locking","category-performance","category-query-optimization","category-troubleshooting","tag-bit-reversal","tag-extended-events","tag-index-contention","tag-index-fragmentation","tag-indexing-strategies","tag-insert-contention","tag-last-page-contention","tag-latch-contention","tag-partitioning","tag-sql-server"],"yoast_head":"\nThe Problem<\/h2>\n
PAGELATCH_EX<\/code> waits in SQL Server’s wait statistics.<\/p>\nCauses of Last Page Insert Contention<\/h2>\n
\n
Diagnosis Tools<\/h2>\n
\n
sys.dm_db_index_operational_stats<\/code> help identify hotspots by displaying latch contention details.<\/li>\nHow to Diagnose<\/h2>\n
Monitor Wait Statistics<\/h3>\n
PAGELATCH_EX<\/code> waits, indicating contention issues.<\/p>\nUtilize DMVs<\/h3>\n
sys.dm_db_index_physical_stats<\/code> and sys.dm_os_waiting_tasks<\/code> to pinpoint affected tables and indexes.<\/p>\nExtended Events<\/h3>\n
latch_suspend_end<\/code> event. This detects when threads wait on latches, allowing you to filter for PAGELATCH_EX<\/code> contention.<\/p>\nSetting Up the Extended Event Session<\/h4>\n
CREATE EVENT SESSION [Last_Page_Contention] ON SERVER\nADD EVENT sqlos.latch_suspend_end\n(\n ACTION\n (\n sqlserver.database_id,\n sqlserver.sql_text,\n sqlserver.tsql_stack\n )\n WHERE\n (\n [package0].[equal_uint64]([latch_class], (28)) -- 28 corresponds to PAGELATCH_EX\n AND [package0].[greater_than_uint64]([duration], (1000)) -- Waits longer than 1 millisecond\n )\n)\nADD TARGET package0.event_file\n(\n SET filename = N'Last_Page_Contention.xel',\n max_file_size = (5),\n max_rollover_files = (5)\n)\nWITH\n(\n MAX_MEMORY = 4096 KB,\n EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,\n MAX_DISPATCH_LATENCY = 5 SECONDS,\n MEMORY_PARTITION_MODE = NONE,\n TRACK_CAUSALITY = OFF,\n STARTUP_STATE = OFF\n);<\/code><\/pre>\nReading the Data<\/h4>\n
SELECT\n object_name,\n CONVERT(XML, event_data) AS event_data_XML\nFROM\n sys.fn_xe_file_target_read_file('Last_Page_Contention*.xel', null, null, null);<\/code><\/pre>\nsql_text<\/code>, duration<\/code>, database_id<\/code>, and more to understand where and why the contention is happening.<\/p>\nSolutions to Reduce Last Page Insert Contention<\/h2>\n
1. Partitioning<\/h3>\n
2. Use a Different Primary Key<\/h3>\n
3. Optimize the Fill Factor<\/h3>\n
4. Row Compression<\/h3>\n
5. Increase Autogrowth Size<\/h3>\n
6. Use of Delayed Durability<\/h3>\n
7. SQL Server 2019’s
OPTIMIZE_FOR_SEQUENTIAL_KEY<\/code><\/h3>\nOPTIMIZE_FOR_SEQUENTIAL_KEY<\/code> 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.<\/p>\nImplementing
OPTIMIZE_FOR_SEQUENTIAL_KEY<\/code><\/h4>\nCREATE INDEX IX_Table_Column\nON dbo.YourTable(YourSequentialKeyColumn)\nWITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);<\/code><\/pre>\nAlternative Solution: Bit Reversal Using a Sequence and Function<\/h3>\n
PAGELATCH_EX<\/code> contention, particularly when experiencing high contention on insert operations, you can replace the use of an IDENTITY<\/code> column with a SEQUENCE<\/code> 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.<\/p>\nCreating a Sequence<\/h4>\n
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.seqmysequence') AND type = N'SO')\n DROP SEQUENCE dbo.seqmysequence;\n\nCREATE SEQUENCE dbo.seqmysequence AS int\n START WITH 1\n INCREMENT BY 1;\nGO<\/code><\/pre>\nCreating the Bit Reversal Function<\/h4>\n
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'))\n DROP FUNCTION [dbo].[ufn_mybitreverse];\nGO\n\nCREATE FUNCTION ufn_mybitreverse (@InputVal int)\nRETURNS int\nWITH SCHEMABINDING\nAS\nBEGIN\n DECLARE @WorkValue int = @InputVal;\n DECLARE @Result int = 0;\n DECLARE @Counter tinyint = 0;\n\n WHILE @Counter < 31 -- 63 for bigint\n BEGIN\n SET @Result = @Result*2;\n IF (@WorkValue&1) = 1\n BEGIN\n SET @Result = @Result+1;\n SET @WorkValue = @WorkValue-1;\n END\n SET @WorkValue = @WorkValue\/2;\n SET @Counter = @Counter+1;\n END\n\n RETURN @Result;\nEND;\nGO<\/code><\/pre>\nCreating the Table and Index<\/h4>\n
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.mytable') AND type = N'U'))\n DROP TABLE dbo.mytable;\n\nCREATE TABLE dbo.mytable (\n ID int NOT NULL,\n col1 VARCHAR(50) NOT NULL\n);\nGO\n\nCREATE UNIQUE CLUSTERED INDEX CIX_BitReversal\nON dbo.mytable (ID);\nGO<\/code><\/pre>\nInserting Data Using the Bit Reversal Function<\/h4>\n
DECLARE @value int;\nDECLARE @i int = 1;\n\nWHILE @i <= 100000\nBEGIN\n SELECT @value = NEXT VALUE FOR dbo.seqmysequence;\n INSERT INTO dbo.mytable (ID, col1)\n SELECT dbo.ufn_mybitreverse(@value), 'testing heavy';\n SET @i += 1;\nEND;\nGO<\/code><\/pre>\nConclusion<\/h2>\n