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

index fragmentation Expand / Collapse
Author
Message
Posted Sunday, July 25, 2010 11:58 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 10:09 PM
Points: 208, Visits: 1,040
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
Posted Monday, July 26, 2010 1:40 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Sunday, July 6, 2014 8:03 PM
Points: 683, Visits: 1,110
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
Posted Monday, July 26, 2010 2:15 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: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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
Posted Monday, May 13, 2013 5:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:59 PM
Points: 333, Visits: 555
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
Posted Monday, May 13, 2013 5:40 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: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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
Posted Monday, May 13, 2013 5:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:59 PM
Points: 333, Visits: 555
Thank you very much!

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

Regards,
Kev
Post #1452057
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse