Enabling Optimize For Sequential Key

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!



One thought on “Enabling Optimize For Sequential Key

Leave a Reply

Your email address will not be published. Required fields are marked *