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.
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.
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.
Now let’s apply Page Compression.
Here’s another DBCC IND to view our new Pages.
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
Page 224
So now let’s update Row 4, a rewrite will place this data in a Row Compressed Format.
Let’s take a look at Page 224 again, and we’ll see that a forwarding record is in place of our Data.
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.
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
USE master
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name='demoCompressionINTERNALS')
BEGIN
DROP DATABASE demoCompressionINTERNALS
END
CREATE DATABASE demoCompressionINTERNALS
GO
USE demoCompressionINTERNALS
GONow 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
ENDSet 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)
GOHere’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=4Let’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)
GOSlot 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 0And 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_LOGGEDSlot 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
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd4So 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