Microsoft documentation on the allocation of pages and extents includes the following sentence:
The SQL Server Database Engine allocates a new extent to an allocation unit only when it cannot quickly find a page in an existing extent with sufficient space to hold the row being inserted.
I was trying to find more concrete information about this behavior, specifically:
- What does "quickly" means in this context? Is this wall time? Is it the number of pages checked?
- What are some scenarios in which this can happen?
In general, does this mean that in some scenarios my tables might keep growing even if there are pages with enough free space?