February 2, 2013 at 1:17 am
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?
February 2, 2013 at 1:39 am
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
February 3, 2013 at 5:45 am
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" 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply