June 18, 2021 at 12:57 pm
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
June 18, 2021 at 7:23 pm
change the ID to be sequential - that will alleviate that issue.
June 18, 2021 at 7:23 pm
The Id comes from some C# - code I'm afraid.
June 18, 2021 at 7:29 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 18, 2021 at 7:33 pm
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..
June 18, 2021 at 7:58 pm
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
June 18, 2021 at 8:08 pm
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)?
June 18, 2021 at 8:17 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 30, 2021 at 2:15 pm
>> 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.
June 30, 2021 at 2:43 pm
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.
June 30, 2021 at 2:57 pm
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply