I have a table that has a primary key as a clustered GUID field; I'm generating GUIDs using NEWSEQUENTIALID() instead of NEWID. Unfortunately, because this table sees ~25k-100k inserts per day, within a few hours the (default: clustered) primary key index becomes 99% fragmented.
I originally used NEWID instead of generating sequential IDs, but even when I recreated the table and reinserted all the rows using NEWSEQUENTIALID (and specified that as the default value for the primary key column), I still see fragmentation in the order of 99% within a few hours. (The table currently has about 1.3 million records in it.
I had thought about replacing the GUID with an integer primary key, but I'm not sure if that will work; plus, since our team uses GUIDs for primary keys instead of integers going forward, I don't think I'll have enough buy-in to do this.
What are my options to keep this thing defragmented? I'm using SQL Server Express, so I don't have access to SQL Agent (and cannot, therefore, run a maintenance plan regularly to rebuild the index).
I also may very likely be splitting this database/table at some point in the future (because of the volume of data), so I will likely need GUIDs to merge the tables.
Also: I cannot use an indexed view, because I have an inner-select which would be difficult for me to unwind into a join.
In my own personal experience, tossing out GUIDs as your clustering key can have major, positive effects on your system - especially on index fragmentation!
My new INT IDENTITY clustering indices have hardly any fragmentation - even after months of intense, daily production use. Definitely worth it!!
Using the Guid datatype as clustering key in SQL Server is a horribly bad choice - whichever way you look at it...
See some of Kimberly Tripp's (Queen of Indexing) blog post on the topic:
and anything else she's blogged on the topic of clustering keys....