Press "Enter" to skip to content

When to Use OPTIMIZE_FOR_SEQUENTIAL_KEY

Chad Callihan explains a feature:

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

Read on to see how you can enable it but also when it makes sense to enable it.