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 ««12

Index Rebuilds Still Leave Fragmentation Expand / Collapse
Author
Message
Posted Wednesday, November 7, 2012 1:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 30, 2014 12:12 PM
Points: 1, Visits: 130
Check the MDOP (Max Degree of Parallelism) setting. In the past, I have seen improvement when MDOP is 1 instead of default 0. My understanding was, when parallel threads try to rebuild an index, there would still be fragmentation simply because more people at work, however if there is only 1 thread doing the work, then we would get the optimum results out of ALTER INDEX...REBUILD

ALTER INDEX <index-name> ON <table-name> REBUILD WITH (MAXDOP = 1)

The details are explained in detail in the blog post:
http://www.sqlservergeeks.com/blogs/Parikshit/personal/427/does-parallel-plan-for-rebuild-index-impact-the-fragmentation-of-index
Post #1382161
Posted Wednesday, November 7, 2012 11:46 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 42,802, Visits: 35,917
Please note: 4 year old thread.


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 #1382275
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse