[dba-SQLServer] Rebuild as opposed to reorganize

jwcolby jwcolby at colbyconsulting.com
Mon Sep 5 12:56:44 CDT 2011


In the process of compressing the indexes on my tables, I end up with about 50% empty space in the 
database.  As these are more or less read-only databases, large, and reside on expensive SSD, I am 
doing a dbcc ShrinkDatabase and then reorganizing the indexes.

I am doing something like the following:

ALTER TABLE [dbo].[AZData] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
ALTER INDEX [IX_Clustered] ON [dbo].[AZData] REBUILD PARTITION = ALL WITH (FILLFACTOR = 100, 
DATA_COMPRESSION = PAGE)
ALTER INDEX [IX_AZUpdated] ON [dbo].[AZData] REBUILD PARTITION = ALL WITH (FILLFACTOR = 100, 
DATA_COMPRESSION = PAGE)
DBCC SHRINKDATABASE (0)
ALTER INDEX [IX_Clustered] ON [dbo].[AZData] REORGANIZE PARTITION = ALL
ALTER INDEX [IX_AZUpdated] ON [dbo].[AZData] REORGANIZE PARTITION = ALL


In my reading I see people say that a rebuild is "more efficient" than reorganize.  They also say 
however that the reorganize does not cause re-expansion of the database container whereas the 
rebuild does.  What I am not finding is whether the rebuild leaves a faster index than a reorganize.

The tables all have a clustered index as well as any other cover indexes.  When I am finished I get 
0.01% fragmentation on the indexes and very little empty space in the database.

Am I good to go here or am I missing something?

-- 
John W. Colby
www.ColbyConsulting.com


More information about the dba-SQLServer mailing list