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.

SQL SERVER - Resolving Last Page Insert PAGELATCH_EX Contention with OPTIMIZE_FOR_SEQUENTIAL_KEY contention-800x332

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.

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.

Solarwinds

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
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.
SQL SERVER - Resolving Last Page Insert PAGELATCH_EX Contention with OPTIMIZE_FOR_SEQUENTIAL_KEY insertkeytest1
The query took around 1 minute and 54 seconds to complete and here is the output of the wait statistics.
SQL SERVER - Resolving Last Page Insert PAGELATCH_EX Contention with OPTIMIZE_FOR_SEQUENTIAL_KEY insertkeytest2
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 3503 across all the threads and wait was around 41% of the total wait with the waiting task of 1197699.

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.

SQL SERVER - Resolving Last Page Insert PAGELATCH_EX Contention with OPTIMIZE_FOR_SEQUENTIAL_KEY insertkeytest3

The query took around 1 minute and 31 seconds to complete and here is the output of the wait statistics.  Our new insert is 23 seconds faster than earlier. A huge improvement in performance.
SQL SERVER - Resolving Last Page Insert PAGELATCH_EX Contention with OPTIMIZE_FOR_SEQUENTIAL_KEY insertkeytest4
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 520 across all the threads and wait was around 8% of the total wait with the waiting task of 1049532.

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)

Solarwinds
, , , , , , , , ,
Previous Post
SQL SERVER Management Studio and SQLCMD Mode
Next Post
SQL SERVER – Add Folder Paths to the Windows Path Variable for Easy Access

Related Posts

Leave a Reply

Menu