7cdc20e3   
   From: esquel@sommarskog.se   
      
   seanD (sean.denney@gmail.com) writes:   
   > What is the best way to defragment a very large table if both minimal   
   > user impact and speed are concerns?   I have a small maintenance   
   > window And a 24X7 application on top of this database.  It seems that   
   > doing anything online would not make sense, but doing a rebuild   
   > offline will have large impact for the   
   > Application.  Is indexing a temptable and copying into it and renaming   
   > the best way to go?   
   >   
   > Any help you can give would really be appreciated.   
      
   What version and edition of SQL Server do you have?   
      
   If you have SQL 2005/2008 Enterprise, you can use the WITH ONLINE option   
   with ALTER INDEX REBUILD to make an online rebuild. The rebuild will still   
   not be 100% online, there will be small windows when the table is locked.   
   But most of the time table will be accessible.   
      
   The alternative is to use ALTER INDEX REORGANIZE which is always online.   
   Sometimes this is better, but it can also take a larger toll on the   
   application.   
      
   The alternative you suggest of copying data into a new table sounds like   
   the poor man's online defragmentation, which you would need to use if you   
   are on SQL 2000, or have Standard Edition. (But if you have a 24/7   
   requirement, I would suggest that this is a strong call to use Enterprise.)   
      
   > index 1 (Clustered) is on varchar(15)/varchar(36)  - 88% fragmentation   
   > index 2 nonunique on uniqueidentifier is 4%   
   > index 3 is primary key index on int col  is also 88% fragmented   
      
   It's a little intriguing that an index on a IDENTITY column would be   
   that badly fragmented. On the other hand, I would expect a guid index   
   to have realy poor numbers. From where did you get these numbers? You   
   did not by chance confuse then with scan density?   
      
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)