We help IT Professionals succeed at work.

the GUID column of SQL server 2008

marrowyung
marrowyung asked
on
Dear expertist,

Right now we find a performance problme on SQL 2008 table that use GUID as the clustered primary key.

It seems it is an unsolvable problem, any way to get ride of it?

DBA100.
Comment
Watch Question

AnujSQL Server DBA
Top Expert 2011
Commented:
This is because GUID takes 16 bytes for storage and make the index key wider, Usually GUID is not a good candidate for Clustered Index, When you create Primary key by default it get created as clustered index, Now the option is to drop the Primary key and recreate it as Non-Clustered Index or change it to non clustered index  and Choose some other columns of that tables as Clustered Index(Unique is the best). Recreating or dropping the Clustered indexes takes time as all the non clustered index are rebuild. So its takes time for large tables.

Hope this article will helps you about GUID index problems
AneeshDatabase Consultant
Top Expert 2009
Commented:
try using SEQUENTIALID() instead..
anyoneisSoftware Developer
Top Expert 2006

Commented:
Guid is a horrible, worst case choice for a clustered index. You are splitting pages every time a new record is inserted, leading to fragmentation and inefficient lookups. A clustered index key should be narrow and monotomically increasing. Use an identity column for the clustered key, and have another index for the GUID that you use to actually locate records. You will find that space requirements decrease, and performance increases.

David
marrowyungSenior Technical architecture (Data)

Author

Commented:
aneeshattingal:

You mean using SEQUENTIALID() for the value on that colume instead of GUID ? That function only availble on SQL 2012 or SQL 2008 SP3?

anyoneis:

so you mean on that colume with GUID to replace with identity colume? or SEQUENTIALID() as what aneeshattingal said ?

I don't understand why spliting page everytime a new record is insert, please explain further. It seems that rebuilt index can help on this if defragementation can help, right?

DBA100.



DBA100.
In General, if the Column in the Table have been specified as Cluster Index. The records in the table will undego physical arangment based on the value for the faster search. that's the Clsuter Index is always recommended on Integer Type as it be whole numebr and arrangment and querry record based on the key will be good.

If a table have a clustered Index, then when every Insert happen to the table will go for updating the Index table of the Physical table to create the reference. So if the column is Integer Type then the updating the Index table will take place in seconds

Take a look into the below link for how much it will impact
http://deepakrangarajan.blogspot.com/2009/02/sql-server-indexes-and-dml-operations.html

In your case, the clsutered Index column in GUID(), which don't follow any pattern on creating the next record id
(i.e if the column type is Integer then we can predict the next Id as next Integer number), It's combination of 5 parts. Take a look into this on GUID foramtion
http://en.wikipedia.org/wiki/Globally_unique_identifier

Since the new GUID created is at the machine level,  The impact of the perfomance will be high as it have to undergo Index table update every time.

Take a look in to the Kimberly explanation for more info
http://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx



marrowyungSenior Technical architecture (Data)

Author

Commented:
Dear all,

Do Oracle have this kind of problem ?

We also want to know if Oracle has advantage in this area over MS SQL?

DBA100.
Take a look in to the below link on sys_GUID in oracle

http://feuerthoughts.blogspot.com/2006/02/watch-out-for-sequential-oracle-guids.html

for more information and the performance in Oracle be http://www.dba-oracle.com/art_9i_indexing.htm
Top Expert 2012

Commented:
>>That function only availble on SQL 2012 or SQL 2008 SP3?<<
You really need to do your homework.  It is available since SQL Server 2005.

Software Developer
Top Expert 2006
Commented:
A clustered index implies that the rows are physically ordered on the clustered key. So if you use a guid, you essentially have a random key, and you are almost never going to be inserting at the next logical free spot. Instead, you are likely going to be inserting between two existing records. You can mitigate the splitting a bit by specifying a lower fill factor, telling SQL server to reserve more space in each page. However, eventually you will need to split the page, and you will likmely always have to move a bunch of data to do your insert. A monotonically increasing key, such as an identity column, will give you a smoother insertion pattern with no splitting. The GUID as a non-clustered key is fine - no need to replace it with a SequentialID.

I agree with santhimurthyd  that the reference to Kimberly Tripp's article is probably the bext place to get a clear explanation.
marrowyungSenior Technical architecture (Data)

Author

Commented:
anyoneis:

This make a lot of sense to me as the GUID originally is not in order, that's why a new GUID created, we don't where the clustered key created and it MAY have a lotof chance that it has to insert between physical page, as the clustered key will be very large, page split will happen, is that right ?

I think this one can also help: http://www.sql-server-performance.com/2011/tsql-sql-server-2011-features/4/.

DBA100.
anyoneisSoftware Developer
Top Expert 2006

Commented:
marrowyung, I think you understand it.

I had not seen the Sequence object before. It looks interesting, but I am not sure where I would use it.

David
marrowyungSenior Technical architecture (Data)

Author

Commented:
David,

Thanks.

Thanks for everyone.

DBA100.
marrowyungSenior Technical architecture (Data)

Author

Commented:
I attended the TechEd about the SEQUENTIALID(), it exists since SQL 2012.
Top Expert 2012

Commented:
I attended the TechEd about the SEQUENTIALID(), it exists since SQL 2012.
Then you appear to have totally misunderstood what was presented:  
NEWSEQUENTIALID() has been around since SQL Server 2005 and unlike NEWID() is a an incremental GUID that avoids some of the problems of using NEWID() as a clustered index.  You can read about it here:
http://msdn.microsoft.com/en-us/library/ms189786(v=sql.90).aspx

What SQL Server 2012 introduced was a SEQUENCE object (not a function).  This allows you to create an incremental numeric (not a GUID) that is global (and not tied to a table).  You can read about it here:
http://msdn.microsoft.com/en-us/library/ff878091.aspx
marrowyungSenior Technical architecture (Data)

Author

Commented:
what I read is "ry using SEQUENTIALID() instead.. "

it looks so familiar to me once I read the SQL 2012 information.

The articles say:

"Creates a GUID that is greater than any GUID previously generated by this function on a specified computer"

This means the GUID will becomes longers and the colume need larger and I don't understand that. please explain your idea.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
not longer in size. but larger (higher) in regards to the plain > comparation.
marrowyungSenior Technical architecture (Data)

Author

Commented:
I am wondering how NEWSEQUENTIALID()  can solve this problem .

any example ?
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
see uniqueidentifier about the "format":
http://msdn.microsoft.com/en-us/library/ms187942.aspx

internally, it's some datetime part, some machine "id" part, and some "random" part.
for the newsequentialid, this is solved a bit differently, but still ...
hope this helps
marrowyungSenior Technical architecture (Data)

Author

Commented:
angelIII,

"but still ..."

oh,, it means it still got problem.

We are trying to take it off as this is the old design. AHAHAHA
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
well, as written previously:
UUID field is usually not the field to be the clustered index.
change that, and the problem should be solved?!
marrowyungSenior Technical architecture (Data)

Author

Commented:
angelIII,

I trust you all well and I dont' think we will do this any more as as previously stated, We are trying to take it off as this is the old design.

Thanks.