Are you experiencing slow performance in your SQL Server database? One possible cause could be contention on the identity column. In this blog post, we will explore how to optimize performance using the OPTIMIZE_FOR_SEQUENTIAL_KEY feature in SQL Server 2019.

Understanding the Issue

When inserting data into a table with an identity column, heavy contention on the PAGELATCH_EX wait type can occur, resulting in slow performance. This can be a significant problem, especially in systems with high insert workloads.

Introducing OPTIMIZE_FOR_SEQUENTIAL_KEY

SQL Server 2019 introduces a new feature called OPTIMIZE_FOR_SEQUENTIAL_KEY, which can help alleviate contention on the identity column and improve performance. By enabling this feature on the primary key index, the insert operations are optimized for sequential key values.

Implementation

To demonstrate the impact of OPTIMIZE_FOR_SEQUENTIAL_KEY, we will create two tables: one without the feature and one with the feature enabled.

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]

Next, we will reset the wait statistics and run a stress test using the RML Utility tool:

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

After running the test, we can analyze the wait statistics to see the impact of contention on the identity column.

Enabling OPTIMIZE_FOR_SEQUENTIAL_KEY

To enable OPTIMIZE_FOR_SEQUENTIAL_KEY, we will create a similar table but with the feature enabled:

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]

We will repeat the stress test and analyze the wait statistics again to compare the performance improvement.

Results

By enabling OPTIMIZE_FOR_SEQUENTIAL_KEY, we observed a significant improvement in performance. The insert operations completed 23 seconds faster compared to the table without the feature. Additionally, we noticed a reduction in the PAGELATCH_EX wait type and the emergence of a new wait type called BTREE_INSERT_FLOW_CONTROL, which governs the new flow control of B-Tree insertions.

Considerations

It is important to note that the effectiveness of OPTIMIZE_FOR_SEQUENTIAL_KEY may vary depending on the specific workload and table characteristics. Enabling this feature on tables with low insert activity may not yield any noticeable performance improvement. Additionally, in rare cases, OPTIMIZE_FOR_SEQUENTIAL_KEY may even have a negative impact on overall performance.

Conclusion

If you are experiencing high contention on the identity column and slow performance in your SQL Server database, enabling OPTIMIZE_FOR_SEQUENTIAL_KEY on the appropriate tables can help improve performance. However, it is crucial to carefully evaluate the impact of this feature on your specific workload before implementing it.

If you need assistance with optimizing your SQL Server performance or have any questions, feel free to reach out to us. We are here to help!