Enabling Optimize For Sequential Key For A SQL Server Index

Sweetness, Shortness

Recently I blogged about last page contention solutions, and one thing I mentioned is that turning on OPTIMIZE_FOR_SEQUENTIAL_KEY doesn’t require you to rebuild an index.  That’s awesome, because a whole lot of changes to indexes require you to rebuild them.

So how exactly do you do that?

Either when you create the table:

CREATE TABLE dbo.Votes_Insert
(
    Id INT IDENTITY(1, 1) NOT NULL,
    PostId INT NOT NULL,
    UserId INT NULL,
    BountyAmount INT NULL,
    VoteTypeId INT NOT NULL,
    CreationDate DATETIME NOT NULL,
    CONSTRAINT PK_Votes_Insert_Id
        PRIMARY KEY CLUSTERED (Id ASC) 
    WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY  = ON) 
);

When you create the index:

CREATE INDEX so_optimized 
    ON dbo.Votes_Insert (Id) WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);

Or if you need to alter an existing index:

ALTER INDEX so_optimized 
    ON dbo.Votes_Insert SET(OPTIMIZE_FOR_SEQUENTIAL_KEY  = ON);

Get Back To Work

You’ll find this post again in a few years when you finally migrate to SQL Server 2019.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.