For last 2 weeks I've been studying possibilities of rebuilding a heap. Our business line is:
- ETL job running every day
- number of rows inserted into heap table: more than 20 millions
- on this table only INSERT and DELETE actions are taken
- current status of table:
a) almost 4 billions of rows
b) data size = 160 GB
c) index size = > 500 GB (4 indexes)
I want to implement Ola Hallengren's solution of Index and Statistics Maintenance. Indexes are fragmented over 99% and I think nobody has implemented an optimization plan on this database.
Studying and reading all kind of articles regarding this, I really don't know how to proceed:
a) creating a clustered index on table and then drop it. After that, running Ola's job for maintenance on heap every 2-3 days
b) dropping all non-clustered indexes and creating them from scratch.
c) rebuild indexes on heap
What do you advise me to do? I know this subject has been discussed so many times here and on some other places but I can't decide which option is the best.