Server表的空间计算:我的查询有什么问题？

Question

我使用以下查询来计算Server数据库中特定表上的一些空间相关度量：

SELECT 
    t.name AS TableName, 
    p.rows,
    (sum(a.total_pages) * 8) as reserved,
    (sum(a.data_pages) * 8) as data,
    N'Not Needed' as index_size,
    (sum(a.total_pages) * 8) -  (sum(a.used_pages) * 8) as unused
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 = N'XYZ' 
GROUP BY t.name, p.rows
ORDER BY 
   1 Desc;

它给出了计算行数、保留空间和未使用空间的正确结果。但是，当我将其输出与运行sp_spaceused存储过程的输出进行比较时，我观察到数据所使用的空间的不同值：

我怎么才能修好它？

Answer 1

sys.allocation_units的文档似乎是不正确的。它指出data_pages是使用过的页面数，它应该包括LOB页面。

LOB页面是数据类型的页面，例如(MAX)类型、XML、地理空间和列存储数据。

但是，在我的机器上(SQLserver2019)，我看到了LOB数据的0个数据页。如果移除组并查看各个行，您将看到我的意思。因此，您必须将它是否为LOB数据作为大小写，并在这种情况下使用其他列。

我在AdventureworksDW2016数据库中使用了AdventureworksDW2016进行测试。适应你的喜好。我将架构名称和表参数化。请参见生成的列名数据(原始计算)和data_corrected (我的调整计算)之间的差异：

DECLARE @s sysname, @t sysname, @f nvarchar(256)
SET @s = 'dbo'
SET @t = 'FactResellerSalesXL_CCI'
SET @f = QUOTENAME(@s) + '.' + QUOTENAME(@t)

SELECT 
    t.name AS TableName, 
    a.type_desc,
    i.index_id,
    p.rows,
    a.total_pages * 8 as reserved,
    a.data_pages * 8 as data,
    (CASE WHEN a.type_desc = 'LOB_DATA' THEN a.used_pages ELSE a.data_pages END) * 8 as data_corrected,
    N'Not Needed' as index_size,
    a.total_pages * 8 -  a.used_pages * 8 as unused
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 = @t
ORDER BY 
   1 Desc;

我让你们把聚合带回来。:-)