index fragmentation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SS2K5 Replication » index fragmentation

index fragmentation

Rate Topic

Display Mode

Topic Options

Author

Message

Zeal-DBA

Posted Sunday, July 25, 2010 11:58 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:11 AM
Points: 185, Visits: 941

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???

Post #958572

Jim McLeod

Posted Monday, July 26, 2010 1:40 AM

Say Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 5:57 PM
Points: 687, Visits: 1,075

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.

Post #958620

GilaMonster

Posted Monday, July 26, 2010 2:15 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 37,725, Visits: 29,981

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 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 #958637

kevaburg

Posted Monday, May 13, 2013 5:31 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:00 AM
Points: 159, Visits: 190

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......

ObjectID IndexID PercentFragment TotalFrags PagesPerFrag NumPages
1295343679 0 76.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

Post #1452052

GilaMonster

Posted Monday, May 13, 2013 5:40 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 37,725, Visits: 29,981

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 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 #1452054

kevaburg

Posted Monday, May 13, 2013 5:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:00 AM
Points: 159, Visits: 190

Thank you very much!

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

Regards,
Kev

Post #1452057

« Prev Topic | Next Topic »

Permissions