Wednesday, January 1, 2014

How to fix the fragmentation on heaps?


We know that heaps are the tables without clustered index on. It may have many non clustered index  (NCI) but still it is considered a heap. So consider a huge table (heap) that is fragmented. How would  you fix it? Technically, you cannot defrag a heap table.  But remember we have ALTER table REBUILD command which works very well on the heaps. But there is one big But. If you have many NCI on the table this command will rebuild all of them at once. What does it mean? It can produce transaction-log bloat which hits the over all performance. Think about the process the scan the log or perhaps Log Shipping job that needs to move the log file to the remote server. All this affects performance.

So, to answer the question ,we need to create a clustered index on that table..That is simple answer to the question.

Happy New Year to all!