3

SQL Server 2019 brings the OPTIMIZE_FOR_SEQUENTIAL_KEY option that, as far as I know you specify on index creation or alter using the WITH keyword.

But how do you check if the option is already turned on for a given index?

And is it on by default in Server 2019? Or Azure?

Tessaract
  • 167
  • 2
  • 7

1 Answers1

9

The system view sys.indexes has a bit column named optimize_for_sequential_key.

It is not on by default because it is intended for use only where significant last-page contention is observed.

See Implementation and contraindications for OPTIMIZE_FOR_SEQUENTIAL_KEY for more details.

Paul White
  • 94,921
  • 30
  • 437
  • 687