Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Fragmentation does not reduce for some tables Expand / Collapse
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: Friday, December 4, 2015 6:56 AM
Points: 274, Visits: 1,778
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



Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 44,011, Visits: 41,417
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, MVP, M.Sc (Comp Sci)
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



Group: General Forum Members
Last Login: Yesterday @ 4:10 PM
Points: 7,985, Visits: 16,022
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