• Adam Thurgar

Index your updates

We are currently working on a project to do a major data update on a manufacturing database to update client and product codes in preparation for moving to a new global system. This will require updates across 2 databases, 23 tables and hundreds of millions of rows. These updates are not difficult, but because of the size can be quite slow. This is when you must remember to check that the columns being updated are indexed and any table join columns are also indexed. This is commonly overlooked and can lead to updates (and merge) statements taking a lot longer than required and also taking out exclusive table locks - causing blocking. As an example, a 43 million row update took over 10 minutes without an index, after the index was added it took just over 2 minutes. These indexes do not need to be permanent and if not required could be dropped after the updates have been completed. So check your indexes before you start doing any major data updates.

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