SQL Server: Small Tables’ Clustered Indexes Fragmentation
Recently, I have received a mail from my friend who was angry that, the defragmentation script that I have mentioned in my post Simple Method to Resolve All Indexes Fragmentation is not working properly, even he executed said script multiple times. sys.dm_db_index_physical_stats is still showing few tables with high fragmentation.
On further inquiry I found that, said tables are from setup schema and have small number of rows. And I just replied him that I am HAPPY that script is not working for these tables.
Actually, when we create a table and start inserting rows, SQL Server initially allocates pages from mixed extents until it has enough data to deserve a full extent, then SQL Server will allocate a uniform extent to it. Similarly if you build an index on a table that have fewer then eight pages SQL Server will allocate pages from mixed extents for storing the index data. And if these mixed extents are not located side by side then database management view sys.dm_db_index_physical_stats will show HIGH external fragmentation. So no need to worry about fermentation of clustered index of small tables which have fewer then eight pages.