SQL Server Performance Forum – Threads Archive
GUID PK design
MS SQL 2k Enterprise. 200mb DB.<br /><br />Our system is all GUID PK. Let me preempt the otherwise imminent debate over GUID PK vs INT/BIGINT PK by saying that our requirements demand GUID for several reasons: global uniqueness, 2-way replication (we also do 1-way), security, and more.<br /><br />The db is at present very small. We have less than 1 million total records w/ 500-750 transactions per day.<br /><br />Most of our SELECTs are hitting views which consist of much JOINing on GUID FK-><img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />K.<br /><br />We’re seeing a pretty serious performance hit that we’ve not been able to figure out, but I imagine it has something to do with our index setup.<br /><br />We ran ITW on all tables in the db, unchecked keep existing indexes, and did a thorough analysis. After ITW did all the calculating of candidates n such, the job completed instantly after that. I expected it to run some scripts on the DB for a while after it’s calculations. Is it supposed to work like this?<br /><br />Should I be using a specific JOIN hint for JOINing on GUID FK-><img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />K?<br /><br />Anyone working with a system with all GUID PK? Need some advice..GUID primary keys could solve your problem, but at an intolerable performance cost. For the most part, system-generated key values need to be unique only to a single server. In most situations, changing an auto-incrementing primary to a GUID has no advantage, only the following disadvantages: GUID values are up to four times larger than the alternatives Server’s Identity columns so GUIDs require more overhead. GUID values take longer to search and sort. Consequently, a GUID primary key suffers a performance penalty that cascades throughout the database. The number of characters in and the awkward composition of the actual GUID values themselves make them hard to remember or work with directly, although neither is really necessary when used as a primary key value.
The GUID datatype can assist with a number of database challenges: 1.Global databases that must guarantee unique key values across many servers
2.Replicated databases that must guarantee unique key values across many servers
3.Large databases that have outgrown incremental primary key values
4.Companies faced with combining database files where primary key values are duplicated
read more on this
http://www.devx.com/dbzone/Article/10167/1954?pf=true
]]>