Ding Ding Ding Ding Ding!
That's for explaining it, so I didn't have to. I'm worn out this week.
.....................
And sometimes we build our relationships OUTSIDE of the database, and shoot
them in via xml.
And we use a surrogate key......and with set based inserts......a
consecutive guid would be beneficial.
We have been relying on the newsequentialid (as the default value for the
uniqueidentifier primary key), but I was going to experiment with creating
the surrogate key outside of the database and pushing it in instead.
..............
This was experimentation stuff. And trying to figure out if having them
generated outside of the db provided any benefit.
One of the biggest wait stats we have is
PAGELATCH_EX.
I'm not a dba, I'm not trying to experiment to see if the "outside
sequential guid" creation might help.
Maybe someone can comment on the
PAGELATCH_EX ... and if I'm barking up the wrong tree or not.
PAGELATCH_EX
True
Occurs when a task is waiting for a latch for a buffer that is not in
an I/O request. The latch request is in Exclusive mode.
Contention can be caused by issues other than IO or memory
performance, for example, heavy concurrent inserts into the same index range
can cause this kind of contention. If many inserts must be added on the same
page, they are serialized using the latch. Lots of inserts into the same
range can also cause page splits in the index which holds onto the latch
while allocating a new page (this can take time). Any read accesses to the
same range as the inserts would also conflict on the latches. The solution
in these cases is to distribute the inserts using a more appropriate index.
"Anthony Jones" <An***********@yadayadayada.comwrote in message
news:eY**************@TK2MSFTNGP04.phx.gbl...
"John Saunders" <no@dont.do.that.comwrote in message
news:uv**************@TK2MSFTNGP02.phx.gbl...
>>I don't understand - why do you need them to be sequential? Is it not
enough that each one is unique?
If you are using them as a unique or primary key in a database such a SQL
server their highly random nature makes them less than desirable. Hence
SQL server provides the newsequentialid function which generates a form of
Guid which more predictable and when compared each new id is than the
previous. This increases slightly the risk of collision but since the
scope of use is so narrow its beyond worrying about and yet provides a
good basis for a clustered key.
--
Anthony Jones - MVP ASP/ASP.NET