This Performance Analysis Case Study looks at UPDATE statements on very large tables and how to use Disable Index and Rebuild Index for 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.
5. Turn off services connecting to database.

----Disable Index 
ALTER INDEX [NC_myindex] ON [dbo].[VeryLargeTable] DISABLE
GO
----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.

Republished from http://www.sqlserver-dba.com/.