Large table with inactive records. Better performance to move inactive records?

Question

We have a database which most tables utilise an inactive flag. Everytime a record is changed the active flag is set to 0 and a new record is created. This enables us to keep an auditlog of all records.

Take 1 table for example we have about 8 million records in it but only about 700,000 records of these are active.

We have been discussions recently about implementing the removal of these inactive records into audit tables.

My question really is will we expect better performance from our queries given that our tables are appropriately indexed and always include the active flag.

Answer 1

If all the queries includes the active flag and your indexes support this filter, removing old records should not impact queries performance.

I've some DBs that work the same and removing some milion rows helped performance just because our ad-hoc queries dodn't use the ActiveFlag.

However, the cleaning effected our maintenance and backup time.

Answer 2

There are indexing strategies that can make the current design perform pretty well, like partitioning and filtered indexes. So you shouldn't make the change expecting a significant performance improvement without realistic testing.

Good news is that 8 million rows is not much, so this should be quick and easy to test.