SQLBalls

Thursday, June 9, 2011

SQL Rally Q & A: Page Compression, Heaps, and Forwarding Pointers

One of my favorite parts of presenting is the questions you get. It is always nice to hear the questions people have, because it helps you to expand your knowledge. I got three questions after my presentation that lead to really nice follow up conversations, and I felt each answer deserved a blog. One was from SQL MVP Louis Davidson (@DrSQL|Blog)

The question was, and I’m paraphrasing “Does the behavior Forwarding Pointers change in Heaps?” The short answer is no, and Dr. SQL knew this but was still kind (and smart) enough to ask, but Dear Reader let’s prove it out just for fun.

THERE’S NO DATA HERE JUST POINTERS

Yesterday I wrote about Heaps & Compression, this was to give a bit of an introduction to this topic. So as we covered yesterday when a Page on a Heap has Page Compression applied to it, subsequent inserts are not in a Page Compressed format, there are three exceptions to this for Bulk Insert, Insert Into with (TABLOCK) specified, or when Rebuilding a table specifying Page Compression. *Actually it is 4, after you Rebuild your Heap once specifying Page Compression, you can rebuild it after and it will be Page Compressed, unless you specify a different type of Compression.

Let’s start off by creating our Demo Database.

USE master

IF EXISTS(SELECT * FROM sys.databases WHERE name='demoCompressionINTERNALS')

BEGIN

DROP DATABASE demoCompressionINTERNALS

END

CREATE DATABASE demoCompressionINTERNALS

USE demoCompressionINTERNALS

GO

Now let’s add 460 rows of Data maxed out so that we fill our uncompressed pages. I’m adding so many rows because I’ve optimized these records for Compression and I want to pack my Page tight so I can guarantee that I will get a Forwarded Record.

DECLARE @i INT, @myID INT

SET @i=0

WHILE (@i<460)

BEGIN

SET @myID=(SELECT (MAX(myid)+1) FROM dbo.heap1)

INSERT INTO dbo.heap1(myChar1, myChar2)

VALUES(

(REPLICATE('a', (100- LEN(@myID))) + CAST(@myID AS VARCHAR(3)))

,(REPLICATE('b', (500- LEN(@myID))) + CAST(@myID AS VARCHAR(3)))

)

SET @i=@i+1

END

Set Trace Flag 3604 on, so we can use DBCC Page with output to SSMS later. And here’s a DBCC IND to view our Pages.

DBCC TRACEON(3604)

DBCC IND(demoCompressionINTERNALS, 'heap1',1)

Now let’s apply Page Compression.

ALTER TABLE dbo.heap1

REBUILD WITH (DATA_COMPRESSION=Page)

GO
Here’s another DBCC IND to view our new Pages.

DBCC IND(demoCompressionINTERNALS, 'heap1',1)

If we do a DBCC Page we see Page 170 is empty and Page 224 has our records. Let’s do a quick view to see the our Page Headers to validate the space.

Page 170

m_pageId = (1:170) m_headerVersion = 1 m_type = 1

m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0

m_objId (AllocUnitId.idObj) = 28 m_indexId (AllocUnitId.idInd) = 256

Metadata: AllocUnitId = 72057594039762944

Metadata: PartitionId = 72057594038845440 Metadata: IndexId = 0

Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)

pminlen = 4 m_slotCnt = 0 m_freeCnt = 8096

m_freeData = 96 m_reservedCnt = 0 m_lsn = (50:93:13)

m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0

m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED

PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED

Page 224

m_pageId = (1:224) m_headerVersion = 1 m_type = 1

m_typeFlagBits = 0x80 m_level = 0 m_flagBits = 0x0

m_objId (AllocUnitId.idObj) = 28 m_indexId (AllocUnitId.idInd) = 256

Metadata: AllocUnitId = 72057594039762944

Metadata: PartitionId = 72057594038845440 Metadata: IndexId = 0

Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)

pminlen = 4 m_slotCnt = 460 m_freeCnt = 5

m_freeData = 7267 m_reservedCnt = 0 m_lsn = (50:93:65)

m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0

m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL

DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED

So now let’s update Row 4, a rewrite will place this data in a Row Compressed Format.

