March 24, 2013 at 8:59 am
Hi apart of avg_fragmentation_in_percent in dm_db_index_physical_stats what other factor should be considered while considering table to be defragmentated eg table size , its row count ,size of data ?
March 24, 2013 at 9:32 am
Number of pages. Pointless defragging a tiny table. Avg page density in percent, wasted free space on a page may indicate a rebuild even if fragmentation is low.
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
March 25, 2013 at 7:37 am
I was confused the first time I saw a table had 75% fragmentation and nothing could be done to reduce it. Other tables I could reduce to 3-4% fragmentation. The reason behind it was that the 75% fragmentation figure was the result of being associated with a somewhat mediocre 4 pages. The other figure was based on several thousand pages.....
Just one of the reasons skipping Math was never a good idea!
March 25, 2013 at 9:35 am
Totally agree. Page count is the main one other than avg frag %.
I use > 30% and > 50 pages to rebuild. Less on either just gets a reorg.
March 25, 2013 at 9:37 am
Of course, then comes the debate of what fill factor to use on the rebuild......
March 25, 2013 at 10:00 am
Go BIG or go HOME you sql noobs! :w00t:
Either you give 100% fill factor or you might as well give 0% fill factor. 😀
Yes, it's a sarcastic morning here at work.
March 25, 2013 at 10:29 am
vikingDBA (3/25/2013)
Totally agree. Page count is the main one other than avg frag %.I use > 30% and > 50 pages to rebuild. Less on either just gets a reorg.
To be honest, I wouldn't bother rebuilding or reorganising under roughly about 1000 pages, there's no real gain the table isn't large enough to have performance problems from fragmentation or low page density at that size.
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
March 27, 2013 at 12:12 am
GilaMonster (3/24/2013)
Number of pages. Pointless defragging a tiny table. Avg page density in percent, wasted free space on a page may indicate a rebuild even if fragmentation is low.
Hi Gail thanks for your reply, now please tell what should be threshold values for both Number of pages and Avg page density percent
March 27, 2013 at 7:02 am
I mentioned the number of pages in the post right above.
As for page density, no fixed number here you have to use your judgement. If you have a fill factor of 80% set, an avg page density of 75 is probably fine. On other tables 75% may be far too low.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply