Bulk disable indexes

If you’re performing a large data migration task, or running a script that will insert many records, you may be able to speed up the process by disabling indexes on you database.
This will help, because the database won’t need to incrementally update the indexes for each of your inserts.
Once you enable the indexes after the load process has completed, the indexes will have an opportunity to update themselves, and the world is good.

So, run the below script. It will output a list of alter statements for each index. Copy and paste the result into a new query window and execute.

select 'ALTER INDEX ' + I.name + ' ON ' + T.name + ' DISABLE'
from sys.indexes I
inner join sys.tables T on I.object_id = T.object_id
where I.type_desc = 'NONCLUSTERED'
and I.name is not null

Note, the script will only disable non clustered indexes. That’s because disabling clustered indexes will mess up your tables!

For a script to enable/rebuild these indexes, check out an earlier post of mine here.

Subscribe

0 comments