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
Director – eDominer Systems, Peopleware India, SQLMaestros

Follow me on Twitter | Follow me on FaceBook | Connect on LinkedIn | Google+
FB page | Know more about me

SQL Server fn_dblog - An interesting observation
SQL Server Cardinality Estimation - Quick Notes, Part 1