Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

fragmentation in database Expand / Collapse
Author
Message
Posted Sunday, March 24, 2013 8:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 1:18 AM
Points: 90, Visits: 135
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 ?
Post #1434676
Posted Sunday, March 24, 2013 9:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1434678
Posted Monday, March 25, 2013 7:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:13 AM
Points: 307, Visits: 475
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!
Post #1434907
Posted Monday, March 25, 2013 9:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:49 PM
Points: 191, Visits: 891
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.
Post #1434984
Posted Monday, March 25, 2013 9:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:49 PM
Points: 191, Visits: 891
Of course, then comes the debate of what fill factor to use on the rebuild......
Post #1434985
Posted Monday, March 25, 2013 10:00 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:36 PM
Points: 541, Visits: 1,044
Go BIG or go HOME you sql noobs!

Either you give 100% fill factor or you might as well give 0% fill factor.


Yes, it's a sarcastic morning here at work.
Post #1434998
Posted Monday, March 25, 2013 10:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1435020
Posted Wednesday, March 27, 2013 12:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 1:18 AM
Points: 90, Visits: 135
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
Post #1435776
Posted Wednesday, March 27, 2013 7:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1435880
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse