liveBook · Manning

Optimize_for_sequential_key

As explained in 100 SQL Server Mistakes and How to Avoid Them

OPTIMIZE_FOR_SEQUENTIAL_KEY is a SQL Server index option that applies flow control to reduce contention from concurrent inserts into the last page of a sequential-key index.

Mistake: enable OPTIMIZE_FOR_SEQUENTIAL_KEY merely because an index uses a sequential key. Its purpose is narrower: reduce last-page insert contention when concurrent inserts queue for a latch on the index's final page. section 11.8.3

A delayed insert, such as one performing a page split, can hold up every insert waiting behind it. With the option enabled, SQL Server applies flow control before page-latch requests and prioritizes threads likely to finish within one processor cycle. section 11.8.3

Targeted configuration

For the ImpressionUID index on marketing.Impressions, apply the setting immediately with ALTER INDEX ... SET. No index rebuild is required. section 11.8.3

Listing 11.16 Enabling OPTIMIZE_FOR_SEQUENTIAL_KEY
ALTER INDEX ImpressionUID
ON marketing.Impressions 
SET (
   OPTIMIZE_FOR_SEQUENTIAL_KEY = ON
) ;

Use the T-SQL listing as a direct configuration pattern for enabling the option on a named index. section 11.8.3

Apply the option cautiously:

  • Use it when a sequential-key index has high insert throughput and latch demand exceeds available processor capacity. section 11.8.3
  • Avoid enabling it on indexes without throughput problems; even a sequential key can experience performance degradation. section 11.8.3

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes
Explains page-latch queues, flow control, and immediate ALTER INDEX configuration, but does not provide diagnostic procedures.
Examples
Uses the ImpressionUID index on marketing.Impressions and an ALTER INDEX SET command.
What it assumes
Assumes familiarity with indexes, sequential keys, inserts, page latches, and ALTER INDEX syntax.
What you can do after
Operate/configure the option immediately with ALTER INDEX when measured contention matches its intended use.
How it connects
Connects sequential keys with page latches, page splits, processor capacity, and index maintenance.
Cover of 100 SQL Server Mistakes and How to Avoid Them
liveBookmanning.com
Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.