SQL SERVER – Resolving Last Page Insert PAGELATCH_EX Contention with OPTIMIZE_FOR_SEQUENTIAL_KEY

Today’s blog post is actually a follow-up blog post of SQL SERVER – Last Page Insert PAGELATCH_EX Contention Due to Identity Column. I have personally implemented the solution by creating an index with OPTIMIZE_FOR_SEQUENTIAL_KEY at my client’s place while Comprehensive Database Performance Health Check. and we have seen some really good improvement in performance.

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.

• SQL SERVER – Performance Test – sqlcmd vs SSMS
• SQL SERVER – Performance Test – oStress vs SSMS
• SQL SERVER – Stress Testing with oStress – Load Testing
• SQL SERVER Management Studio and SQLCMD Mode
• SQL SERVER – Wait Statistics Generated by oStress – Insert Workload
• SQL SERVER – Impact of Recovery Model on Insert Workload Stress Test and Wait Statistics
• SQL SERVER – Last Page Insert PAGELATCH_EX Contention Due to Identity Column

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.

OPTIMIZE_FOR_SEQUENTIAL_KEY in SQL Server 2019

As seen in the earlier blog post, due to the identity key, we are getting a heavy PAGELATCH_EX wait type and overall slow performance. Now today we will see how we can use the new feature which is available in SQL Server 2019 to optimize OPTIMIZE_FOR_SEQUENTIAL_KEY.

This new feature is only available in SQL Server 2019, so if you are using the earlier version of SQL Server you may get an error when you run the sample code.

Demo : OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF

Let us create a table in our database.

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

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 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

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

Demo : OPTIMIZE_FOR_SEQUENTIAL_KEY = ON

Now we will create a new table which is very similar to the earlier table but this time we will add the OPTIMIZE_FOR_SEQUENTIAL_KEY = ON to the primary key index which is on the identity column.

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

Please pay special attention to the keywords OPTIMIZE_FOR_SEQUENTIAL_KEY = ON in the code above. Now we will repeat the same exercise which we had performed before.

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].[TestTableOpt] 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.

BTREE_INSERT_FLOW_CONTROL Wait Stats

Additionally, you can see that there is a high amount of new wait type BTREE_INSERT_FLOW_CONTROL. The wait is actually governing the new flow control of B-Tree insertions. There are sets of improvements this new Btree Insert Flow control brings to insertion logic.

When OPTIMIZE_FOR_SEQUENTIAL_KEY is set to ON, Btree Insert Flow Controls how the inserts are happening. This new flow control gives priority to the threads that will complete the task early have a very little delay. Essentially, instead of inserts operations getting stuck at the threads which are waiting on other operations, the control flow selects the threads which are faster to complete their task and have a lesser delay.

I will write a future blog post just discussing this wait stats with a few of the tests, I have done at my client’s place during Health Check.

Summary – Remove Contention

In our test described in the blog post, we have seen that our query got completed nearly 23 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.

With that said, if you enable this feature on the table where there are not many inserts going on, you may not see any performance improvement at all. There are rare situations where I have seen even this feature OPTIMIZE_FOR_SEQUENTIAL_KEY = ON working against the overall performance.

Enable this feature on the table where you are very sure that it is going to positively improve performance. If you are seeing a high amount of the PAGELATCH_EX in your overall wait statistics, you should definitely investigate your system to identify hotspots and contention.

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)