index fragmentation

Question

index fragmentation

Zeal-DBA

SSCarpal Tunnel

Points: 4332
More actions
July 25, 2010 at 11:58 pm

#387424

hello experts,
i have rebuilt all the indexes of my database but after that still some indexes have the same fragmentation like 80 to 99. wanna know why is this so???

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 1

The usual reason for this is the size of the index is so small that index fragmentation figures make little sense. I can't remember if it's 100 pages (800 KB), or 1000 pages (8MB), or 1000 rows (any size!), but I wouldn't worry about fragmentation for any indexes less than 1000 pages.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Under 24 pages, a rebuild will have little to no effect, due to the way SQL allocates pages for small indexes. The 1000 page threshold is usually specified as an aprox size for indexes where you should care about fragmentation for performance reasons.

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

kevaburg SSCoach Points: 18131 More actions · Answer 3

To take this thread a little further, I have a problem reorganising the indexes on a particular table. This is the query I ran.....

SELECT object_id AS ObjectID,

index_id AS IndexID,

avg_fragmentation_in_percent AS PercentFragment,

fragment_count AS TotalFrags,

avg_fragment_size_in_pages AS PagesPerFrag,

page_count AS NumPages

FROM sys.dm_db_index_physical_stats(

NULL, NULL, NULL, NULL , 'DETAILED')

WHERE avg_fragmentation_in_percent > 30

and page_count > 1000

ORDER BY 3 desc;

.....and this is the output......

ObjectIDIndexIDPercentFragment TotalFragsPagesPerFrag NumPages

1295343679076.4705882352941 719 10.3588317107093 7448

The number of pages is 7448 so a percentage fragmentation of 76.47 has meaning. Regardless of how often I reorg the index, the %fragmentation does not reduce and I am at a bit of a loss to understand why.

I would be grateful for any input you may have!

Thanks in advance.

Regards,

Kev

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

It's a heap (index 0). You can't reorganise a heap and fragmentation has a very different meaning for a heap than for an index.

Please post new questions in a new thread in future. Thanks

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

kevaburg SSCoach Points: 18131 More actions · Answer 5

Thank you very much!

And yes....next time I will....

Regards,

Kev