Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

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

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


Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, sql statements, T-SQL

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...