UniqueIdentifier as Primary Key, non-clustered index, fragmentation issue

  • I've been working a little bit with a few tables here, they've got "CreatedDate" as clustered Index and and Id (UniqueIdentifier) as Primary Key. The Id's are generated from code and are non-sequential.

    The problem(?) is that the Id-PK index gets heavily fragmented almost instantly even after a defrag-session.

    I can't say I've identified any performance bottlenecks related to that but I feel uncomfortable with an index thats like 98-99% fragmented. 🙂 Anyone got any hints here how I should proceed? Should I bother? Should we consider some kind of redesign of the table?

    Thanks in advance

    • This topic was modified 3 months ago by  oRBIT.
  • change the ID to be sequential - that will alleviate that issue.

  • The Id comes from some C# - code I'm afraid.

  • Actually they got the structure right for this table: the clustered index is what's critical, and it's not fragmented (presumably).

    To help correct the PK but NONCLUSTERED index -- which again is the correct set up for this table -- lower the fillfactor when you rebuild the index.

    For example:

    ALTER INDEX PK_ON_GUID_INDEX ON dbo.table_name REBUILD WITH ( FILLFACTOR = 80 /*or 70, whichever works out better over time*/, ONLINE = ON /*if your system allows this, if not set to OFF*/, SORT_IN_TEMPDB = ON )

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • That's interesting hints. Setting the fillfactor here should not prevent, but slow down the current rapid fragmentation I guess?

    I'm pretty sure fill factor is configured to "0" for that table at the moment..

  • oRBIT wrote:

    The Id comes from some C# - code I'm afraid.

    I assumed your company owned the code - should be easy to change it - and if not you can ask the vendor what they think about changing it.

     

    changing the fill factor will only delay the fragmentation.

    but as said this may not even be a big problem as the cluster index is setup correctly

  • I'm trying to learn more about indexes but it's so much stuff to learn. 🙂 But very interesting at the same time.

    The reason for my uniqueidentifier non-clustered index is getting fragmented, is it because the guid's needs to be stored in order in the index and since my guid's comes in no order they have to be inserted all the time (and removed when deleted)?

     

  • Yes, delay, which will hopefully prevent.  For example, rebuild that index every 3 days, or however many days between when it gets too fragmented.  If it still fragments too quickly, lower the fillfactor some more.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • >> I've been working a little bit with a few tables here, they've got "CreatedDate" as clustered Index and and Id (UniqueIdentifier) as Primary Key. The Id's are generated from code and are non-sequential.<<

    Why do you think that your unique identifier can ever be a key of any kind? By definition, a key is a subset of columns (attributes) in a table, which is always unique. Ever read anything about RDBMS by Dr. Codd or anyone else? GUIDs are global, like the letter "G" says, and not part of the logical data model in the table.

    Serious RDBMS people refer to programmers like you as "ID-iots" because they try to use a non-attribute as a key in a table. This derogatory term also applies to people who use IDENTITY or AUTOINCREMENT vendor extensions for keys. Your logical fallacy is that while a key is unique, being unique does not make something it a key.

    >>Should we consider some kind of redesign of the table? <<

    Since this thing does not have a proper key, is not yet a table by definition, please consider starting over on the whole schema.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • I've not designed this thing. I am nowhere near an SQL-expert either, but I do think this world is interesting and am trying to learn, that's why I'm in this forum actually, to hopefully get helpful and *friendly* advice.

     

  • oRBIT wrote:

    I've not designed this thing. I am nowhere near an SQL-expert either, but I do think this world is interesting and am trying to learn, that's why I'm in this forum actually, to hopefully get helpful and *friendly* advice.

     

    Just ignore Celko, he's singularly uninterested in providing helpful advice 🙂

     

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply