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!
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.
- How SQL Server 2019 Helps You Find Queries That Have Missing Index Requests
- How SQL Server 2019 Helps You Link Queries To Missing Index Requests
- An Overlooked Benefit Of Batch Mode With Windowing Functions In SQL Server
- Batch Mode On Row Store vs Batch Mode Tricks For Performance Tuning SQL Server Queries