Indexes gets fragmented soon after rebuilding index
You have a table with index created on it. Logical Fragmentation for the index comes to 99 %. You did re-indexing of the index with Rebuild option and after that logical fragmentation goes
down to 1-2% but within few minutes, fragmentation rises to high value.
Cause of the issue in our scenario
Index key is a GUID data type
Things to check
Please make sure that index key is not a GUID. GUIDs if included in the index key causes index fragmentation in both clustered and non-clustered indexes.
A GUID key causes fragmentation because of its random generation of values. The point where insertion of a new record (in an index) occurs is determined by the value of the index key, so if the key
value is random, so is the point of insertion.
In my case, Clustered index key was on uniqueidentifier datatype(GUID).
The uniqueidentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value
we can use NEWSEQUENTIALID to reduce fragmentation.
References - http://msdn.microsoft.com/en-us/library/ms190215(v=SQL.100).aspx
Written By :- Anmol Bhasin
Support Engineer, Microsoft SQL server Support
Reviewed By: – Akbar Farishta
Technical Lead, Microsoft SQL server Support