Recently, I had to remove a large number of rows, almost 10 million, from a very active database table in a LIVE system. Forget why for now as that is the subject of another post. The basic problem was how to remove 99.8% of the rows without impacting users or removing the few rows that we actually wanted to keep. To make matters worse, the field that was determined to have all the answers didn’t have an index.

Adding an index to the field in question would only solve part of the problem. It would take resources just to apply the key to a table that large, especially one that already had several indices. There are also locking issues and let us not forget the Transaction Log usage. We are talking Gigabytes of space for any new index and for temporary Transaction Log space.

Continue reading →