When you purge records from a database table, deleting them, you have impact on the logical and physical structure of the target table, as well as indexes and statistics.

What maintenance should be performed on the table after a massive record deletion?


You can rebuild or defrag, depending on the nature of the data that was deleted, the number of indexes, and how badly they were impacted. If you know the fragmentation before the delete, it would be easy to assess the delta from sys.dm_db_index_physical_stats; if you don't, then you could just apply the same rules you normally would when determining whether to defrag, rebuild, or neither. (You could also take this time to look at sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats to validate that all of your indexes are necessary and are being used. Sometimes the best thing to do is drop an index.)

You could also break the deletes into chunks (see this article), and perform incremental maintenance in between, so that your maintenance at the end will be minimal. That may be more intrusive than a single operation, but it would be broken into smaller bursts over a longer time.

  • On the matter of indexes, to speed up the purge process but keep fragmentation at a manageable level, should the records to delete be identified using a clustered or non-clustered index? – Ozzie Jul 20 '15 at 15:05
  • 1
    I don't think how you find the rows to delete is going to be a significant enough factor to have any bearing on the outcome. Compared to the delete itself it is insignificant in my experience. – Aaron Bertrand Jul 20 '15 at 15:31

What maintenance should be performed on the table after a massive record deletion?

When performing a large delete like this, SQL Server doesn't actually remove the data from the pages for you right away. It marks them as logically deleted and the ghost cleanup task will then remove them as it runs in the background. That said, here are two questions you can ask yourself:

Does the table have a clustered index?

If so, rebuilding it will remove the deleted rows. If not, and you don't want one, consider building one and then removing it. Otherwise, a heap table can suffer from various issues like forwarded rows, etc.

Is this a large table?

You've mentioned it is, so the statistics that are automatically updated by SQL Server will not be sufficient. You will want to perform periodic UPDATE STATISTICS to have SQL Server sample the data (or force full scan) and update the statistics accordingly. As an alternative, consider rebuilding the table indexes as a way of also triggering an update to the associated statistics.

Next step:

Review whether you have/want a clustered index on your table and make any necessary changes. Then, rebuild your clustered index (or non-clustered if a conscious decision has been made to keep it a heap table). When rebuilding the clustered index it will cause a rebuild of the non-clustered indexes as well. This is because non-clustered indexes point back into the clustered index if one exists on the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.