October 29, 2026 at 7:20 am
Hello everyone,
I am planning to implement a data purging solution that is expected to reduce the size of one of our main databases by nearly 50%.
One of my colleagues believes that the performance benefits will be limited. His argument is that most queries access data through Index Seeks, so reducing the amount of data would not have a significant impact on query performance. According to him, the main benefits would be seen only for Index Scans and operations that process large portions of the data.
What is your opinion on this assessment?
Beyond the storage savings, should we expect noticeable improvements in areas such as query performance, index maintenance, statistics updates, backup/restore times, DBCC checks, or overall resource consumption after a purge of this magnitude?
I would appreciate any feedback or real-world experiences.
Thank you.
October 29, 2026 at 4:03 pm
It's true that single seeks will see no difference from a reduction in data.
However, a seek and scan will still benefit.
Yes, overall you should see improvement. If you have a lot of lengthy char data, you would see even more improvement from page compression (pc). I suggest using a fillfactor of 90 to help accommodate the way pc works. But it can save big on i/o and disk usage.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 29, 2026 at 4:21 pm
The execution plan is your friend here. Whatever happens make sure to update your stats when you're done. Cutting data by 1/2 will make your life miserable if your histograms go stale, the optimizer will produce bad cardinality choices.
The only way to know for sure if reducing the DB size by 50% will speed things up is to actually do it and see how it goes. Do you have a Dev or Sandbox environment you can use for testing? If so you can start with a couple of your largest table(s) and compare queries that run against those tables.
"His argument is that most queries access data through Index Seeks, so reducing the amount of data would not have a significant impact on query performance." If most queries access data via index seeks then there is a Some truth to this statement. Scanning is not the only expensive operation and queries that seek 80% of the rows aren't performing much better. Assuming we're talking about fast efficient scans then we still have data to filter, sort and aggregate. Sorting is an N LOG N operation, which means the cost per row increases as you add new rows. Reducing the cost of a scan, key or rid lookup and other expensive operations by 50% is a big deal and the benefits will be obvious.
Lastly, Index maintenance is resource intensive. If you're getting more seeks than scans it's because your tables are indexed to support those scans. Reducing the data by 50% will certainly improve index maintenance performance; re-orgs/re-builds will be ~50% faster. Updates, inserts, deletes will be faster. Backups will be faster too.
-- Itzik Ben-Gan 2001
October 29, 2026 at 8:47 pm
Don't forget that less data potentially could mean less use of the buffer cache from inefficient queries. Not every query uses and index, so buffer cache can still matter.
plus what's above.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply