主页 / dba / 问题 / 187122
Accepted
tuxmania
Asked: 2017-09-28 22:58:06 +0800 CST

就地更新导致转发记录

  • 772

我很清楚什么是堆中的转发记录。因为我想将转发记录保持为 0，所以我们决定只更新无法扩展的列。

最近在我的系统上遇到转发记录。
表格设计是这样的：

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

插入语句总是带HashValueAND LoadTime。我检查了查询日志。
我插入值“9999-12-31”。

现在系统执行这样的更新LoadTime：

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

由于该LoadEndTime列始终被填充，因此在执行更新时，该列在行内不应有任何扩展。它应该是一个就地更新。在那个过程之后我仍然总是收到转发的记录......这对我来说没有意义。

插入语句是这样的：

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

所以已经有一个虚拟值。即使压缩，日期时间 '1900-01-01 00:00:00' 的固定和可变表示也应该是 8 个字节。

sql-server update

2 个回答

  1. Best Answer

    这是由于压缩。

    压缩表没有更多的FixedVar行格式，因此即使您使用固定长度的列，它们也不会以 Fixedvar 方式存储，也不会为（偶数NULL）值保留更多的固定空间。

    当您使用页面压缩时，首先应用 ROW COMPRESSION。这就是我们所拥有的：行压缩实现

    它通过不存储空白字符而使用可变长度格式存储固定字符串。笔记

    所有数据类型的 NULL 和 0 值都经过优化并且不占用字节。

    这是我的重现，我使用 dbo.Nums，1000000 个自然数的表来填充我的两个表：dbo.test_comp启用页面压缩和dbo.test_no_comp不压缩：

    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

    更新

    即使压缩，日期时间 '1900-01-01 00:00:00' 的固定和可变表示形式也应为 8 个字节

    这是完全不正确的，因为您使用PAGE创建字典并替换重复值的压缩：

    字典压缩

    将前缀压缩分别应用于每一列后，页面压缩的第二阶段会查看页面上的所有值，以查找任何行的任何列中的重复值，即使它们已被编码以反映前缀使用情况。检测重复值的过程与数据类型无关，因此完全不同列中的值在其二进制表示中可能相同。例如，一个 1 字节的字符以十六进制表示为 0x54，它会被视为 1 字节整数 84 的副本，后者也以十六进制表示为 0x54。字典存储为一组符号，每个符号对应于数据页上的一个重复值。在确定符号和数据值之后，重复值之一的每次出现都被符号替换。SQL Server 通过检查 CD 数组中的编码识别出实际存储在列中的值是一个符号而不是数据值。已被符号替换的值的 CD 数组值为 0xc。

    但我想指出另一件事。您是否测试过您的 PAGE 压缩对您的情况是否有意义？你有一个堆，它不是静态的，即你更新它。

    堆页面的压缩

    堆中的页面仅在重建和收缩操作期间检查可能的压缩。此外，如果您在表上删除聚簇索引以使其成为堆，则 SQL Server 将对所有完整页面运行压缩分析。为了确保 RowID 值保持不变，在典型的数据修改操作期间不会重新压缩堆。尽管保留了 Page-ModCount 值，但 SQL Server 从不尝试根据 PageMod-Count 值重新压缩页面

    因此，在您的情况下，即使您可以在 INSERT 上实现压缩（仅当您将批量插入与 一起使用时tablock），任何更新都只会破坏您的压缩。

    看看我的新测试。

    我意识到在我的第一个测试中，当我在没有 tablock 的情况下执行 INSERT 时，只应用了行压缩，我插入了 1000000 行，我得到了 19688 页。现在我将 tablock 插入到同一个表中：

    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

    现在我只有 1387 页。1387 vs 19688 只是因为在第一种情况下页面压缩，即使定义了，也没有应用，因为我的 INSERT 没有tablock

    现在我更新了我漂亮的压缩表：

    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

    哇，更新后我有 22422 页 vs 1387 个原始页面！

    在这次测试之后，我真的永远不会在非静态堆上使用页面压缩。与未压缩的模拟表相比，最终表的大小并没有减少多少，但作为奖励，我在 1000000 条记录中转发了 990247 条记录

    • 5

  2. 社区维基答案：

    堆和压缩值得注意：

    来自产品文档中的数据压缩：

    当堆配置为页面级压缩时，页面仅通过以下方式接收页面级压缩：

    • 在启用批量优化的情况下批量导入数据。
    • 数据是使用INSERT INTO ... WITH (TABLOCK)语法插入的，并且该表没有非聚集索引。
    • 通过执行带有压缩选项的ALTER TABLE ... REBUILD语句来重建表。PAGE

    因此，您UPDATE无法执行就地更新，后果已说明。

    作为解决方法，如果您使用基于日期的分区，则可以不压缩最近的分区。Clustered columnstore 表也做类似的事情，初始插入到行存储，然后被压缩到 Column 段中。

    • 1

相关问题