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!