I have a table (lets call it Packages) that grows continuously and its basically feed by ordersId's, a FK from the Orders table.

The thing is that due to the concurrency nature of the order process, where we only generate a package AFTER you pay your order, the table grows "not straightly"...
If you query the Packages table now, the last orderId on the table will be 12345, and if you query again in a couple of minutes it WILL BE a bigger number, like 12350, but that doesn't mean that the records were inserted in order... perhaps 12350 was the first person to pay and the next one will be 12346...

Is this scenario/table a good candidate for OPTIMIZE_FOR_SEQUENTIAL_KEY?

I have a table (lets call it Packages) that grows continuously and its basically feed by ordersId's, a FK from the Orders table.

The thing is that due to the concurrency nature of the order process, where we only generate a package AFTER you pay your order, the table grows "not straightly"...
If you query the Packages table now, the last orderId on the table will be 12345, and if you query again in a couple of minutes it WILL BE a bigger number, like 12350, but that doesn't mean that the records were inserted in order... perhaps 12350 was the first person to pay and the next one will be 12346...

Is this scenario/table a good candidate for OPTIMIZE_FOR_SEQUENTIAL_KEY?

• sql-server
• performance
• indexing

• How many rows fit on a single 8kb page of the table, and how far apart are inserted rows likely to be? Makes sense only when the last page is always the one being inserted. But if you fit 20 rows on the page, and you are often 40 or 60 rows apart then you won;t be on the same page, so the optimization doesn't help you. – Charlieface Commented Feb 3 at 19:05
• Are you seeing the latch contention issue that this option was introduced to mitigate? – Martin Smith Commented Feb 3 at 19:06

1 Answer 1

Is this scenario/table a good candidate for OPTIMIZE_FOR_SEQUENTIAL_KEY?

No. Listen to Pam Lahoud:

You should only use this option if you have a very heavily contentious workload – one where the number of threads inserting into the index is much higher than the number of schedulers – on a clustered index with a sequential key

Behind the Scenes on OPTIMIZE_FOR_SEQUENTIAL_KEY

This is, of course, in addition to seeing significant PAGELATCH_EX wait time. General troubleshooting doc for this is Resolve last-page insert PAGELATCH_EX contention in SQL Server.