Luis Cazares (8/6/2015)If identity is ever increasing, how can it create the same fragmentation of a random value?[/Quote]
fragmentation on an identity value can surely occur if you have a high concurrent System. I've linked the Pictures from my blog post about it.
The above picture shows what happens in a clustered index with an contigious increasing clustered key. You generate a hotspot at the very end of the leaf levels.
This picture shows the Transaction log for the insert process in the numeric_table. Please have a look to the SLOT ID! You can see that the Slot Id permanently changes. Why could this happen in an ever increasing key?
The [RowLog Contents 0] column shows the values which are inserted into the table. I don't want to go to deep into record structures BUT...
the ID-Value is from Byte 5 to 9!
SELECT CAST(0x14 AS INT) AS [Slot_0],
CAST(0x21 AS INT) AS [Slot 1],
CAST(0x22 AS INT) AS [Slot 2],
CAST(0x23 AS INT) AS [Slot 3],
CAST(0x24 AS INT) AS [Slot 4],
CAST(0x20 AS INT) AS [Slot w],
CAST(0x1F AS INT) AS [Slot x],
CAST(0x15 AS INT) AS [Slot y],
CAST(0x1D AS INT) AS [Slot z];
The above SQL Statement will decrypt the ID's of the inserted values in the order of their INSERTS!
This will be the result:
The picture demonstrates that IDs have NOT been inserted contigious but "random". The reason is quite simple - the processes have been blocked by other processes which have inserted the records!
The above picture Shows what happens inside:
Every single transaction gets it's ID from the IDENTITY-Pool first. This process is not revertable!
When the process has recievd it's ID (System process) it goes on to insert the record.
But there are 199 other processes which want to do the same!
Where do they all want to do that? - At the very end of the index - LATCH CONTENTION!
So that is the explanation WHY a clustered index with contigious index keys can become highly fragmented 🙂
All the best to all of you. I'm off now for at least 3 weeks. I'm on my family holiday in Singapore and the Philippines (diving).
I really hope to see many of you in Seattle to the PASS Summit or the SQL in the City-Event!
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)