Tag Archives: Truncate Table

Truncate Table or “So what would say you do here?”

I’ve heard many times (and said it myself) that TRUNCATE TABLE doesn’t delete data like the DELETE statement does. It is transactional but instead of deleting the data it removes the allocations. I decided I wanted to know what it really does behind the scenes so I set up a small sample database to test and track it.

So what am I testing? I’m testing to see what happens to the pages in a SQL Server file that manage allocations when a table is truncated. The pages in question are the PFS page, the GAM page, the SGAM page and the IAM page/chain for the table. To learn more about these pages I recommend reading Paul Randal’s blog posts about them.
PFS, GAM and SGAM pages

You will also want to read up about extents.
Extents

My database, TruncateTest, has one table, dbo.mytest. This table has one column, a varchar(8000). If I use the entire 8000 characters of the varchar, I can use a single page for each record. This makes it easy to set up the database pages I need for the test.

I have inserted 16 records into dbo.mytest. The first 8 pages should get allocated to mixed extents and the second 8 pages should be given a dedicated extent. Using DBCC IND and DBCC PAGE I have been able to see what the GAM, SGAM, PFS and IAM allocations are for these records. I’m going to be looking at a lot of database pages and it may get confusing. I summarize this data at the end in a neat little chart to make it easier to grasp. The data I show comes out of SQL Server is so you know I’m not making it up.

First I want to look at the IAM page to see what extents and pages my database is using.
To find out what database page the IAM page is on I will use DBCC IND

DBCC IND ('TruncateTest','mytest',1)

IAM page

The IAM page is the page from the previous results that doesn’t have an IAM file ID (IAMFID) and an IAM page ID (IAMPID) listed. It is page 1:154. Once I have that I am going to use DBCC PAGE to look at the IAM to see which of my pages are allocated to mixed extents and which are allocated to a dedicated extent.

DBCC PAGE ('TruncateTest',1,154,3)

IAM: Single Page Allocations @0x00000000131EA08E

Slot 0 = (1:153) Slot 1 = (1:155) Slot 2 = (1:156)
Slot 3 = (1:157) Slot 4 = (1:158) Slot 5 = (1:159)
Slot 6 = (1:168) Slot 7 = (1:169)

IAM: Extent Alloc Status Slot 1 @0x00000000131EA0C2

(1:0) – (1:168) = NOT ALLOCATED
(1:176) – = ALLOCATED
(1:184) – (1:2552) = NOT ALLOCATED

The pages dbo.mytest is using (you can assum all of these start with 1:):
153, 155, 156, 157, 158, 159, 168, 169, 176, 177, 178, 179, 180, 181, 182, 183.

We can see that 153, 155, 156, 157, 158, 159, 168 and 169 are single page allocations while the extent that ranges from 1:176-1:183 is part of a dedicated allocation.

We can confirm this by looking at the PFS page (which is page 1 of file 1)

DBCC PAGE ('TruncateTest',1,1,3)

(1:152) – = ALLOCATED 0_PCT_FULL Mixed Ext
(1:153) – = ALLOCATED 100_PCT_FULL Mixed Ext
(1:154) – = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:155) – (1:159) = ALLOCATED 100_PCT_FULL Mixed Ext
(1:160) – (1:162) = ALLOCATED 0_PCT_FULL
(1:163) – (1:167) = NOT ALLOCATED 0_PCT_FULL
(1:168) – (1:170) = ALLOCATED 100_PCT_FULL Mixed Ext
(1:171) – = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:172) – (1:173) = ALLOCATED 100_PCT_FULL Mixed Ext
(1:174) – = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:175) – = ALLOCATED 100_PCT_FULL Mixed Ext
(1:176) – (1:183) = ALLOCATED 100_PCT_FULL

The pages the IAM page report as being in mixed extents are here also listed as being in mixed extents. The pages the IAM page report as being in a dedicated extent are also listed here as being in a dedicated extent, those in extent 1:176 – 1:183.

So we have the results of the looking at the IAM page for dbo.mytest and of the PFS page. There are two other pages I want to look at as well the GAM and SGAM pages.

The GAM page is page 2 of the database file

DBCC PAGE ('TruncateTest',1,2,3)

(1:0) – (1:192) = ALLOCATED
(1:200) – (1:2552) = NOT ALLOCATED

This shows that the extents that exist in the page ranges from 1:0 to 1:192 are all allocated. The minimum and maximim page numbers for dbo.mytest are 1:153 and 1:183 so all of my pages are in this allocated range. In my chart below I indicate this with a 0 because an unallocated bit in the GAM bitmap is 1. When an extent gets allocated the bit gets set to 0.

Now the SGAM page

DBCC PAGE ('TruncateTest',1,3,3)

(1:0) – (1:184) = NOT ALLOCATED
(1:192) – = ALLOCATED
(1:200) – (1:2552) = NOT ALLOCATED

The SGAM page is different than a GAM page. An unallocated extent in the SGAM bitmap is 0. When an extent is marked as allocated the bit is set to 1. When the bit is set to 1 this means that the extent is a mixed extent with a page available (go read Paul’s post if this doesn’t make sense.) In our case all of the extents are not allocated. This means that the extents are either mixed with no pages available for use or that the extent is dedicated to a single object. From the previous look at the PFS and IAM pages we know that we have mixed and dedicated extents. I’ve summarized it below in a nice little chart.

