Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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 Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 8, 2016 6:35 PM
Points: 207, Visits: 612
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


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 982, Visits: 1,879
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
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: Friday, November 25, 2016 6:54 AM
Points: 1,176, Visits: 4,805
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