Introduction of OPTIMIZE_FOR_SEQUENTIAL_KEY = ON

Question

Post reply

Introduction of OPTIMIZE_FOR_SEQUENTIAL_KEY = ON

saum70

Ten Centuries

Points: 1002
More actions
January 16, 2026 at 2:37 pm

#4717707

Hi,
We have low latency high volume system.
I have a table having 3 columns (2 int and 1 bigint) as primary non clustered key . This table is expected to store 3o million + records each day. At the end of the day the table will be cleared empty. Now the record in this table will be inserted using BCP from VC++ application and it is expected that 200000 records per second will be inserted in this table.
What i have read online is to introduce another column (Identity) as clustered index that will monotonically increase for each record with OPTIMIZE_FOR_SEQUENTIAL_KEY = ON. This will reduce the page latch wait types thus increasing the efficiency of insertion which .
Is this correct implementation.
Regards,
Saumik

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247210 More actions · Answer 1

Phil Parkin

SSC Guru

Points: 247210

January 16, 2026 at 2:53 pm

#4717708

How will the data be used, other than for INSERTs?

Johan Bijnens SSC Guru Points: 135652 More actions · Answer 2

will there be concurrent activities?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data and code to get the best help

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Who am I ? Sometimes this is me but most of the time this is me

saum70 Ten Centuries Points: 1002 More actions · Answer 3

So there are be possibilities of referring the data from that table through stored some procedures whilst the data is inserted continuously. In that table there will only be bcp inserts. no update or delete at the time when inserts will happen. when update and delete happen, insert will not take place.

Regards,

Saumik Vora

Jeff Moden SSC Guru Points: 1004701 More actions · Answer 4

saum70 wrote:

...will be inserted using BCP from VC++ application...

I'm definitely not a front-end kind of guy... When you say "BCP", what are you talking about??? Single row inserts?

The reason I ask is because there's a "BCP.exe" program that comes with SQL Server that has a very high rate of speed for converting data in files and inserting them into SQL Server tables. I want to make sure I know what you're talking about.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dbruton95 SSC Journeyman Points: 89 More actions · Answer 5

I think the answer is probably No. Although it's hard to say for sure without knowing your implementation. If I can assume that when you look up data in the table you're using the "(2 int and 1 bigint)" primary key and you don't need any other indexes on the table, you should simply cluster on the primary key. If you add the clustered identity key, you don't get to avoid maintaining the PK which will be just as expensive whether or not you have the clustered identity key. So the BCP will go fast into the clustered index but it wont be fast into the PK. The clustered index just becomes dead weight - an additional object you need to maintain to enforce an order that has no practical purpose.

If you really need to insert fast, one other possibility would be to leave the clustered identity key but drop the PK before you run the BCP job (actually before you do the delete) and then recreate it (online=on) when the BCP is done. Your seeks will be slow during the import/rebuild process but maybe that's OK.

I'd be interested hear what Jeff and the other commenters think about this.

ScottPletcher SSC Guru Points: 101231 More actions · Answer 6

> 3 columns (2 int and 1 bigint) <

This could be a (rare) case where partitioning is a good idea; partitioning being based on one/both of the 2 int leading key columns. Be sure to test that SQL is accurately excluding partitions when you do seek based on the leading key(s).

This would allow you to spread both the insert and the lookup across multiple files if you wanted to, reducing insert contention.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".