Extent Mixed Page GAM SGAM PFS IAM
1:152-159 Yes 1:153 0 0 Allocated Single Page
1:154 Allocated IAM page
1:155 Allocated Single Page
1:156 Allocated Single Page
1:157 Allocated Single Page
1:158 Allocated Single Page
1:159 Allocated Single Page
1:168-175 Yes 1:168 0 0 Allocated Single Page
1:169 Allocated Single Page
1:176-1:183 No 1:176 0 0 Allocated Extent
1:177 Allocated
1:178 Allocated
1:179 Allocated
1:180 Allocated
1:181 Allocated
1:182 Allocated
1:183 Allocated

So, now the question is, what happens to all of these when I truncate the table. Let’s find out.

TRUNCATE TABLE dbo.mytest

First I will use DBCC IND to see what pages are allocated to my table:

DBCC IND ('TruncateTest','mytest',1)

The blank above this line represents the big fat nothing that the query returned. There are apparently no pages allocated to my table. I will use DBCC PAGE to take a look at what used to be the IAM page for this table:

DBCC PAGE ('TruncateTest',1,154,3)

Slot 0 = (0:0) Slot 1 = (0:0) Slot 2 = (0:0)
Slot 3 = (0:0) Slot 4 = (0:0) Slot 5 = (0:0)
Slot 6 = (0:0) Slot 7 = (0:0)

IAM: Extent Alloc Status Slot 1 @0x00000000131EA0C2

(1:0) – (1:2552) = NOT ALLOCATED

There are no pages allocated by this IAM page anymore.

The PFS page:
(1:152) – = ALLOCATED 0_PCT_FULL Mixed Ext
(1:153) – = NOT ALLOCATED 100_PCT_FULL Mixed Ext
(1:154) – = NOT ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:155) – (1:159) = NOT ALLOCATED 100_PCT_FULL Mixed Ext
(1:160) – (1:162) = ALLOCATED 0_PCT_FULL
(1:163) – (1:167) = NOT ALLOCATED 0_PCT_FULL
(1:168) – (1:169) = NOT ALLOCATED 100_PCT_FULL Mixed Ext
(1:170) – = ALLOCATED 100_PCT_FULL Mixed Ext
(1:171) – = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:172) – (1:173) = ALLOCATED 100_PCT_FULL Mixed Ext
(1:174) – = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
(1:175) – = ALLOCATED 100_PCT_FULL Mixed Ext
(1:176) – (1:183) = NOT ALLOCATED 100_PCT_FULL

The pages in dbo.mytest that used to show as allocated in the PFS page are now all not allocated. Page 154, which was the IAM chain is also unallocated.

The GAM page which shows the allocation of each extent:
(1:0) – (1:168) = ALLOCATED
(1:176) – = NOT ALLOCATED
(1:184) – (1:192) = ALLOCATED
(1:200) – (1:2552) = NOT ALLOCATED

The pages in the range from 1:0 to 1:168 show as still allocated. This is because they are mixed extents with some pages that are still being used by objects other than dbo.mytest. So they are still allocated. However extent 1:176 is not allocated. This extent was dedicated to dbo.mytest. It is now unallocated.

Finally the SGAM page:
(1:0) – (1:144) = NOT ALLOCATED
(1:152) – = ALLOCATED
(1:160) – = NOT ALLOCATED
(1:168) – = ALLOCATED
(1:176) – (1:184) = NOT ALLOCATED
(1:192) – = ALLOCATED
(1:200) – (1:2552) = NOT ALLOCATED

The mixed extents at 1:152 and 1:168 that held some of the pages from dbo.mytest are now showing as allocated whereas before they were not. This is because the pages related to dbo.mytest have been unallocated and those pages in the mixed extent are now available to be used by SQL Server to store pages for other objects.

An updated table of the allocations.

Extent Mixed Page GAM SGAM PFS IAM
1:152-159 Yes 1:153 0 1 Not Allocated None
1:154 Not Allocated None
1:155 Not Allocated None
1:156 Not Allocated None
1:157 Not Allocated None
1:158 Not Allocated None
1:159 Not Allocated None
1:168-175 Yes 1:168 0 1 Not Allocated None
1:169 Not Allocated None
1:176-1:183 No 1:176 1 0 Not Allocated None
1:177 Not Allocated
1:178 Not Allocated
1:179 Not Allocated
1:180 Not Allocated
1:181 Not Allocated
1:182 Not Allocated
1:183 Not Allocated

When I take a before and after shot of the data pages in dbo.mytest they all still contain the data as if they were active records. The bit patterns on the data pages are the shape of what used to be a valid record. They are essentially abandoned in the wild to fend for themselves.

The advantage of a truncate is that each page and record doesn’t have to be touched. This means that each page doesn’t have to be loaded into the buffer pool, which takes disk I/O and memory usage. It also means that the transaction doesn’t have to contain the information needed to roll the delete back if necessary (which means more I/O). The transaction for a truncate table only has to hold enough information to roll back changes to a few pages, the GAM, SGAM, PFS and IAM pages.

So, it appears that a TRUNCATE TABLE:

  • Removes the allocations from the IAM page. Even though the data pages don’t get their data removed the IAM pages do.
  • Removes the allocations from the GAM page if the extent is dedicated to the object.
  • Adds an allocation to the SGAM page to show that the mixed extent has pages available.
  • Removes the allocations from the PFS page.