SQL Server Performance

uniqueidentifier/Index performance

Discussion in 'Performance Tuning for DBAs' started by PAMUR, Sep 6, 2005.

  1. PAMUR New Member

    Dear All,

    Forgive my ignorance in asking you this. The uniqueidentifier's length is only 38 characters right. Irrespective of nationality and the usage of native language the uniqueidentifier's length is 38 according to my knowledge. My colleague refutes this and says it should be 255 charaters. The newID function does only create unique codes with in this 38 character length isn't it?

    And please explain its performance if its indexed and clusterd.

    Thank you and My advanced gratitude.
  2. dineshasanka Moderator

  3. PAMUR New Member

    Hi Dinesh,

    If the field is of nvarchar and its default value is ('{' + convert(nvarchar(255),newid()) + '}')
    even then the length should be of that irrespective of the nationality right!

    Thank you
  4. benwilson New Member

    I agree- from BOL it is hexidecimal and has the form 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' which is 36 characters...the length isnt going to change based on regional settings as far as i can see! And converting it to nvarchar is only going to use the characters it needs
    '{' + 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' + '}' = 38 Characters

    'I reject your reality and substitute my own' - Adam Savage
  5. dineshasanka Moderator

  6. satya Moderator

    I feel generally these type of columns will be used as Clustered Indexes. And using GUID's for Clustered indexes pose their own problem. GUID's would induce page splits if we have not definied FILL_FACTOR.

    Re-organizing the index pages is something costlier, time and resource consuming. We would not like to do this often. And this is not suitable for a OLTP applications atleast. But the counter arguement would be that running numbers usage will induce Hot spots on OLTP applications on high running transactions.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. FrankKalis Moderator

    Here's the rewrite of a tip on the site about GUIDs and clustered indexes

    If possible, avoid creating the clustered index on a GUID column (uniqueidentifier data type). Here are some reasons:

    - GUIDs take up 16 bytes of storage, which is, for example, 4 times as much as an INTEGER column will consume. GUIDs will make your index larger, which in turn causes that fewer rows could be saved on a single page. SQL Server will have to perform more IO operations to fetch all relevant data, which might negatively affect performance.

    - The clustered index keys are contained in the leaf level of every non-clustered index on the same table. So, when using a GUID you also wide your non-clustered indexes unnecessarily, slowing down performance.

    - GUIDs are random in nature. That means, you do not know, if the next GUID will have to be sort before or behind the current one. So, you either have to leave enough room on a page to allow new rows to be inserted there and "waste" storage space or you might have to deal with excessive page splitting on a busy server.

    - GUIDs are non-intuitive to humans. That in turn means that in most cases there should be better candidates available for the clustered index. It is unlikely that your most critical queries will run against those data or you need to frequently sort or group on them. Because a wisely chosen clustered index is crucial to performance, you better "save" the clustered index for a better suited column (or set of columns).

    - When you need to use GUIDs for one reason or the other (replication scenarios, for example), and you decide to make a GUID column the PRIMARY KEY of your table, uncheck the "Create as Clustered" option, when you create the table with Enterprise Manager or explicitely specify NONCLUSTERED when you create the table's PRIMARY KEY via Query Analyzer. Otherwise SQL Server will automatically make your PRIMARY KEY also the clustered index when there is no clustered index existant on the table at the time when you create the PK constraint.

    - Finally another, maybe less important reason, might be, that GUIDs are proprietary to SQL Server (the IDENTITY property is so, too, btw). They are not ANSI conform and not portable

    Frank Kalis
    Microsoft SQL Server MVP
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  8. PAMUR New Member

    Thanks to all of you. Then I can happily reduce the nvarchar Guid field to 38 character length and create NON Clustered Index on it. Atleast I will improve performance by reducing the lenght of the field and save resources.

    Hope that my Indexes will perform better after this.

    Thanks once again.

Share This Page