CODEWITHSUNDEEP
sqlsql-server
Holmes IV
Holmes IV

Reputation: 1739

New PK Clustered Index starts at 50% Fragmented

I am working with a table that has about 200 records. It previously had no index or constraints. I added PK/Clustered index on a unique column that is of datatype VARCHAR(20).

After creating the index, and before any Update/Insert/Delete operations, the fragmentation level is already at 50%. Any idea why? (I realize with a table this small an index is more of a formality but every bit helps.)

Upvotes: 1

Views: 243

Answers (1)

usr
usr

Reputation: 171178

SQL Server applies no smarts whatsoever to allocating pages. Probably, the table is 1-10 pages in size and these pages happened to be spread across some free space holes. SQL Server has no facilities to avoid this. It does not try to collocate related pages.

Fragmentation is nearly irrelevant for small tables since they usually end up being cached. These pages are never read again from disk.

Ignore this.

Just for fun, you could likely avoid this by rebuilding the index over to a fresh filegroup with MAXDOP = 1. The FG is empty so there are no freespace holes.

Upvotes: 3

Related Questions