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?
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.