DBCC Showcontig - index fragmentation
-
Hi. I ran a dbcc showcontig. But I'm confused as to what numbers on the output of the dbcc shows there is fragmentation. I've read that if the Scan Density is 100 then everything is contigous or if it is a little less that 100 that shows some fragmentation. But then I also read that you want the Avg. Page Density to be a high percentage and Avg. Bytes free per page to to be a low number. some of my output shows 100 in scan density but then not the 'correct' percentages for the other fields.. so is there a sure way to determine if you have fragmentation..
Thanks,
Juanita
-
Your reading
you want the Avg. Page Density to be a high percentage and Avg. Bytes free per page to be a low number.
is true in relative context, not in absolute terms.
If you have OLTP DB with significant # of writes/updates than you set index fill factor somewhere in range of 70 to 90 % to keep some free space on all pages all the time to avoid frequent page split, which hurt the performance. Where as in OLAP DB or db where you expect more reads, then 90 % plus fill factor will provide you more info. in less # of pages and is advisable.
Scan density 100 or close in general says, low or no fragmentation. Scan density of 100 will not stay for long in live db after short time from reindex or indexdefrag.
I think it will be good to look at this short and nice article.
http://www.sqlservercentral.com/columnists/chedgate/fragmentation101.asp
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com -
Good Morning,
Thank you so much for the information! I will also definitely read the article. It looks great!
Juanita Drobish
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply