Minimizing PAGELATCH_EX waits in SQL Server 2019

By: Aaron Bertrand | Updated: 2021-07-23 | Comments | Related: > SQL Server 2019

Problem

There is a common problem in SQL Server where contention when writing to a single page can lead to excessive waiting. This waiting comes in the form of the wait type PAGELATCH_EX, and often happens when the clustering key is a monotonically increasing value – like an IDENTITY or date/time column. Pedro Lopes has highlighted some non-trivial workarounds in his post, "PAGELATCH_EX waits and heavy inserts," and there is some official guidance in the Microsoft Docs article, "Resolve last-page insert PAGELATCH_EX contention in SQL Server." But are there any ways to address this issue without intrusive changes?

Solution

SQL Server 2019 introduces a new index setting, OPTIMIZE_FOR_SEQUENTIAL_KEY, which attempts to reduce this contention by better managing processes as they are inserting rows. More precisely, this setting allows the engine to prioritize threads that are requesting exclusive latches on pages. Without getting too technical, work is prioritized based on the state and speed of the thread, as well as the scheduler it is on.

Pam Lahoud talks a bit more about the underlying mechanisms in her blog post, "Behind the Scenes on OPTIMIZE_FOR_SEQUENTIAL_KEY." But I wanted to look at this from a practical perspective and demonstrate how throughput might (or might not) be improved.

Let’s say you have a table with an IDENTITY column:

CREATE TABLE dbo.HotSpotTable
(
  HotSpotID  int IDENTITY(1,1),
  ver        tinyint,
  dt         datetime2(7) NOT NULL DEFAULT sysutcdatetime(),
  CONSTRAINT PK_HotSpot PRIMARY KEY CLUSTERED (ID)
);

Then let’s set up two different types of inserts – one that should be about as fast as possible, and the other with an artificial delay (to mimic queries that may be slow for any number of other reasons):

CREATE PROCEDURE dbo.InsertFast
AS
BEGIN
  SET NOCOUNT ON;
  INSERT dbo.HotSpotTable(ver) VALUES(1);
END
GO

CREATE PROCEDURE dbo.InsertSlow
  @ver tinyint = 2
AS
BEGIN
  SET NOCOUNT ON;
  ;WITH a(ver) AS 
  (
    SELECT TOP (101) ver = @ver
      FROM master.sys.all_columns 
      ORDER BY NEWID()
  )
  INSERT dbo.HotSpotTable(ver) 
    SELECT TOP (1) a.ver FROM a;
END
GO

Now, let’s test the duration of a set of threads inserting 1,000 rows each, using one method or a mix of both. Presumably, these threads should exhibit PAGELATCH_EX waits as they fight each other to write to the last page.

I used SqlQueryStress for these, since it is very easy to tinker with the number of threads, but any script or tool that can generate concurrent workload will be fine. I ran the following sets of tests with the default setting for the clustered index, dropping and recreating the table each time:

Mix of tests - number of threads, transaction type, and total number of rows inserted

I measured the elapsed time and also all of the top wait stats that were accrued during the session. Here is a quick recap of the results:

Elapsed time and most prominent wait times from first set of tests.

The most relevant waits were PAGELATCH_EX, SOS_SCHEDULER_YIELD, and WRITELOG. PAGELATCH_EX waits were only relevant when either all or some of the transactions were fast. When all of the transactions were slow, those waits were completely absent. Similarly, the scheduler-related waits were absent when all of the transactions were fast.

Now, let’s run all the tests again after changing the table to use the new OPTIMIZE_FOR_SEQUENTIAL_KEY setting:

DROP TABLE IF EXISTS dbo.HotSpotTable;
GO

CREATE TABLE dbo.HotSpotTable
(
  HotSpotID int IDENTITY(1,1),
  ver       tinyint,
  dt        datetime2(7) NOT NULL DEFAULT sysutcdatetime(),
  CONSTRAINT PK_HotSpot PRIMARY KEY CLUSTERED (ID)
    WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON)
);
GO

While I did this in an intentionally disruptive way for my tests, you don’t actually have to drop and re-create the table, and you don’t even have to rebuild the index. When you think about it, we’re not changing anything about the structure of the index, we’re only changing the way we manage write activity to the index pages. I could have accomplished this change with the much simpler:

ALTER INDEX PK_HotSpot ON dbo.HotSpotTable 
  SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);

…and you could, too – turning it on and off for testing without going through the pain of a rebuild or drop and create.

Anyway, after this change, here are the results (green is an improvement):

Similar elapsed time and wait measurements with the new option in place.

Elapsed times are all either very close or better, as are most of the wait times. The most visible improvement in PAGELATCH_EX waits were at the higher concurrency levels and, in those tests, log write durations notably improved as well.

If your workload is comprised solely of slower inserts already, and PAGELATCH_EX isn’t the reason those inserts are slower, there isn’t much this setting can do. However, it is possible that, if PAGELATCH_EX is your bottleneck, you can alleviate this pressure by moving from a resource you can’t change (like your disk subsystem) to one that you can (more and/or faster CPUs, particularly easy in a PaaS or VM environment). Keep in mind that I ran this on a lowly 8-core machine with 128 GB of memory, but with fast disks, and the results might be drastically different on a proper server you are using.

Conclusion

This is another in a long list of compelling reasons to explore an upgrade to SQL Server 2019, even if you are planning to also address this type of contention in other ways. As Pam outlines in her blog post, some of the other methods may have more significant impact. In fact, this option may actually cause throughput degradation in cases where this isn’t the true bottleneck, which is why it isn’t just turned on by default. Still, it can only be a tool in your performance troubleshooting arsenal once you upgrade.

Next Steps

See these tips and other resources involving SQL Server 2019:

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-07-23