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