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

No comments:

Post a Comment