DBA Myths: TRUNCATE TABLE deletes all the rows of a table instantly.

7

April 30, 2014 by Kenneth Fisher

Most DBAs and developers I’ve talked to over the years have felt that TRUNCATE TABLE is an instant DELETE. However would you believe that it doesn’t actually delete the data from the pages at all? Here is an example:

-- Setup code
CREATE DATABASE TruncateTest
GO
USE TruncateTest
GO
CREATE TABLE dbo.TruncateTable (Col1 varchar(500),
	Col2 varchar(500))
GO
INSERT INTO dbo.TruncateTable VALUES (REPLICATE('A',500),
	'Here I Am')
GO 500
-- Get a page id.  I'm going to pick out
-- one of the later data pages for no particular reason.
SELECT allocated_page_file_id, allocated_page_page_id,
	page_type_desc, *
FROM sys.dm_db_database_page_allocations(db_id(),
	OBJECT_ID('TruncateTable'), NULL, NULL, 'DETAILED')
-- Check space used by the table
EXEC sp_spaceused 'dbo.TruncateTable'
GO
-- Required so that the DBCC PAGE output will display
-- on the screen.
DBCC TRACEON (3604);
GO
-- Show the contents of the data page.
DBCC PAGE ('TruncateTest', 1, 325, 3);
GO

Here is the space currently taken up by the table.

Truncate_sp_spaceused_Before1

And here is the first part of the contents of the page.

PAGE: (1:325)

BUFFER:

BUF @0x00000001FA0FA8C0

bpage = 0x00000001E8476000          bhash = 0x0000000000000000          bpageno = (1:325)
bdbid = 11                          breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 41794                       bstat = 0xb
blog = 0x1cc                        bnext = 0x0000000000000000          

PAGE HEADER:

Page @0x00000001E8476000

m_pageId = (1:325)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 84    m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594043432960
Metadata: PartitionId = 72057594039042048                                Metadata: IndexId = 0
Metadata: ObjectId = 245575913      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 4                         m_slotCnt = 11                      m_freeCnt = 2332
m_freeData = 5838                   m_reservedCnt = 0                   m_lsn = (33:440:2)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x42 ALLOCATED  80_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

Slot 0 Offset 0x60 Length 522

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 522
Memory Dump @0x000000000AC5A060

0000000000000000:   30000400 02000002 0001020a 02414141 41414141  0............AAAAAAA
0000000000000014:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000028:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
000000000000003C:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000050:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000064:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000078:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
000000000000008C:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000000A0:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000000B4:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000000C8:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000000DC:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000000F0:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000104:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000118:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
000000000000012C:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000140:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000154:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000168:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
000000000000017C:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000190:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000001A4:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000001B8:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000001CC:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000001E0:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000001F4:   41414141 41414141 41414141 41486572 65204920  AAAAAAAAAAAAAHere I
0000000000000208:   416d                                          Am     

Slot 0 Column 1 Offset 0xd Length 500 Length (physical) 500

Col1 = AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA                  

Slot 0 Column 2 Offset 0x201 Length 9 Length (physical) 9

Col2 = Here I Am                    

Slot 1 Offset 0x26a Length 522

I’m only showing the page header and the first row on the page since 8k of information is a bit much to put in a blog.

Next we truncate the table.

-- Truncate the table
TRUNCATE TABLE dbo.TruncateTable
GO

Now we run the same tests as before

-- Check the space used by the DB
	after the truncate.  There is no change
EXEC sp_spaceused
GO
-- Check space used by the table after the truncate.
EXEC sp_spaceused 'dbo.TruncateTable'
GO
-- Display the actual page.
DBCC PAGE ('TruncateTest', 1, 325, 3);
GO

Note the table now shows 0 rows and 0 space used.

Truncate_sp_spaceused_After

If you skip down past the header you will see that the data portion has not changed.

 PAGE: (1:325)

BUFFER:

BUF @0x00000001FA0FA8C0

bpage = 0x00000001E8476000          bhash = 0x0000000000000000          bpageno = (1:325)
bdbid = 11                          breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 42120                       bstat = 0xb
blog = 0x1cc                        bnext = 0x0000000000000000          

PAGE HEADER:

Page @0x00000001E8476000

m_pageId = (1:325)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 84    m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594043432960
Metadata: PartitionId = 72057594039042048                                Metadata: IndexId = 0
Metadata: ObjectId = 245575913      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 4                         m_slotCnt = 11                      m_freeCnt = 2332
m_freeData = 5838                   m_reservedCnt = 0                   m_lsn = (33:440:2)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                      

Allocation Status

GAM (1:2) = NOT ALLOCATED           SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x2  80_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

Slot 0 Offset 0x60 Length 522

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 522
Memory Dump @0x000000000138A060

0000000000000000:   30000400 02000002 0001020a 02414141 41414141  0............AAAAAAA
0000000000000014:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000028:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
000000000000003C:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000050:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000064:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000078:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
000000000000008C:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000000A0:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000000B4:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000000C8:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000000DC:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000000F0:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000104:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000118:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
000000000000012C:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000140:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000154:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000168:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
000000000000017C:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000000000000190:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000001A4:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000001B8:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000001CC:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000001E0:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
00000000000001F4:   41414141 41414141 41414141 41486572 65204920  AAAAAAAAAAAAAHere I
0000000000000208:   416d                                          Am     

Slot 0 Column 1 Offset 0xd Length 500 Length (physical) 500

Col1 = AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA                  

Slot 0 Column 2 Offset 0x201 Length 9 Length (physical) 9

Col2 = Here I Am                    

Slot 1 Offset 0x26a Length 522

If you look for the GAM entry on line 32 you will see that it has gone from ALLOCATED to UNALLOCATED. This is because the page is no longer assigned as a data page for the table even though the data is still available. Heck even the column information is still there. This data will remain until the page is re-allocated for a new use.

Here is some additional reading you can do on how truncate works and the undocumented commands that I used.

Information about DBCC PAGE

Information about sys.dm_db_database_page_allocations

Additional information on how the truncate command works behind the scenes

More reading I just found out about. Also the way this works is covered under the term “deferred-drop”. Basically the work happens later behind the scenes.

Dropping and Rebuilding Large Objects

The Myth that DROP and TRUNCATE TABLE are Non-Logged

7 thoughts on “DBA Myths: TRUNCATE TABLE deletes all the rows of a table instantly.

  1. temp says:

    What did you expect? Truncate is a quick operation. Do you want to perform some kind of secure deletion? That will take a lot longer. Computers allocate and deallocate memory, but the data is still there until it’s overwritten. I thought everybody knew that, but then again I’ve never been a dba and I’ve been retired from computing for a while.

  2. silentdba says:

    Well levels of knowledge as well as areas of knowledge vary greatly in the tech field. There is a lot of knowledge out there and people sharing that knowledge in their own way is a wonderful thing. Some things that might seem as obvious and universally known to you. Could be something someone else never heard of or thought about before.

    Something like truncate is a perfect example of something that some people probably just use and don’t really think about what it is doing on a more in depth level.

    I have known some DBAs and programmers that have never l even looked at a execution plan. They just write a query or stored procedure and know that if give them back the data they want. The don’t think about the underlying process of what is going on to retrieve that data.

  3. Nice write up on an important misconception.

  4. Thanks for sharing such nice info in depth with examples.

  5. jay says:

    It should be no suprise. Truncate is a fast low overhead operation. That’s a good thing

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,134 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: