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 task, if enable housekeeper GC is set to 1
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 task compacts unused space, but it runs only when no other tasks are requesting CPU time, so it may not reach every page that needs it.
