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