My New Friend: Disable Index

Adding to Pragati Sharma comment regarding disabling indexes prior to large DML operations, DO NOT disable UNIQUE key indexes when doing large DML operations. Indexes that maintain the logical integrity of your database should never be disabled. On the topic of index consolidation, consider using INCLUDEd columns, to cover your queries. I.e. fewer but wider. Finally, make your Clustered Index as narrow as possible, since it appears in every row of your nonclustered indexes

Good find Anoop Agarwal will help you in long way.I always use as practice to disable index before a big DML operation and reenable after DML done. Have helped many times for better performance.

I agree

I recently encountered a similar situation. I was tasked with improving the performance of a stored procedure (SP) that runs during non-production hours. The SP was performing a bulk update on a flag column in a large table and was consuming a significant amount of memory and CPU resources. Upon investigation, I discovered that a trigger on the table was executing to log the changes, which was contributing to the performance issues. To address this, I disabled the trigger before the update statement and re-enabled it afterward. This adjustment resulted in a significant improvement in performance.

why not leave the index as is, why do you need to disable it and re-enable it every single day (via a rebuild)?