UPDATE dbo.heap1

SET myChar1 = (REPLICATE('c', 99) + CAST(4 AS VARCHAR(1))),

myChar2 = (REPLICATE('d', 499) + CAST(4 AS VARCHAR(1)))

WHERE myID=4

Let’s take a look at Page 224 again, and we’ll see that a forwarding record is in place of our Data.

DBCC PAGE('demoCOMPRESSIONInternals', 1, 224, 3)

Slot 3 Offset 0x1c59 Length 9

Record Type = (COMPRESSED) FORWARDING_STUB Record size = 9

CD Array

Record Memory Dump

63FBDC59: 09aa0000 00010000 00††††††††††††††††† ª.......

Forwarding to = file 1 page 170 slot 0

And it tells us right there, if we look on Page 170 then we see there is our Data Record, sitting in Slot 0. So let’s look at page 170.

m_pageId = (1:170) m_headerVersion = 1 m_type = 1

m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8

m_objId (AllocUnitId.idObj) = 28 m_indexId (AllocUnitId.idInd) = 256

Metadata: AllocUnitId = 72057594039762944

Metadata: PartitionId = 72057594038845440 Metadata: IndexId = 0

Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)

pminlen = 4 m_slotCnt = 1 m_freeCnt = 7474

m_freeData = 1336 m_reservedCnt = 0 m_lsn = (50:171:6)

m_xactReserved = 0 m_xdesId = (0:1514) m_ghostRecCnt = 0

m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED

PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 1

myID = 4

Slot 0 Column 2 Offset 0xc Length 100 Length (physical) 100

myChar1 = ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc4

Slot 0 Column 3 Offset 0x70 Length 500 Length (physical) 500

myChar2 = ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd4

So the short answer is that it doesn’t, change the way that Forwarded Records behave, but it sure is a lot of fun to play around with.

Thanks,

Brad

Wednesday, June 8, 2011

HEAPS & COMPRESSION

Compression did not work out so well for Bessy

Heaps are really interesting animals when it comes to Compression. They start out having a lot in common with Clustered Indexes, but they stop just after creation.

You can apply compression to a Heap or Clustered Index upon creation, or you can apply compression to a Heap or Clustered Index using a Rebuild Operation and specifying the type of compression you would like to apply.

After that the behaviors diverge. When a Heap is rebuilt and Page Compression is Applied, even if you only have enough data to occupy one page, two will be provisioned.

You will always get One new page that does not have any data on it. In the case of our table with only Two pages, Page One will contain your data in its Page Compressed format, as long as an internal algorithm can determine that you would save enough space to fully apply Page compression, and subsequent inserts will be in a row Compressed format. Page Two will only be row compressed until it is rebuilt. But don’t take my word for it let’s prove this out.

First I’m going to create my database and my table that we’ll be using for this demo.

USE master

IF EXISTS(SELECT * FROM sys.databases WHERE name='demoCompressionINTERNALS')

BEGIN

DROP DATABASE demoCompressionINTERNALS

END

CREATE DATABASE demoCompressionINTERNALS

USE demoCompressionINTERNALS

Create a Heap Table

IF EXISTS(SELECT * FROM sys.tables WHERE name='heap1')

BEGIN

DROP TABLE dbo.heap1

END

CREATE TABLE heap1(

myID int IDENTITY(1,1)

,myChar1 CHAR(500) default 'some text'

,myChar2 Char(1000) default 'more text for a larger field'

);

Our table has 1504 bytes per record, so we’ll insert 5 records to fill up our page. Let’s run our script to populate the page with exactly 5 records.

DECLARE @i INT

SET @i=0

WHILE (@i<5)

BEGIN

INSERT INTO dbo.heap1(myChar1, myChar2)

VALUES(

('some product' + CAST((@i +1) AS VARCHAR(1)))

,('Here is a Generic Product Description' + CAST((@i+2) AS VARCHAR(1)))

)

SET @i = @i +1

END

We’ll set trace flag 3604 so we can get the output of DBCC Page to SSMS, we’ll run DBCC IND to get our pages, and finally let’s run DBCC Page on our data page and see the internal contents.

DBCC TRACEON(3604)

