SQL Server: How Reserved Pages are Reused After Deleting a Table

database-internalsdisk-spacesql server

I just want to know:

  1. How SQL Server reuses reserved space after dropping a 1GB table? (call the table T1)
  2. How SQL Server reuses reserved space after truncating a table? (call this table T2)

Both are clustered tables (not heaps).

Best Answer

Quite simply a DROP or TRUNCATE statement marks any allocated pages that were associated with the table (regardless if it was a heap or a clustered table) as no longer being allocated and the space is reused by the next operation that requires additional space (e.g. new records, a page split, etc.).

From sys.allocation_units (Transact-SQL):

ⓘ Note

When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sys.allocation_units immediately after dropping or truncating a large object may not reflect the actual disk space available.

A TRUNCATE TABLE operation is effectively the same thing as a DROP TABLE statement, but it comes with the bonus of recreating the table immediately after it has been dropped. This is why a TRUNCATE TABLE statement is a Data Definition Language (DDL) statement and not a Data Manipulation Language statement such as a DELETE.

If you want to look at how the engine deallocates and reuses space, you can dig into the DBCC PAGE undocumented function. Paul Randal has a few articles (e.g. ref1, ref2, ref3, ref4) about its use that you can read through if you're so inclined.

If you've got a sandbox environment, you can also run the following series of scripts which will show the pages being allocated and deallocated in the same manner, regardless if a TRUNCATE TABLE or DROP TABLE operation is performed:

-- Create Test Database
USE [master]
GO

EXECUTE AS LOGIN = 'sa'

CREATE DATABASE [TestDB]

REVERT
GO

USE [TestDB]
GO

-- Look at GAM
DBCC PAGE(0, 1, 2, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
--Only 1016 pages are reserved.  This is because that's the initial size needed by data populating a "blank" database

ParentObject   Object                   Field                     VALUE
-------------- ------------------------ ------------------------- --------------
GAM: Header    GAM: Extent Alloc Status (1:0)        - (1:312)        ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:320)      - (1:1016)   NOT ALLOCATED 
*/

-- Look at SGAM
DBCC PAGE(0, 1, 3, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
The SGAM also shows only 1016 pages are reserved, this view will remain relatively unchanged throughout the demo

ParentObject  Object                     Field                    VALUE
------------- -------------------------- ------------------------ --------------
SGAM: Header  SGAM: Extent Alloc Status  (1:0)        - (1:304)   NOT ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:312)      -               ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:320)      - (1:1016)  NOT ALLOCATED 
*/

-- Create our Test Table
CREATE TABLE T1
(
    ID  INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Value   CHAR(8000)
)
GO

-- Populate it with exactly 1GB of data
INSERT INTO T1 (Value)
SELECT TOP 131072 ''
FROM sys.configurations t1
    CROSS JOIN sys.configurations t2
    CROSS JOIN sys.configurations t3
    CROSS JOIN sys.configurations t4


-- Table is 1GB in Size, as expected
EXEC sp_spaceused 'T1'

/*
name  rows    reserved    data        index_size  unused 
----- ------- ----------- ----------- ----------- -------
T1    131072  1052616 KB  1048576 KB  3904 KB     136 KB
*/

-- Recheck GAM and SGAM
-- Look at GAM
DBCC PAGE(0, 1, 2, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
This allocates 131072 8kb data-pages for the data and 488 8kb data-pages for the index
This view shows the location of the allocated and unallocated pages within the datafile

ParentObject   Object                   Field                     VALUE
-------------- ------------------------ ------------------------- --------------
GAM: Header    GAM: Extent Alloc Status (1:0)        - (1:8080)       ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:8088)     -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:8096)     - (1:16168)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:16176)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:16184)    - (1:24256)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:24264)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:24272)    - (1:32344)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:32352)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:32360)    - (1:40432)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:40440)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:40448)    - (1:48520)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:48528)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:48536)    - (1:56608)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:56616)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:56624)    - (1:64696)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:64704)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:64712)    - (1:72784)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:72792)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:72800)    - (1:80872)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:80880)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:80888)    - (1:88960)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:88968)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:88976)    - (1:97048)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:97056)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:97064)    - (1:105136)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:105144)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:105152)   - (1:113224)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:113232)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:113240)   - (1:121312)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:121320)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:121328)   - (1:129400)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:129408)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:129416)   - (1:130480)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:130488)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:130496)   - (1:132024)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:132032)   - (1:132088) NOT ALLOCATED
*/

-- Look at SGAM
DBCC PAGE(0, 1, 3, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
This view only changed to show no additional Shared Extents are allocated, though you can see 
the number of 8kb data-pages reserved in the db grew to 132,088

ParentObject  Object                     Field                      VALUE
------------- -------------------------- -------------------------- --------------
SGAM: Header  SGAM: Extent Alloc Status  (1:0)        - (1:304)     NOT ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:312)      -                 ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:320)      - (1:132088)  NOT ALLOCATED
*/


