Several types of activities reclaim or reorganize the use of space in a table on a page-by-page basis:
Inserts, when an insert encounters a page that would have enough room if it reclaimed unused space.
The update statistics command (for index pages only)
Re-creating clustered indexes
The housekeeper garbage collection task, if enable housekeeper GC is set to 1 or more
Each of these has limitations and may be insufficient for use on a large number of pages. For example, inserts may execute more slowly when they need to reclaim space, and may not affect many pages with space that can be reorganized. Space reclamation under the housekeeper garbage collection task compacts unused space, but a single housekeeper garbage collection task that runs at user priority may not reach every page that needs it.