DBCC IND(demoCompressionINTERNALS, 'heap1',1)

We’ll Go ahead and run that.

You can see that I get back 2 pages 146 & 145. Looking at the picture I’ve highlighted the PageType field. Page Type 1 is a Data Page, and that is the type we are interested in. Page 10 is an IAM page. We want to look at our Data Page, so we’ll take page 145 and look at it. For the sake of space I’m going to hop down to the first record on the page after the hex dump. We’ll look at Slot 0.

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

myID = 1

Slot 0 Column 2 Offset 0x8 Length 500 Length (physical) 500

myChar1 = some product1

Slot 0 Column 3 Offset 0x1fc Length 1000 Length (physical) 1000

myChar2 = Here is a Generic Product Description2

We are looking at our un-compressed Record. Page Compression has three parts to it that are applied in the following order, Row Compression, Column Prefix Compression, and Page Dictionary Compression.

So first let’s apply Row Compression and see our space savings.

ALTER TABLE dbo.heap1

REBUILD WITH (DATA_COMPRESSION=ROW)

We need to re-run DBCC IND & DBCC Page as our Rebuild operation will cause our page numbers to change.

DBCC IND(demoCOMPRESSIONInternals, 'heap1', 1)

As you can see our page numbers changed, and we now have our second page has been allocated. If we look at page 160 our data is on that page, and 147 is blank. So let’s look at Slot 0

DBCC PAGE('demoCOMPRESSIONInternals', 1, 160, 3)

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 1

myID = 1

Slot 0 Column 2 Offset 0xc Length 500 Length (physical) 13

myChar1 = some product1

Slot 0 Column 3 Offset 0x19 Length 1000 Length (physical) 38

myChar2 = Here is a Generic Product Description2

Row Compression did exactly what it was supposed to, it took the fixed length strings and stored only the space required. So if we max out our columns we see that our sizes mirror what it was before row compression.

DECLARE @i INT

SET @i=0

WHILE (@i<5)

BEGIN

UPDATE dbo.heap1

SET myChar1 = (REPLICATE('a', 499) + CAST(@i AS VARCHAR(1)))

WHERE myID=@i

UPDATE dbo.heap1

SET myChar2 = (REPLICATE('b', 999) + CAST(@i AS VARCHAR(1)))

WHERE myID=@i

SET @i=@i+1

END

DBCC PAGE('demoCOMPRESSIONInternals', 1, 160, 3)

We see that our data has grown, and that the physical size now matches the logical size again, everything except our ID column.

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 1

myID = 1

Slot 0 Column 2 Offset 0xc Length 500 Length (physical) 500

myChar1 = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1

Slot 0 Column 3 Offset 0x200 Length 1000 Length (physical) 1000

myChar2 = bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb1

Let’s add some more data so we have more than 1 page worth of data. We will need this data in order to get Page Compression to apply.

DECLARE @i INT, @myID INT

SET @i=0

WHILE (@i<10)

BEGIN

SET @myID=(SELECT (MAX(myid)+1) FROM dbo.heap1)

INSERT INTO dbo.heap1(myChar1, myChar2)

VALUES(

(REPLICATE('a', (250- LEN(@myID))) + CAST(@myID AS VARCHAR(3)))

,(REPLICATE('b', (500- LEN(@myID))) + CAST(@myID AS VARCHAR(3)))

)

SET @i=@i+1

END

If we take a look at our two pages, 160 & 140 we see that they are both populated with rows.

DBCC IND(demoCOMPRESSIONInternals, 'heap1', 1)

DBCC PAGE('demoCOMPRESSIONInternals', 1, 160, 3)

DBCC PAGE('demoCOMPRESSIONInternals', 1, 147, 3)

So now we will apply Page Compression. We will need to run DBCC IND & DBCC Page again, as this will rebuild our pages.

ALTER TABLE dbo.heap1

REBUILD WITH (DATA_COMPRESSION=Page)

DBCC IND(demoCOMPRESSIONInternals, 'heap1', 1)

DBCC PAGE('demoCOMPRESSIONInternals', 1, 200, 3)

