November 27, 2002 at 7:27 am
At the recent PASS conference in Seattle, during the session titled "Point/Counterpoint - Using Identities," someone mentioned something about a 400 row per second limitation when inserting records in a table. Does anyone remember the details of this comment?
Thanks,
Scott G.
December 2, 2002 at 8:00 am
This was removed by the editor as SPAM
December 2, 2002 at 10:54 am
One of the MS people who works in data warehousing, mentioned that at 400 rows per sec, for inserts, the identity bottlenecks and the server has problems going above.
Not sure if this is hardware dependent, but because the value is stored in SQL Server and incremented, I suspect this is a point at which the server has issues regardless of hardware (at least at this time). Not sure if faster CPU/RAM might alleviate this. Their recommnendation was to use a GUID.
Steve Jones
December 2, 2002 at 4:02 pm
One of the more interesting comments from the audience I'd say. I am curious what effect hardware has on this number. What did you think of the presentation?
Andy
December 2, 2002 at 7:15 pm
That is most likely an average. Unfortunately I did not attend but I am familiar with Identity and how it works. It has to place a lock on the storage row to do the insert to prevent the next insert from getting the same, then it increments the value, releases the row and does it's insert. Overall, i still believe 400/sec is average on hardware platforms, probably accounting for most common setups. The biggest factors will be Memory and Harddrive as most IO contention come from those areas.
December 3, 2002 at 9:53 pm
Another consideration here is if the IDENT is the PK and is CLUSTERED, the inserts all single thread through a single physical page. Our DB standards say if you PK is the IDENT col, then make it non-CL and make of the data indexes clustered instead. Just be aware that clustering implies a physical ordering of the rows inthe table. Be careful with ANY clustered index for any table that has an unusually high insertion rate and requires appropriately exceptional performance.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply