Mahmoud ElMansy: knowledge meant to be free

Disable and Enable Indexes to perform large data update in SQL Server 2008

Indexes are very important they used for making Queries run faster and to reduce execution time .
But they are Headache in very large table especially when updating large number of data .
The query can run for ever.
So it is recommended To disable and run your update and then re enable the indexes .
The question now why i do that ?!

The answer is simple to reduce the time of patch update .
To get rid of indexes scan that the execution plane do (if the table has more than index this time can be very very large ).
her is the SQL statement to enable and disable indexes

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

Please note that in order to make that you should end all connection to the database .