I just want to know:
- How SQL Server reuses reserved space after dropping a 1GB table? (call the table T1)
- How SQL Server reuses reserved space after truncating a table? (call this table T2)
Both are clustered tables (not heaps).
Add a comment
|
1 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.
answered Oct 3, 2017 at 20:14
