SQL Server - Disable Index and Rebuild Index for Updates on very large tables

This Performance Analysis Case Study  looks at UPDATE statements on very large tables and how to use Disable Index and Rebuild Index for sql performance  gains

 A developer was  executing an UPDATE statement on an ETL staging table. The table is ~ 550 million rows. The estimated amounts of rows to update was  ~ 11 million .

 The purpose of the statement is to clean data , prior to UPDATE on main production table.

 A  look at the Execution Plan using  SET SHOWPLAN_XML  ON , revealed a Table Scan on the staging table.

 The DDL of the Staging table indicated no Index Seek on  table

I created a Clustered Index , which still forced a Scan . Looking at the data and statement – a Scan was required, unless I  added a WHERE clause .

There were 8 Non-Clustered Indexes on the target table.  I disabled them and this solved the problem.  The UPDATE went from  63 minutes down to 11 minutes

I executed Index Rebuilds  after the  UPDATE .

Notes:

1)      If the table has too many indices, it is better to disable them during update and enable it again after update

2)      Try and use the WHERE clause as this limits the data
and enable it again after update

3)       Batch Updates

4) Ensure all login are disconnected

4)      Turn off services connecting to database

----Disable Index ALTER INDEX [NC_myindex] ON [dbo].[VeryLargeTable] DISABLEGO----Enable Index - ALTER INDEX [NC_myIndex] ON [dbo].[VeryLargeTable] REBUILD  WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )GO

Notes:

1)      If the table has too many indices, it is better to disable them during update and enable it again after update

2)      Try and use the WHERE clause as this limits the data
and enable it again after update

3)       Batch Updates

4)     Ensure all login are disconnected

5)     Turn off services connecting to database

Read More

SQL Server Disable Indexes and Rebuild Indexes dynamically ...

SQL SERVER REBUILD INDEX - SQL Server DBA

SQL Server Performance Checklist