This post helps us in understanding “How SQL Server Page Allocations Works”. When I drop an object SQL Server does not immediately re-use those extents for a new object. This is because the storage engine is optimized for speed to continue allocating new extents from available free space in the file rather than always going back in to routines to identify and reclaim previously dropped extents.
The storage engine maintains a pointer in the FCB for each file to point to the next available (uniform or mixed extent). It continues to move this pointer along the file as objects and extents are allocated. The storage engine does not constantly evaluate to move this pointer back to previously dropped extents until it reaches the end of the file and is faced with an auto-grow or out of space. Then routines will kick in to aggressively start going back through the file to reclaim available extents. The reason why the storage engine does the allocations this way is primarily for speed and performance. If the storage engine was always trying to go back through the file to look for previously available extents then allocation of new extents would be much slower and fragmented rather than continuing along the available space in the file. The storage engine will only get aggressive to reclaim space when it has no other alternative and space at the end of the file is not sufficient and then go back through to see where it can find space to handle the new allocations.
As an example, say my database file looks like the following with free available extents at the end of the file and my FCB points to extent 101 as the next available uniform extent: Now we will see how SQL Server Page Allocations Works.
I then create a new table and insert some data. The storage engine allocated extents 101-103 for my new data. The FCB pointer was moved along and now points to extent 104 as the next available uniform extent:
Then I do what I needed to do with that object data and decide to drop the object. Extents 101-103 are no longer in use (there are other cleanup routines that go through and mark extents as available). Note that the FCB pointer continues to point to extent 104 as the next available uniform extent:
I then decide to create another table and insert some data. The storage engine looks at the pointer for the next uniform extent and starts allocation for my new object at extent 104. Extents 105 and 105 are allocated for my new object and the FCB next available uniform extent pointer is moved along to 106:
