Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

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

26 July,2011 by Jack Vamvas

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

Performance 1 execution plan 

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer