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:

Now, I will turn ON STATISTICS TIME and record the execution times for CREATE/DROP Clustered Index technique & for the ALTER TABLE REBUILD command.

SQL Server ALTER TABLE REBUILD_1

Now, let’s see the execution times for ALTER TABLE REBUILD command:

SQL Server ALTER TABLE REBUILD_2

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?

 

 

Amit Bansal (SQL MCM, SQL MVP) | Twitter | FaceBook | LinkedIn | FB Page|Ask a SQL Question                                                          Browse more blogs on SQLServerGeeks.com