Hi Friends,
I see a common practice with DBAs: If you have to rebuild a HEAP, you will create a clustered index and drop it – this two phase process does the rebuild job, but is expensive. SQL Server supports ALTER TABLE REBUILD command to rebuild a heap.
Creating a dropping a clustered index, the old technique, is expensive since it has to update all the non-clustered indexes with the correct pointer, twice! When you create a clustered index, it updates all the non-clustered indexes with a pointer to the clustering key. When you drop the clustered index, it updates all non-clustered indexes with a pointer to the RID (HEAP). So in this technique, non-clustered indexes are rebuilt twice.
Instead, use SQL Server ALTER TABLE REBUILD command and the non-clustered indexes are rebuilt only once.
Here is a quick example to show the performance difference:
I create a table and a few non-clustered indexes:
use AdventureWorks2012
go
--drop table person.persontemp
select * into Person.PersonTemp
from Person.Person
CREATE NONCLUSTERED INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[PersonTemp]
(
[LastName] ASC,
[FirstName] ASC,
[MiddleName] ASC
)
CREATE NONCLUSTERED INDEX [IX_EP] ON [Person].[PersonTemp]
(
[EmailPromotion] ASC
)
CREATE NONCLUSTERED INDEX [IX_PT] ON [Person].[PersonTemp]
(
[PersonType] ASC
)
Now, I will turn ON STATISTICS TIME and record the execution times for CREATE/DROP Clustered Index technique & for the ALTER TABLE REBUILD command.
set statistics time on
CREATE CLUSTERED INDEX [IX_BEID] ON [Person].[PersonTemp]
(
[BusinessEntityID] ASC
)
drop index [IX_BEID] on [Person].[PersonTemp]
Now, let’s see the execution times for ALTER TABLE REBUILD command:
set statistics time on
ALTER TABLE [Person].[PersonTemp] REBUILD
The difference in execution times is very evident. Especially, note the CPU time in both cases.
ALTER TABLE REBUILD was introduced in SQL Server 2008 (if I am not wrong) and still not known to many, so consider using it in case you have to rebuild a heap, rather than using the old technique.
Shall you have a HEAP? Why not a clustered index? Why do you need a HEAP? Etc, etc, etc – This is completely another story!
But why do you want to rebuild a HEAP?
Realy Nice post.
You want to rebuild a heap when you just removed 1/3 of the rows in a table that is consuming 180 GB if space and you need to recover the space from it 🙂
Now the bigger question, is which developer thought of creating schemas where you can’t build a clustered index without incurring a massive IO increase…. and thus a heap is just as good
New columns may be added on a clustered table at a later date. If we use ALTER TABLE REBUILD command on a clustered table (after adding new column or dropping an existing column), will it give any performance benefit?
Super!!!!
ALTER TABLE REBUILD
How do you do that to point to a new filegropup?
is it possible to use alter rebuild to change filegroup?