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.
> So no need to worry about fermentation of clustered index of small tables which have fewer then eight pages.
ReplyDeleteWhat is the criteria for determining 8 pages?
Why not 16 or some other relatively small value?
Should there be concern for high fragmentation on an index with say 9 pages?
Upto first 8 pages SQL Server can allocate from mixed extent (every extent consists of 8 pages), Once it need 8 or more then 8 pages, It becomes qualified for uniform dedicated extent.
ReplyDeleteThere will not be 9 pages, as whole uniform extent always contain 8 pages.
sys.dm_db_index_physical_stats have a column page_count, which give us total number of pages an index is consists of.