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
waitsOn 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://erikdarling.com/pros-and-cons-of-last-page-contention-solutions/
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
This is excellent article thanks for educating us in this.
Excellent Article. Learnt something new for me. Thanks much