TRUNCATE TABLE T1

-- Let the background process mark the extents as NOT ALLOCATED, so wait 10 seconds before running this
-- Recheck GAM and SGAM
-- Look at GAM
DBCC PAGE(0, 1, 2, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
This shows that all pages that were previous used by T1 have been deallocated

ParentObject   Object                   Field                     VALUE
-------------- ------------------------ ------------------------- --------------
GAM: Header    GAM: Extent Alloc Status (1:0)        - (1:312)        ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:320)      - (1:132088) NOT ALLOCATED
*/


-- Look at SGAM
DBCC PAGE(0, 1, 3, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
No changes here, as the db didn't grow and we haven't done anything regarding mixed extents

ParentObject  Object                     Field                      VALUE
------------- -------------------------- -------------------------- --------------
SGAM: Header  SGAM: Extent Alloc Status  (1:0)        - (1:304)     NOT ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:312)      -                 ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:320)      - (1:132088)  NOT ALLOCATED
*/

-- Repopulate T1
INSERT INTO T1 (Value)
SELECT TOP 131072 ''
FROM sys.configurations t1
    CROSS JOIN sys.configurations t2
    CROSS JOIN sys.configurations t3
    CROSS JOIN sys.configurations t4

-- Table is 1GB in Size
EXEC sp_spaceused 'T1'

/*
name  rows    reserved    data        index_size  unused 
----- ------- ----------- ----------- ----------- -------
T1    131072  1052616 KB  1048576 KB  3904 KB     136 KB
*/

-- Recheck GAM and SGAM
-- Look at GAM
DBCC PAGE(0, 1, 2, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
Again, this allocates 131072 8kb data-pages for the data and 488 8kb data-pages for the index
This view shows the location of the allocated and unallocated pages within the datafile and they've
likely changed slightly from when they were first allocated, though the counts remain consistent

ParentObject   Object                   Field                     VALUE
-------------- ------------------------ ------------------------- --------------
GAM: Header    GAM: Extent Alloc Status (1:0)        - (1:8080)       ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:8088)     -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:8096)     - (1:16168)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:16176)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:16184)    - (1:24256)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:24264)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:24272)    - (1:32344)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:32352)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:32360)    - (1:40432)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:40440)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:40448)    - (1:48520)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:48528)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:48536)    - (1:56608)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:56616)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:56624)    - (1:64696)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:64704)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:64712)    - (1:72784)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:72792)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:72800)    - (1:80872)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:80880)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:80888)    - (1:88960)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:88968)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:88976)    - (1:97048)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:97056)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:97064)    - (1:105136)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:105144)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:105152)   - (1:113224)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:113232)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:113240)   - (1:121312)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:121320)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:121328)   - (1:129400)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:129408)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:129416)   - (1:130416)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:130424)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:130432)   - (1:131960)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:131968)   - (1:132024) NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:132032)   - (1:132088)     ALLOCATED
*/

-- Look at SGAM
DBCC PAGE(0, 1, 3, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
Still no changes here, as the db didn't grow and we haven't done anything regarding mixed extents

ParentObject  Object                     Field                      VALUE
------------- -------------------------- -------------------------- --------------
SGAM: Header  SGAM: Extent Alloc Status  (1:0)        - (1:304)     NOT ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:312)      -                 ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:320)      - (1:132088)  NOT ALLOCATED
*/

DROP TABLE T1

-- Let the background process mark the extents as NOT ALLOCATED, so wait 10 seconds before running this
-- Recheck GAM and SGAM
-- Look at GAM
DBCC PAGE(0, 1, 2, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
Again, this shows that all pages that were previous used by T1 have been deallocated.
This is the same behavior we saw with a TRUNCATE, even though we just executed a DROP TABLE command.

ParentObject   Object                   Field                     VALUE
-------------- ------------------------ ------------------------- --------------
GAM: Header    GAM: Extent Alloc Status (1:0)        - (1:312)        ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:320)      - (1:132088) NOT ALLOCATED
*/


-- Look at SGAM
DBCC PAGE(0, 1, 3, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
Still no changes here, as the db didn't grow and we haven't done anything regarding mixed extents

ParentObject  Object                     Field                      VALUE
------------- -------------------------- -------------------------- --------------
SGAM: Header  SGAM: Extent Alloc Status  (1:0)        - (1:304)     NOT ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:312)      -                 ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:320)      - (1:132088)  NOT ALLOCATED
*/

-- Cleanup
USE [master]
GO

DROP DATABASE [TestDB]
GO

For more information on the GAM, SGAM, extents, and the basics to the storage engine, start with the Pages and Extents Architecture Guide by Microsoft. Hopefully this answers your question, though I feel you'll have more questions to follow-up with.

Related Question