OPTIMIZE_FOR_SEQUENTIAL_KEY – The Key to Success?

If you’re running into problems caused by concurrent inserts and you’re on SQL Server 2019 or above, it’s worth testing out OPTIMIZE_FOR_SEQUENTIAL_KEY. This may alleviate those issues by handling inserts a little bit differently to reduce contention. OPTIMIZE_FOR_SEQUENTIAL_KEY will “control the rate at which new threads are allowed to request the latch, and favor threads that are likely to keep the throughput high.”

Enabling OPTIMIZE_FOR_SEQUENTIAL_KEY

Let’s say we want to include OPTIMIZE_FOR_SEQUENTIAL_KEY with a new table storing super heroes spilling out of the multiverse. We could reference OPTIMIZE_FOR_SEQUENTIAL_KEY like this:

CREATE TABLE Hero (
	Hero_Id INT IDENTITY(1, 1) NOT NULL
	,Hero_Name VARCHAR(100) NOT NULL
	,Alter_Ego VARCHAR(100)
	,Universe VARCHAR(100) NOT NULL
	,CONSTRAINT PK_Hero_Id PRIMARY KEY CLUSTERED (Hero_Id) 
		WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON)
);

We can’t have heroes without villains. If we wanted OPTIMIZE_FOR_SEQUENTIAL_KEY on a similar Villains index, we can add it by running:

CREATE INDEX Villain_Id ON Villain (Villain_Id)
	WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);

Safe Everywhere?

Someone may think to themselves “I’ll turn use OPTIMIZE_FOR_SEQUENTIAL_KEY on every server where I have sequential inserts because it’s sure to help.” That’s not true. Just because a feature loosely applies to your environment doesn’t mean it’s time to instantly turn it on.

Consider something like the “optimize for ad hoc workloads” setting. Sure, it may sound like a common sense solution for optimizing your workload on all of your SQL Servers. Is it though? Check out this blog post from Randolph West and this blog post from Erik Darling to learn where there can be problems.

What about disk space? It’s great to have plenty of free disk space for database growth. Does this mean you should be shrinking all of your database files every night to gain as much free disk space as possible? Of course not.

OPTIMIZE_FOR_SEQUENTIAL_KEY falls into this category of settings that are nice to use when you need them but may do more harm than good when you don’t.

The Right Time

The right time to test if OPTIMIZE_FOR_SEQUENTIAL_KEY is right for you is if you have a workload where “the number of threads inserting into the index is much higher than the number of schedulers” on an index with a sequential key. If this sounds like it applies to you, give OPTIMIZE_FOR_SEQUENTIAL_KEY a shot; otherwise, you’re probably better off without it.

The Behind the Scenes on OPTIMIZE_FOR_SEQUENTIAL_KEY post that I quoted and linked a few times above is worth the full read so I highly recommend checking that out.

Thanks for reading!