October 1, 2025 at 12:00 am
Comments posted to this topic are about the item Unlocking High-Concurrency Inserts in SQL Server with OPTIMIZE_FOR_SEQUENTIAL_KEY
October 1, 2025 at 10:45 am
This is a good explanation of the contention problems when using sequential keys. This problem has been a problem with SQL Server since the days of SYBASE.
I've experienced this with multiple applications over the years. I've eliminated the problem by requiring all new development to use GUIDs for all keys. And if the problem has occurred with legacy applications, I just initiate an easy fix by adding a new GUID column to the table with a default of NEWID(). Then I add a clustered alternate key index and this issue is eliminated.
For new applications, if the design requires a sequential key for useability, simply add that as a nonclustered alternate key to keep the end users happy.
Using OPTIMIZE_FOR_SEQUENTIAL_KEY is a better solution than doing nothing, but I've worked with Microsoft and SQL Server for a long time, and I don't like putting my trust in a complicated solution from them that might impact Query Execution Plans when there is an easy architectural solution that works better.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply