Hi,

Currently we are facing some performance issue while accessing the archive data from the archive tables. the archive table is hugh and it contains around 100,000,000 records and this archive table is being used in few reports and in our commission cycles too. since we are facing performance issues we are rebuilding index once in a week on all the indexes on this archive table. we have 1 clustered index and 5 non clustered indexes, every time when we rebuild all these indexes on this table it is taking more time, more often rebuilding the clustered index itself is taking approx. 1hr which is consuming more time. wanted to know is there any useful to rebuild clustered indexes or not, if yes then what would be the better way. if not then do we need to rebuild only non clustered indexes.

Any suggestions would be highly appreciated and would help us to dig into this issue in right direction'

Thanks,

Rajesh Kumar

Are you on Enterprise edition?

If yes you could partition the table so that you could rebuild specific partitions based on data volatility

You can also consider making Non clustered indexes align to partition so that you can do partition switching effectively.

Simply rebuilding your indexes may not increase your performance unless it is really fragmented.

I recommend check the fragmentation level before rebuilding indexes. it may save some time as well.

one more thing index rebuild is completely a logged operation. consider changing the recovery model before you do these operations. this could save time time and resources.

From my experience I can say that in more than 70-80 percent you need only update statistics and not rebuild indexes

But, before you need to check for fragmentation, what the below for example return for your table?

SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats
( 
  DB_ID('yourdb'),
  OBJECT_ID('dbo.yourtable'),
  1,
  NULL,
  NULL
);

Thanks for the reply. can you please tell will there be any helpful if we rebuild the clustered index after checking the fragmentation level or we can ignore rebuilding the clustered index and rebuild only non clustered indexes.

If your CI is fragmented then you need rebuild it... I would suggest  you looking at the script written by Ola to maintain indexes 

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html