Another reason of Index logical fragmentation!

Problem Description:

Indexes gets fragmented soon after rebuilding index

Detailed description

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

Workaround

we can use NEWSEQUENTIALID to reduce fragmentation.

References - https://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