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

After Index Rebuild, the fragmentation rate is still very high Expand / Collapse
Author
Message
Posted Wednesday, June 24, 2009 2:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 3:44 AM
Points: 193, Visits: 546
After having index rebuild, the indexes fragmentation is still very high, why ?
I used Maintenance Plan and SQL Agent Job to do index rebuild and used the following SQL statement to find out the indexes fragmentation :

SELECT TOP 10 a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id AND (name IS NOT NULL)
ORDER BY avg_fragmentation_in_percent DESC

Any ideal ?
Post #740829
Posted Wednesday, June 24, 2009 3:07 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 3:10 AM
Points: 615, Visits: 1,259
Check the size of the table as well. For smaller tables, rebuiding may not remove fragmentation.

-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #740832
Posted Wednesday, June 24, 2009 10:35 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:54 AM
Points: 1,153, Visits: 4,620
Joe is correct...

Additionally, if the table is HEAP (no cluster index) then ur fragmentation not remove.

The above statements is true put the cluster index and drop it then ur fragmentation removed.
(As per MS a table must have cluster index)



Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #741544
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse