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
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name='demoCompressionINTERNALS')
BEGIN
    DROP DATABASE demoCompressionINTERNALS
END
CREATE DATABASE demoCompressionINTERNALS
GO
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)
GO
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)
GO

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
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
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
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name='demoCompressionINTERNALS')
BEGIN
    DROP DATABASE demoCompressionINTERNALS
END
CREATE DATABASE demoCompressionINTERNALS
GO
USE demoCompressionINTERNALS
GO
/*
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'
    );
GO

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)
GO
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)
GO

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)
GO



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)
GO
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)
GO

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
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1               

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

myChar2 = bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
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)
GO
DBCC PAGE('demoCOMPRESSIONInternals', 1, 160, 3)
GO

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

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)
GO
DBCC IND(demoCOMPRESSIONInternals, 'heap1', 1)
GO 
DBCC PAGE('demoCOMPRESSIONInternals', 1, 200, 3)
GO


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
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
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
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
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)
GO

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
  1.  When data is inserted into a heap using Bulk Insert
  2. When data is inserted into and a full table lock is placed on the table.
  3. 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