SQL SERVER – Last Page Insert PAGELATCH_EX Contention Due to Identity Column

May 5, 2020

Resolving the Last Page Insert PAGELATCH_EX Contention Due to the Identity Column is always a topic to be discussed during my Comprehensive Database Performance Health Check. I will discuss the solution the future blog posts, our today’s goal is to understand what is the Last Page Insert PAGELATCH_EX Contention Due to Identity Column.

SQL SERVER - Last Page Insert PAGELATCH_EX Contention Due to Identity Column LastPageInsert0-800x216

Performance Tuning Architecture

As you know I primarily work with the SQL Server Performance Tuning area and I have pretty much seen most of the SQL Server performance troubles. During the consultation, my chief goal is to help clients to resolve the issues without any schema or architecture changes as they are difficult to resolve. With that said, it is not always possible to resolve the problem with just changing in Server configurations or indexes.

If I see a table in a system which is constantly modified with the inserts and it has a primary key (with clustered index) on the identity column, I am always very certain that it is creating a hot spot for insert or in more technical terms it creates the Last Page Insert PAGELATCH_EX Contention Due to Identity Column.

Before we continue talking about this subject, let us see a quick definition of the PAGELATCH.

PAGELATCH

Pagelatch is a thread synchronization mechanism. It synchronizes short term physical access to the database pages located in the buffer cache area. In simpler words, they help SQL Server to guarantee the consistency of the in-memory structures.

It is very important to remember that page latches are common and they are also required in many cases when physical pages need protection. Do not assume that PAGELATCH wait is always bad and also not spend all of your time reduce it to zero. With said a higher value in the PAGELATCH is usually a representation of hot spot for insert or insert contention on the last page of the B-Tree.

There are many different types of the latch but in this blog post we will see two different types of the waits during the demonstration.

EX – Exclusive latch, blocks other threads from writing to or reading from the referenced structure.
SH – Shared latch, required to read a page structure.

Identity Column – Primary Key with Clustered Index

When any table has a primary key (which is also a clustered index) on the identity column, all the newly inserted data is always inserted at the end of the table on a single page. If you have multiple inserts going on a single table in parallel, there is a good chance that that one page now gets lots of traffic from the insert page. Every single insert has to take access on that page to make its own insert which leads to a heavy contention on the page. This contention eventually creates the wait type of PageLatch and it can be visible in the Wait Statistics.

If you do not have a query to measure the wait statistics, you can get that from here: SQL Server Wait Statistics collection script.

Demonstration – Last Page Insert

Let us create a table in our database.

USE [SQLAuthority]
GO
CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](500) NOT NULL DEFAULT ('I hope you have a great day!'),
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]
GO

Now that we have created a table with an identity column on the same column created a primary key clustered index.

Please note that there is no impact of the recovery model on this demonstration so we are not going to discuss that. I have previously blogged about that here: Impact of Recovery Model on Insert Workload Stress Test and Wait Statistics.

Now rest the wait statistics on your development or test server where you are going to run the query with the following command.

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
GO

To run the test make sure that you have installed RML Utility in your system. Next, you can run the oStress command RML Utility folder. In most systems, it will be here: C:\Program Files\Microsoft Corporation\RMLUtils.

ostress -S"Quick\SQL19" -E -Q"INSERT INTO [SQLAuthority].[dbo].[TestTable] VALUES(DEFAULT);" -n100 -r10000 -q -dSQLAuthority

As you can see in the code above, I am running the test of inserting a single row 10000 times (one at a time) in 100 parallel threads in parallel. This will definitely create a hotspot while inserting as there is a Primary Key (clustered) on the identity column.

The query took around 2 minutes and 1 second to complete and here is the output of the wait statistics.

Well, you can see from the wait statistics that due to contention on the identity column the wait on the PAGELATCH_EX is very heavy. The wait time in the seconds is 4157 across all the threads and wait was around 46% of the total wait with the waiting task of 1230345.

Solution – Last Page Insert

If your system is seeing a high PAGELATCH_EX, it is high time you pay attention to it and do your best to resolve them. Here are a few quick suggestions which we will explore in future blog posts.

Remove Primary Key from Identity Column
Add a PK which will distribute the insert across many pages
Create a computed column with partition key
In SQL Server 2019 you can use OPTIMIZE_FOR_SEQUENTIAL_KEY
… and a few more.

As I mentioned earlier in the blog post, we will be not looking at the solution in this blog post. We will talk about various solutions in future blog posts and I will keep updating this blog post with the link for it.

Wait Statistics are a great way to get idea of your system’s performance bottleneck, if you need help with that, you can always send them to me at pinal@Sqlauthority.com and I will be happy to help.

Reference: Pinal Dave (https://blog.sqlauthority.com)