Sql-server – in place updates causes forwarded records

heapsql serverupdate

I am well aware what a forwarded record within a heap is. Since I want to keep forwarded records at 0, we decided to update only on columns that could not be extended.

Recently on my system I encountered forwarded records.
Table design is like this:

CREATE TABLE dbo.test (
 HashValue BINARY(16) NOT NULL,
 LoadTime DATETIME NOT NULL,
 LoadEndTime DATETIME NULL,
[other columns that never get updates]
) WITH(DATA_COMPRESSION=PAGE);

The insert statements ALWAYS bring HashValue AND LoadTime. I checked the query logs.
I insert a value of '9999-12-31'.

Now system performs an update on LoadTime like this:

;WITH CTE AS (
SELECT *, COALESCE(LEAD(LoadTime) OVER(PARTITION BY HashValue ORDER BY LoadTime) ,'9999-12-31') as EndTimeStamp
)
UPDATE CTE SET LoadEndTime = EndTimeStamp;

Since the LoadEndTime column is always filled, there should be no extension of that column within the row when the update is executed. It should be an in place update. Still I get forwarded records always after that process… It doesn't make sense to me.

Insert Statement is like this:

INSERT INTO dbo.test (HashValue, LoadTime,LoadEndTime)
SELECT HASHBYTES(...), GETDATE(), '1900-01-01'

So there is already a dummy value. Even with compression the fixed and variable representation of datetime '1900-01-01 00:00:00' should be 8 bytes.

Best Answer

This is due to compression.

Compressed table has no more FixedVar row format, so even if you use fixed-length columns they aren't stored in Fixedvar way, no more fixed space reserved for (even NULL) values.

When you use page compression, frist ROW COMPRESSION is applied. And here what we have:Row Compression Implementation

It stores fixed character strings by using variable-length format by not storing the blank characters. Note

NULL and 0 values across all data types are optimized and take no bytes.

Here is my repro where I use dbo.Nums, table of 1000000 natural numbers, to fill my two tables: dbo.test_comp with page compression enabled and dbo.test_no_comp with no compression:

CREATE TABLE dbo.test_comp (
 id int,
 LoadTime DATETIME NOT NULL,
 LoadEndTime DATETIME NULL,
filler char(200) default 'qwertyuiopasdfghjklzxcvbnmnbvcxzlkjhgfdsapoiuytrewyyyyyyyykshdgfgsklghkfdjglfdvlaepeoèrehjblgjbltdjgljreglrelgretgregrtegregreqw'
) 
WITH(DATA_COMPRESSION=PAGE);

insert into dbo.test_comp(id,  LoadTime)
select n, getdate()
from dbo.nums;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_comp 0   11,7117117117117    18519

UPDATE dbo.test_comp SET LoadEndTime = LoadTime;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_comp 54949   11,1783696529459    19688


CREATE TABLE dbo.test_no_comp (
 id int,
 LoadTime DATETIME NOT NULL,
 LoadEndTime DATETIME NULL,
filler char(200) default 'qwertyuiopasdfghjklzxcvbnmnbvcxzlkjhgfdsapoiuytrewyyyyyyyykshdgfgsklghkfdjglfdvlaepeoèrehjblgjbltdjgljreglrelgretgregrtegregreqw'
) 

insert into dbo.test_no_comp(id,  LoadTime)
select n, getdate()
from dbo.nums;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_no_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_no_comp  0   6,22905027932961    28572

UPDATE dbo.test_no_comp SET LoadEndTime = LoadTime;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_no_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_no_comp  0   6,22905027932961    28572

UPDATE

Even with compression the fixed and variable representation of datetime '1900-01-01 00:00:00' should be 8 bytes

This is completely untrue, because you use PAGE compression that creates a dictionary and substitute repeating values:

Dictionary compression

After prefix compression is applied to every column individually, the second phase of page compression looks at all values on the page to find duplicates in any column of any row, even if they have been encoded to reflect prefix usage. The process of detecting duplicate values is data type–agnostic, so values in completely different columns could be the same in their binary representation. For example, a 1-byte character is represented in hex as 0x54, and it would be seen as a duplicate of the 1-byte integer 84, which is also represented in hex as 0x54. The dictionary is stored as a set of symbols, each of which corresponds to a duplicated value on the data page. After the symbols and data values are determined, each occurrence of one of the duplicated values is replaced by the symbol. SQL Server recognizes that the value actually stored in the column is a symbol and not a data value by examining the encoding in the CD array. Values which have been replaced by symbols have a CD array value of 0xc.

But I wanted point out another thing. Did you test if your PAGE compression make sense at all in your case? You have a heap and it's not static, i.e. you update it.

Compression of heap pages

Pages in a heap are checked for possible compression only during rebuild and shrink operations. Also, if you drop a clustered index on a table so that it becomes a heap, SQL Server runs compression analysis on any full pages. To make sure that the RowID values stay the same, heaps aren’t recompressed during typical data modification operations. Although the Page-ModCount value is maintained, SQL Server never tries to recompress a page based on the PageMod-Count value

So in your case even if you can achieve compression on INSERT (only if you use bulk insert with tablock), any update will only destroy your compression.

Look at my new test.

I realized that in my first test when i did INSERT without tablock, only row compression was applied, I inserted 1000000 rows and I get 19688 pages. Now I insert with tablock into the same table:

CREATE TABLE dbo.test_comp (
 id int,
 LoadTime DATETIME NOT NULL,
 LoadEndTime DATETIME not NULL,
filler char(200) default 'qwertyuiopasdfghjklzxcvbnmnbvcxzlkjhgfdsapoiuytrewyyyyyyyykshdgfgsklghkfdjglfdvlaepeoèrehjblgjbltdjgljreglrelgretgregrtegregreqw'
) 
WITH(DATA_COMPRESSION=PAGE);

insert into dbo.test_comp with(tablock) (id,  LoadTime, LoadEndTime)
select n, getdate(), getdate()
from dbo.nums;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_comp 0   0   1387

Now I have only 1387 pages. 1387 vs 19688 only because in first case page compression, even if defined, was not applied since my INSERT was without tablock

And now I update my nice compressed table:

UPDATE dbo.test_comp SET LoadEndTime = getdate();

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_comp 990247  8,43971631205674    22442

Wow, after update I have 22422 pages vs 1387 original pages!

After this test I really NEVER will use page compression on a heap that is not static. The final table size is not much less compared with its non-compressed analog but as a bonus I have 990247 forwarded records out of 1000000

Related Question