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.
No comments:
Post a Comment