Fragmentation on Table

Toad for SQL Server
1

Hi All,

I have a puzzle on my hand. Please check following:

select *
from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(‘dbo.Survey’),1,0,‘DETAILED’);

index_depth = 4
index_level = 0
avg_fragmentation_in_percent = 98.2717326087911
fragment_count = 203005
avg_fragment_size_in_pages = 1.00984212211522
page_count = 205,003

Now I did following:
alter table dbo.Survey REBUILD WITH ( FILLFACTOR=10);

Above stats again looks like this:

index_depth = 4
index_level = 0
avg_fragmentation_in_percent = 0.01
fragment_count = 390
avg_fragment_size_in_pages = 2218.08461538462
page_count = 865,053

Everything seems ok, except “page_count”. Why it should increase so much, in fact I think it shouldn’t increase at all.

2

Thinking when the index rebuild occured on the table (its primary key) you specified a fill factor of 10%, this equates to 90% of the page being left empty for each page. This action would increase the number of pages to accomodate the existing data. Was this intentional, or, did you really mean for a fill factor of 90%, which would leave 10% empty space on each page?