StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO25k Inserts Daily, 99% Fragmentation on Clustered GUID Index
text
Body
copied!<p>I have a table that has a primary key as a clustered GUID field; I'm generating GUIDs using <code>NEWSEQUENTIALID()</code> instead of <code>NEWID</code>. Unfortunately, because this table sees ~25k-100k inserts per day, within a few hours the (default: clustered) primary key index becomes 99% fragmented.</p> <p>I originally used <code>NEWID</code> instead of generating sequential IDs, but even when I recreated the table and reinserted all the rows using <code>NEWSEQUENTIALID</code> (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.</p> <p>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.</p> <p>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).</p> <p>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.</p> <p>Also: I cannot use an indexed view, because I have an inner-select which would be difficult for me to unwind into a join.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload