• Repost - originally posted 05-07-2012

Insert query performance improvement


I was asked the other day was there any way to improve the performance of a large number of rows being inserted. My immediate thought was to drop all indexes, do the inserts then recreate all the indexes. This was the traditional method of improving large insert performance. But since SQL Server 2005 you have had the ability to disable and re-enable indexes. Use the ALTER INDEX DISABLE and ALTER INDEX REBUILD statements to accomplish this. It is a lot more elegant, fool proof solution than the drop/recreate and there is less likelihood of an error, like an index being dropped and never being recreated. Even after disable/re-enable I still like to run my normal index maintenance and update index statistics just to make sure everything is optimised.


Recent Posts

See All

Migrating SSIS packages to AWS

We are currently migrating a large amount of SSIS packages (SQL Server 2008R2) to SQL Server 2016 at AWS. It has been a learning exercise to get these packages running. What we have learnt (so far) is