Last Page Insert Contention

I recently worked on an issue called “Last Page Insert Contention,” which I had heard of before but never tackled firsthand until a few weeks ago. Here's what I learned and steps I took to resolve it.

What Is Last Page Insert Contention?

When operations wait to acquire a latch on a page, you’ll see a wait type called PAGELATCH. A latch is a lightweight lock. PAGELATCH waits typically occur in TempDB on pages like PFS, GAM, SGAM, or system object pages.

Normally, you won’t see PAGELATCH waits in user databases because user objects don’t usually experience the same level of concurrent inserts/updates/deletes as temp tables do.

But, there is one case where this can happen:
When many concurrent transactions try to insert into the last page of a table, they all compete for a latch on that page. This results in last page insert contention.

How to Identify Last Page Insert Contention

You can confirm this issue when your wait stats show:

• PAGELATCH_EX waits

• On an user object

• INSERT operations

• And not on PFS, GAM, SGAM, or system pages

Demo: Simulating the Contention

• Create a new table

USE DBATEST;
CREATE TABLE dbo.LastPageInsertTable(
id bigint IDENTITY(1,1) NOT NULL,
InsertDate datetime NOT NULL,
Comments nvarchar(max) NULL,
CONSTRAINT PK_LastPageInsertTable PRIMARY KEY CLUSTERED
(id ASC) ) 
GO
ALTER TABLE dbo.LastPageInsertTable ADD CONSTRAINT DF_LastPageInsertTable DEFAULT (getdate()) FOR InsertDate
GO

• This is the insert statement I’m going to run to cause contention

INSERT INTO dbo.LastPageInsertTable (Comments) 
VALUES ('Am I causing Contention')

