SQL SERVER – Resolving Last Page Insert PAGELATCH_EX Contention Changing Primary Key to Non-Clustered

3 years ago

Today we are going to write another follow-up blog post for the Resolving Last Page Insert PAGELATCH_EX Contention Changing Primary Key. The earlier suggestion which I had posted was for SQL Server 2019. You can read the entire blog post here SQL SERVER – Resolving Last Page Insert PAGELATCH_EX Contention with OPTIMIZE_FOR_SEQUENTIAL_KEY. One of my clients of Comprehensive Database Performance Health Check recently asked if there is any way they can reduce the PAGELATCH_EX Contention for their SQL Server which is not running 2019. Yes, you can change your clustered primary key to Non-clustered Primary Key, and can get the benefit of performance by reducing Pagelatch_EX. Let us learn about it today.

Related Prior Reading on Contention

This blog post is going to assume that you have installed RML Utilities in your server and you know how to use oStress. If you want to learn more about that topic, I strongly suggest that you read the following blog posts.

Now that you have setup RML Utility, Wait Statistics script, let us start with a demonstration where we will generate PAGELATCH on a table by creating a primary key (with clustered index) on an identity column.

Demo: Non-clustered Primary Key

Let us create a table in our database. Now rest the wait statistics on your development or test server where you are going to run the query with the following command. Please note that unlike the previous example, we will not create a Primary Key Clustered but will create a nonclustered primary key.

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 NONCLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]
GO

Please pay attention to the PK constraint as a Non-clustered.

First, reset the wait statistics:

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

Next, we will 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

Please note that the only change in the oStress command is the table name. The rest of the logic is very same. I am still running the test of inserting a single row 10000 times (one at a time) in 100 parallel threads in parallel.

The query took around 1 minute and 06 seconds to complete and here is the output of the wait statistics.

We can clearly see that the count of the PAGELATCH_EX wait is much lesser around 2234 seconds than the previous experiment which we had done. I compared this result with the earlier test performed in this blog post, under the heading Demo: OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF.

In the previous case, the query took around 1 minute and 54 seconds, and wait stats were around 3503 seconds.

Summary – Remove Contention

In our test described in the blog post, we have seen that our query got completed nearly 48 seconds faster. However, when I ran this test at the client place where they have significant inserts going on a large table with lots of other workloads, I have seen performance which was very surprising in numbers to me.

Well, there you go, by just changing the clustered index to non-clustered index, we see a significant performance improvement.

In general, it is not a good idea to have a table without clustered index hence, I strongly recommend that you find another column which is more suitable for a clustered index.

If you have a slow running SQL Server, I will be happy to help you. Write an email to me or just ping me or connect with me on LinkedIn.

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