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 does not reduce for some tables Expand / Collapse
Author
Message
Posted Saturday, February 2, 2013 1:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 5:11 AM
Points: 272, Visits: 1,756
I am defragmenting the database as a maintenance work. But i found that some of the databases avg_fragmentation_in_percent does not reduce.But in some article i have seen that fragmentation on small indexes is often not controllable and The pages of small indexes are stored on mixed extents.
So what is the limit for page count for which i can neglect the value of avg_fragmentation_in_percent?

In some databases having page count 100 has shown reduce in fragmentation but some db having page count 68 does not reduce fragmentation. So how can i decide for which condition i should consider checking fragmentation?
Post #1414918
Posted Saturday, February 2, 2013 1:39 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: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
68 pages is too small to worry about. The usual threshold that's mentioned is 1000 pages. Not a hard and fast number, just a guide


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 #1414921
Posted Sunday, February 3, 2013 5:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 6,760, Visits: 14,412
Aside what Gail has said check the index id for the object. If its a heap then it will naturally contain some fragmentation.

The page allocation marker is 8 pages, any allocations over that will use uniform extents. This is detailed on msdn, sorry but I don't have link at present.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1415010
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse