Contents
Why does index affect insert performance?
The number of indexes on a table is the most dominant factor for insert performance. The more indexes a table has, the slower the execution becomes. For this reason it has to add the new entry to each and every index on that table. The number of indexes is therefore a multiplier for the cost of an insert statement.
Why the performance goes down when updating a table which has too many indexes?
First off, data inserts become slower for every update/delete/insert, because we have to maintain all indexes added for that specific table. Too many indexes also have an impact on your read performance. This is what increases the amount of memory requiered to have the full table in memory.
Can you have too many indexes?
Too many indexes create additional overhead associated with the extra amount of data pages that the Query Optimizer needs to go through. Also, too many indexes require too much space and add to the time it takes to accomplish maintenance tasks.
Why are you dropping and Recreating Your indexes?
It is common practice to drop indexes and then create them to optimize the data load process. By not having the indexes at load time SQL Server can load the data much faster, because there are no indexes to update. This is a common practice for optimizing loading large data warehouse table loads.
What happens if you drop a clustered index?
If you drop the clustered index again, all the clustering key links are now invalid so all the nonclustered indexes must be rebuilt automatically to pick up the new heap physical location links. In other words, if you create and then drop a clustered index, all the nonclustered indexes are rebuilt twice. Nasty.
Why are indexes so bad in SQL Server?
3. Implement indexes that provide a benefit to your queries. This involved creating missing indexes and removing poor performing or unutilized indexes. Having a large number of indexes can be detrimental to DML transactions.
Why did they drop the indexes on the archive table?
So Somebody decided it would be a good idea at the beginning of the SP to drop all the indexes on the archive table and recreate them at the end. Of course, recreating them also takes eons, but arguably not as long as inserting the new records with the indexes in place.