• To simulate contention, run ostress utility to execute insert command on 50 concurrent connections for 1000 times. (More about ostress utility - https://gohigh.substack.com/p/stress-testing-a-server)

ostress.exe -S"Server1" -Q"INSERT INTO dbo.LastPageInsertTable (Comments) VALUES ('Am I causing Contention')" -n50 -r1000 -q

• Now check the waits using below script and you will see PAGELATCH_EX on LastPageInsertTable.

  /*
  Check if waits exist:
  */
  USE master
  GO
  SELECT
  er.session_id, er.wait_type, er.wait_resource,
  OBJECT_NAME(page_info.[object_id],page_info.database_id) as [object_name],
  er.blocking_session_id,er.command,
      SUBSTRING(st.text, (er.statement_start_offset/2)+1,   
          ((CASE er.statement_end_offset  
            WHEN -1 THEN DATALENGTH(st.text)  
           ELSE er.statement_end_offset  
           END - er.statement_start_offset)/2) + 1) AS statement_text,
  page_info.database_id,page_info.[file_id], page_info.page_id, page_info.[object_id],
  page_info.index_id, page_info.page_type_desc
  FROM sys.dm_exec_requests AS er
  CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
  CROSS APPLY sys.fn_PageResCracker (er.page_resource) AS r --database ID, file ID, page ID SQL2019
  CROSS APPLY sys.dm_db_page_info(r.[db_id], r.[file_id], r.page_id, 'DETAILED') AS page_info --replace dbcc page SQL2019
  WHERE er.wait_type like 'PAGELATCH%'
  AND er.wait_resource NOT LIKE '2:%';
  GO
  

So, there are pagelatches on user object - LastPageInsertTable due to INSERT commands, then you know it is last page insert contention.

Note: wait_resource 7:1:321764 means:

• 7 = database ID

• 1 = data file ID

• 321764 = page number

Microsoft Script to Confirm Contention

To confirm if what we are seeing is last page insert contention, Microsoft has provided a script.

Run this only when you're actually seeing PAGELATCHes or experiencing contention. If you run when there are no pagelatches at that moment, it will simply report “no contention.”

/* Check Contention Script:

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/resolve-pagelatch-ex-contention#1-confirm-the-contention-on-pagelatch_ex-and-identify-the-contention-resource

*/

SET NOCOUNT ON
DECLARE @dbname SYSNAME, @dbid INT, @objectid INT, @indexid INT, @indexname SYSNAME, @sql VARCHAR(8000), @manul_identification VARCHAR(8000)

IF (CONVERT(INT, SERVERPROPERTY('ProductMajorVersion')) >= 15)
BEGIN

    DROP TABLE IF EXISTS #PageLatchEXContention

    SELECT DB_NAME(page_info.database_id) DbName, r.db_id DbId, page_info.[object_id] ObjectId, page_info.index_id IndexId
    INTO #PageLatchEXContention
    FROM sys.dm_exec_requests AS er
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
        CROSS APPLY sys.fn_PageResCracker (er.page_resource) AS r
        CROSS APPLY sys.dm_db_page_info(r.[db_id], r.[file_id], r.page_id, 'DETAILED') AS page_info
    WHERE er.wait_type = 'PAGELATCH_EX' AND page_info.database_id not in (db_id('master'),db_id('msdb'), db_id('model'), db_id('tempdb'))
    GROUP BY DB_NAME(page_info.database_id), r.db_id, page_info.[object_id], page_info.index_id
    HAVING COUNT(er.session_id) > 5 AND Max (er.wait_time) > 10

    SELECT * FROM #PageLatchEXContention
    IF EXISTS (SELECT 1 FROM #PageLatchEXContention)
    BEGIN
        DECLARE optimize_for_seq_key_cursor CURSOR FOR
            SELECT DbName, DbId, ObjectId, IndexId FROM #PageLatchEXContention
            
        OPEN optimize_for_seq_key_cursor
        FETCH NEXT FROM optimize_for_seq_key_cursor into @dbname, @dbid, @objectid , @indexid
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT 'Consider using below statement to enable OPTIMIZE_FOR_SEQUENTIAL_KEY for the indexes in the "' + @dbname + '" database' AS Recommendation
            SELECT @sql =  'select ''use ' + @dbname + '; ALTER INDEX '' + i.name + '' ON ' + OBJECT_NAME(@objectid, @dbid) + ' SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON )'' AS Corrective_Action from #PageLatchEXContention pl JOIN ' + @dbname+'.sys.indexes i ON pl.ObjectID = i.object_id WHERE object_id = ' + CONVERT(VARCHAR, @objectid) + ' AND index_id = ' + CONVERT(VARCHAR, @indexid)

            EXECUTE (@sql)
            FETCH NEXT FROM optimize_for_seq_key_cursor INTO @dbname, @dbid, @objectid , @indexid

        END

        CLOSE optimize_for_seq_key_cursor
        DEALLOCATE optimize_for_seq_key_cursor
    
    END
    ELSE
        SELECT 'No PAGELATCH_EX contention found on user databases on in SQL Server at this time'
END
ELSE
BEGIN
    
    IF OBJECT_ID('tempdb..#PageLatchEXContentionLegacy') IS NOT NULL
        DROP TABLE #PageLatchEXContentionLegacy
    
    SELECT 'dbcc traceon (3604); dbcc page(' + replace(wait_resource,':',',') + ',3); dbcc traceoff (3604)' TSQL_Command
    INTO #PageLatchEXContentionLegacy
    FROM sys.dm_exec_requests er
    WHERE er.wait_type = 'PAGELATCH_EX' AND er.database_id NOT IN (db_id('master'),db_id('msdb'), db_id('model'), db_id('tempdb'))
    GROUP BY wait_resource
    HAVING COUNT(er.session_id) > 5 AND Max (er.wait_time) > 10

    SELECT * FROM #PageLatchEXContentionLegacy
    
    IF EXISTS(SELECT 1 FROM #PageLatchEXContentionLegacy)
    BEGIN
        SELECT 'On SQL Server 2017 or lower versions, you can manually identify the object where contention is occurring using DBCC PAGE locate the m_objId = ??. Then SELECT OBJECT_NAME(object_id_identified) and locate indexes with sequential values in this object' AS Recommendation
        
        DECLARE get_command CURSOR FOR
            SELECT TSQL_Command from #PageLatchEXContentionLegacy

        OPEN get_command
        FETCH NEXT FROM get_command into @sql
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @sql AS Step1_Run_This_Command_To_Find_Object
            SELECT 'select OBJECT_NAME(object_id_identified)' AS Step2_Find_Object_Name_From_ID
            FETCH NEXT FROM get_command INTO @sql
        END

        CLOSE get_command
        DEALLOCATE get_command

        SELECT 'Follow https://learn.microsoft.com/troubleshoot/sql/performance/resolve-pagelatch-ex-contention for resolution recommendations that fits your environment best' Step3_Apply_KB_article
        
    END
    ELSE
        SELECT 'No PAGELATCH_EX contention found on user databases on in SQL Server at this time'

END

Here we have it confirmed. And it also gives you which index to modify with the script to enable ‘optimize for sequential key’.

Recommendation:

Consider using below statement to enable OPTIMIZE_FOR_SEQUENTIAL_KEY for the indexes in the "DBATEST" database

Corrective Action:

use DBATEST; 
ALTER INDEX PK_LastPageInsertTable ON LastPageInsertTable SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON )

After deploying the corrective action which is to enable ‘optimize sequential key‘, I reran the ostress test again, there are no PAGELATCHes anymore. Now, the script also reported: ‘No PAGELATCH_EX contention found on user databases on in SQL Server at this time’

Lessons from Applying in Production

When I was doing this on production, I ran the index modification for one table on a Friday evening after business hours, to make sure there was no impact, since it was my first time dealing with this issue. The next time when I had to enable this option on two more tables, I did it during the business hours and it was perfectly fine.

As PAGELATCH wait type goes down, you may see BTREE_INSERT_FLOW_CONTROL wait type which is harmless and this does show up by design since the option is enabled.

As a result, on my server

• PAGELATCH wait type got reduced by 70%

• Reduction in number of timeouts on one of the stored procedures, which is exactly what we wanted

• Before modifying the index - Pagelatches were high

• After modifying the index - Pagelatches have reduced

How to get a list of tables that are undergoing ‘last page insert contention’

If you’re unsure how many tables might be experiencing contention, run the following PowerShell script on the server. This script continuously runs the “Check Contention” query every 5 seconds for an hour in a loop. The reason I let this PowerShell script run for an hour is that, when inserts occur intermittently, you might miss the contention if you check only once or you may not capture the full scope of which objects are affected.

This query is not resource-intensive at all, so it’s perfectly fine to let it run in a loop for an hour.

PowerShell Script (Runs Every 5 Seconds for 1 Hour):

Create the ‘Check Contention’ script as Check_PAGELATCH.txt in a directory, in this case it is on C:\Backup.

$Server = "servername"      # Use instance name if needed 
$Database = "master"          # Or any relevant DB 
$SqlScriptPath = "C:\Backup\Check_PAGELATCH.sql"
$OutputPath = "C:\Backup\Output_Log.txt"  
for ($i = 0; $i -lt 700; $i++) #run once every 5 seconds for 700 times(1 hour)
{  
$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"     
Add-Content -Path $OutputPath -Value "n========================="     
Add-Content -Path $OutputPath -Value "Run Time: $timestamp"          
sqlcmd -S $Server -d $Database -i $SqlScriptPath | Out-File -Append -FilePath $OutputPath        
Start-Sleep -Seconds 5     
} 

As this powershell is running, open C:\Backup\Output_Log.txt file

• You can get a list of tables that are undergoing last page insert contention issue

• It will contain alter index statements for tables that need ‘Optimize for Sequential key’ to be enabled, which you can just execute, but of course after proper testing.

• One downside: Even after the index is modified, sometimes the same corrective action keeps coming up, which I just ignored.

Summary

• Last Page Insert Contention happens when many inserts fight to get a latch on the last page of a table.

• Confirm this contention using Microsoft’s script.

• Fix it using OPTIMIZE_FOR_SEQUENTIAL_KEY or run the corrective action script that is provided.

• Automate detection with PowerShell to catch last page contention on various tables.

Hope this helps you resolve insert contention issue.

Additional Reading

• https://techcommunity.microsoft.com/blog/sqlserver/behind-the-scenes-on-optimize-for-sequential-key/806888

• https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/resolve-pagelatch-ex-contention#1-confirm-the-contention-on-pagelatch_ex-and-identify-the-contention-resource

• https://erikdarling.com/pros-and-cons-of-last-page-contention-solutions/

• https://gohigh.substack.com/p/stress-testing-a-server

• https://gohigh.substack.com/p/easily-identify-object-name-from

• https://www.sqlshack.com/sql-server-performance-tuning-resolving-last-page-insert-contention/

Thanks for reading,
Haripriya.
LinkedIn