Current Scenario :
- All the tables have only non-clustered indexes ( HEAPS ) .
- All these non-clustered indexes are heavily fragmented > 98 %. i.e We have to deal with fragmentation on HEAPS
- Couple of non-clustered indexes has a forwarding record count which is not good.
- These Heap tables , over time become highly fragmented and degrade query performance.
Maintenance activity planned for today will :
We will defragment a HEAP :
- Rebuild the non-clustered index on tables which have forwarding record count
- Rebuild non clustered indexes on rest of tables which will help reduce the fragmentation
This should help in normal scenarios but issue we have is fragmentation on HEAPS , the only other option available is to create a clustered index on the all tables to remove fragmentation.
However by far we should always to avoid heaps altogether in your database design.
Read Good Blog on heap fragmentation : http://www.patrickkeisler.com/2013/03/dealing-with-fragmented-heap.html