[dba-SQLServer] Rebuild as opposed to reorganize

jwcolby jwcolby at colbyconsulting.com
Mon Sep 5 13:28:08 CDT 2011


Thanks Arthur.

John W. Colby
www.ColbyConsulting.com

On 9/5/2011 2:12 PM, Arthur Fuller wrote:
> Good to go.
> A.
>
> On Mon, Sep 5, 2011 at 1:56 PM, jwcolby<jwcolby at colbyconsulting.com>  wrote:
>
>> 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
>>
>>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>



More information about the dba-SQLServer mailing list