As you can see we have our pages again. And after running DBCC Page we’ll see that we have a CI, or Compression Information Header Section. This is where Page Compression will store the CI that it uses for Column Prefix and Page Dictionary Compression.

Just because the ID of 6 appears in our data doesn’t mean we are going to Slot 5 (remember zero based array’s). This is a heap and our data is not sorted off of the ID column like it is with a Clustered Index. Slot 0 contains our record with an ID of 6 and that corresponds with our CI information. This will be our most compressed record. I’ll go down further and grab Slot 6 for us to use as a comparison.

* Note the difference in the record lengths in the Compression Data Array, CD array entry. I’ll highlight those in yellow.

CD array entry = Column 1 (cluster 0, CD array offset 0): 0x02 (ONE_BYTE_SHORT)

CD array entry = Column 2 (cluster 0, CD array offset 0): 0x01 (EMPTY)

CD array entry = Column 3 (cluster 0, CD array offset 1): 0x01 (EMPTY)

Record Memory Dump

000000001443A35E: 01031211 86000000 00†††††††††††††††††....†....

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 1

myID = 6

Slot 0 Column 2 Offset 0x0 Length 500 Length (physical) 0

myChar1 = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa6

Slot 0 Column 3 Offset 0x0 Length 1000 Length (physical) 0

myChar2 = bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb6

Slot 6 Offset 0x3a2 Length 13

Record Type = (COMPRESSED) PRIMARY_RECORD Record size = 13

CD Array

CD array entry = Column 1 (cluster 0, CD array offset 0): 0x02 (ONE_BYTE_SHORT)

CD array entry = Column 2 (cluster 0, CD array offset 0): 0x05 (FOUR_BYTE_SHORT)

CD array entry = Column 3 (cluster 0, CD array offset 1): 0x05 (FOUR_BYTE_SHORT)

Record Memory Dump

000000001443A3A2: 01035215 8c80f831 3281f231 32††††††††..R.Œ.ø12.ò12

Slot 6 Column 1 Offset 0x4 Length 4 Length (physical) 1

myID = 12

Slot 6 Column 2 Offset 0x5 Length 500 Length (physical) 4

myChar1 = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa12

Slot 6 Column 3 Offset 0x9 Length 1000 Length (physical) 4

myChar2 = bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb12

So when we look at our other page we will see that there are no records on it. Go do a DBCC Page and take a look just to confirm.

DBCC PAGE('demoCOMPRESSIONInternals', 1, 155, 3)

So let’s insert a couple more records, we should have plenty of room to fin on this page right?

DECLARE @i INT, @myID INT

SET @i=0

WHILE (@i<10)

BEGIN

SET @myID=(SELECT (MAX(myid)+1) FROM dbo.heap1)

INSERT INTO dbo.heap1(myChar1, myChar2)

VALUES(

(REPLICATE('a', (250- LEN(@myID))) + CAST(@myID AS VARCHAR(3)))

,(REPLICATE('b', (500- LEN(@myID))) + CAST(@myID AS VARCHAR(3)))

)

SET @i=@i+1

END

Now take another look at Page 155, you’ll see that these rows take up around 762 bytes each, and that and are not in a Page Compressed format, this looks more like a Row Compressed format and it is.

Slot 0 Offset 0x60 Length 762

Record Type = (COMPRESSED) PRIMARY_RECORD Record attributes = LONG DATA REGION

Record size = 762

CD Array

CD array entry = Column 1 (cluster 0, CD array offset 0): 0x02 (ONE_BYTE_SHORT)

CD array entry = Column 2 (cluster 0, CD array offset 0): 0x0a (LONG)

CD array entry = Column 3 (cluster 0, CD array offset 1): 0x0a (LONG)

“But Balls,” you say, “I thought we enabled Page Compression?” Ahh Dear Reader, we did. However, Heaps do not automatically apply Page Compression when data is added to a page. There are 3 exceptions to this

When data is inserted into a heap using Bulk Insert
When data is inserted into and a full table lock is placed on the table.
When a heap is Rebuilt specifying the with (Data_Compression) option, as we did above.

So when you use Page Compression together with Heaps keep their behavior in mind so you don’t encounter any surprises. Good luck and Happy Compressing!

Thanks,

Brad