Is there any issue with assigning the primary key to a rowguid column?
Thoughts?
WBIt depends...
http://www.aspfaq.com/show.asp?id=2504
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"WB" <none> wrote in message news:OOk1gk2HFHA.2456@.TK2MSFTNGP09.phx.gbl...
> Is there any issue with assigning the primary key to a rowguid column?
> Thoughts?
> WB
>|||Yes....
1. Creates a VERY Wide Index that is inefficient (Compared to INT for
example)
2. IF this is also the Clustered Index (I recommend against), Then ALL
Indexes must propogate this GUID in their lookup tables
3. Clustering on GUID can cause page splits,etc
Company I recently worked at just added an identity column to all tables in
DB and clustered on the Ident instead of the GUID. Inserts of records sped
up tremendously. I cant quote the exact number of records, bu the "Process"
went form 17 minutes to literally seconds...
Food for thought
Some companies (My current employer for example) want to use a GUID type of
identifier as opposed to integers for whatever reason. So what they do, to
ensure that the GUID values are Monotonically increasing in value (to avoid
page splits), is the create their own custom "GUID" type data generator in
which the algorithm ensure incrementing values.
Greg Jackson
PDX, Oregon
"WB" <none> wrote in message news:OOk1gk2HFHA.2456@.TK2MSFTNGP09.phx.gbl...
> Is there any issue with assigning the primary key to a rowguid column?
> Thoughts?
> WB
>|||Cluster Index definition on surrogate keys are ALWAYS a bad idea, INT or
GUID. Usually, the Business Key the surrogate is proxiing for is a better
Cluster Index candidate.
Sincerely,
Anthony Thomas
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:uaC7za3HFHA.1948@.TK2MSFTNGP14.phx.gbl...
Yes....
1. Creates a VERY Wide Index that is inefficient (Compared to INT for
example)
2. IF this is also the Clustered Index (I recommend against), Then ALL
Indexes must propogate this GUID in their lookup tables
3. Clustering on GUID can cause page splits,etc
Company I recently worked at just added an identity column to all tables in
DB and clustered on the Ident instead of the GUID. Inserts of records sped
up tremendously. I cant quote the exact number of records, bu the "Process"
went form 17 minutes to literally seconds...
Food for thought
Some companies (My current employer for example) want to use a GUID type of
identifier as opposed to integers for whatever reason. So what they do, to
ensure that the GUID values are Monotonically increasing in value (to avoid
page splits), is the create their own custom "GUID" type data generator in
which the algorithm ensure incrementing values.
Greg Jackson
PDX, Oregon
"WB" <none> wrote in message news:OOk1gk2HFHA.2456@.TK2MSFTNGP09.phx.gbl...
> Is there any issue with assigning the primary key to a rowguid column?
> Thoughts?
> WB
>|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uPDlrHwKFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Cluster Index definition on surrogate keys are ALWAYS a bad idea, INT or
> GUID. Usually, the Business Key the surrogate is proxiing for is a better
> Cluster Index candidate.
There's no such thing as "usually". There are plenty of applications
for either type of clustering key -- and clustering on a sequential integer
can be especially beneficial in many situations. You should be very careful
with that assumption.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Name one.
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OYusbfwKFHA.3132@.TK2MSFTNGP12.phx.gbl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uPDlrHwKFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Cluster Index definition on surrogate keys are ALWAYS a bad idea, INT or
> GUID. Usually, the Business Key the surrogate is proxiing for is a better
> Cluster Index candidate.
There's no such thing as "usually". There are plenty of applications
for either type of clustering key -- and clustering on a sequential integer
can be especially beneficial in many situations. You should be very careful
with that assumption.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%23gGre3wKFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Name one.
Inserting data.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Yes, and Inserting Data in Business Key order shouldn't cause an inordinate
amount of page splits if you have your fill factors and database maintenance
routines set correctly. And, having the Cluster Index on the Business Key
will far outweigh any split issues compared to any scans you may encounter
and gather the covering aspects of the clustered index plus it is typlically
the Business Key that most range queries are conducted on.
Care to try again? Are you really telling me that page splits is the BEST
reason you can come up with? Please. Are you really telling me that was
the whole design goal with having Clustered Indexes over Heaps to begin
with? You've got to be kidding me.
Can I get a witness?
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OuxM5KxKFHA.4052@.tk2msftngp13.phx.gbl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%23gGre3wKFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Name one.
Inserting data.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eCMwAS5KFHA.2860@.TK2MSFTNGP10.phx.gbl...
> Can I get a witness?
No. Never worked in a 24-7 (4-nines, and we're trying for 5 this year),
high-volume OLTP environment, have you? Good luck maintaining those fill
factors when you have no maintenence window.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||http://www.dbta.com/columnists/crai...orner_0902.html
http://www.craigsmullins.com/dbta_026.htm
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OH8L1F%23KFHA.2748@.TK2MSFTNGP09.phx.gbl...
Since you brought it up...well, and I brought it up, the topic of SLAs and
availability metrics is an interestin topic in its own right.
Craig Mullins has some good articles on the topic you should check out.
http://www.craigsmullins.com/dbta_006.htm
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eXByZ$9KFHA.3832@.TK2MSFTNGP12.phx.gbl...
Would agree that the DBREINDEX can be troublesome in highly available
systems. But the fill factors should be static from a design perspective.
Also, the DBREINDEX is semi-online in that it only needs to obtain exclusive
table access only on the table it is currently running against. Running the
Cluster Index rebuild WITH DROP EXISTING can speed up the process.
But then, again, that is a management issue. The 5 9's do NOT refer to
TOTAL TIME, but to SLA time. And, EVERY SYSTEM HAS TO HAVE AN SLA, which
would include a maintenance window. If you don't have an APPROPRIATE SLA,
then your 5 9's don't mean very much. But, yes, we maintain 5, and even 6,
9's of availability, but properly measured against reasonable SLAs that have
actually been documented and constructed with the various Business Units
that are demanding the system availabilities.
I suppose that if your SLAs DEMANDED 24x7x52, with NO MAINTENANCE, as
delusional as the Business Units may be, then sacrificing performance over
page splits would be one reason to not care about the Cluster Index
placement. But, then, why bother and just use a HEAP. You're not going to
get much better performance from an ill-chosen Clustered Index. Some, but
not much.
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Ra$B$8KFHA.2252@.TK2MSFTNGP15.phx.gbl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eCMwAS5KFHA.2860@.TK2MSFTNGP10.phx.gbl...
> Can I get a witness?
No. Never worked in a 24-7 (4-nines, and we're trying for 5 this year),
high-volume OLTP environment, have you? Good luck maintaining those fill
factors when you have no maintenence window.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment