Tag Archives: IAM

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

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

DBCCIND ('TruncateTest','mytest',1)

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.

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.

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.

TRUNCATETABLE dbo.mytest

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

DBCCIND ('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:

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.

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.