Azure SQL数据库单表大小远超预期问题排查咨询

可能的核心原因 #

• 索引填充因子配置不合理：如果主键或外键索引的填充因子设置值过低（比如低于70），即使是纯插入业务，每个数据页也会预留大量空白空间，直接导致存储占用成倍上涨
• 版本存储残留未回收：Azure SQL默认开启读提交快照隔离（RCSI），如果存在长时间未提交的事务，会导致该表关联的版本存储数据一直无法清理，额外占用大量存储空间
• 变长字段实际存储超限：虽然vc_field1定义为VARCHAR(100)，如果业务插入时填充了大量全长度字符、甚至不可见的冗余字符，会导致单条记录实际存储大小远高于预估
• 空白数据页未回收：如果该表为无聚集索引的堆表，即便当前没有删除操作，历史如果有过批量删除动作，会留下大量无法被自动复用的空白页，大幅拉高表的总占用
• 行溢出存储浪费：如果单条记录总长度超过8060字节的页上限，会触发行溢出存储，每个溢出页仅存储少量数据，会产生极高的空间浪费

排查操作步骤 #

• 首先查询表和索引的空间分配明细，确认空白空间占比：

SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.object_id = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.name = '替换为你的表名'
GROUP BY 
    t.name, i.name, p.rows
ORDER BY TotalSpaceKB DESC

如果UnusedSpaceKB占总空间比例超过90%，基本可以定位为空白页未回收问题。

• 检查索引填充因子配置：

SELECT name, fill_factor 
FROM sys.indexes 
WHERE object_id = OBJECT_ID('替换为你的表名')

纯插入业务的索引填充因子建议设置为100，如果当前值低于70就是不合理配置。

• 检查该表关联的版本存储占用：

SELECT 
    total_page_count * 8 AS VersionStoreKB
FROM sys.dm_tran_version_store_space_usage
WHERE database_id = DB_ID()
AND object_id = OBJECT_ID('替换为你的表名')

如果版本存储占用很高，排查并杀掉长时间运行的闲置事务后，版本存储会自动清理。

• 检查单条记录平均大小：

SELECT 
    avg_record_size_in_bytes,
    forwarded_record_count
FROM sys.dm_db_index_physical_stats(
    DB_ID(), 
    OBJECT_ID('替换为你的表名'), 
    NULL, 
    NULL, 
    'DETAILED'
)

如果单条平均大小远高于预估的几十字节，排查业务插入的数据是否有异常冗余内容。

• 确认无业务影响的前提下，可直接重建索引回收空间：

-- 有聚集索引的表执行
ALTER INDEX ALL ON 替换为你的表名 REBUILD WITH (FILLFACTOR = 100, ONLINE = ON)
-- 堆表执行
ALTER TABLE 替换为你的表名 REBUILD

执行后再次检查表大小，正常情况下会回落至你预估的合理区间。

内容的提问来源于stack exchange，提问作者Andrew