Insert query performance improvement
- Repost - originally posted 05-07-2012
- Sep 6, 2017
- 1 min read
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.

Comments