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?

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.