August 20, 2009 at 12:51 pm
The fragmentation percentage on an index of mine seems to change each time I rebuild it.
I use sys.dm_db_Index_Physical_Stats to view the stats, rebuild the index using the default fill factor, and check the stats again. Every time I run thru the process the fragmentation changes... bouncing back and forth from 50% to 80%.
The index is on the primary key of a table with 186 rows. The DB is not being updated in any way.
First, rebuilding the index should bring the fragmentation down to less than 1%, no? And second, why would the percentage change between rebuilds?
August 20, 2009 at 2:18 pm
how many pages are there?
August 20, 2009 at 2:24 pm
Fragment_Count = 4
Avg_Fragment_Size_In_Pages = 1
Page_count = 4
August 20, 2009 at 2:24 pm
With only 180 or so rows, you've very likely got under 8 pages in that index. Very small indexes do show fragmentation after rebuilds, it has to do with how SQL allocates pages for small indexes.
It's not a concern. Fragmentation seldom has a measurable effect on indexes smaller than 1000 pages.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2009 at 2:53 pm
Thank you.
Found this supporting blurb in BOL
Rebuilding or reorganizing small indexes often does not reduce fragmentation. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding it. For more information about mixed extents, see Understanding Pages and Extents.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply