主页 / dba / 问题 / 127405
Accepted
Chris Woods
Asked: 2016-01-27 13:08:10 +0800 CST

来自 sys.allocation_units 的 DATALENGTH 的总和与表大小不匹配

  • 772

我的印象是，如果我DATALENGTH()将表中所有记录的所有字段相加，我将得到表的总大小。我弄错了吗？

SELECT 
SUM(DATALENGTH(Field1)) + 
SUM(DATALENGTH(Field2)) + 
SUM(DATALENGTH(Field3)) TotalSizeInBytes
FROM SomeTable
WHERE X, Y, and Z are true

我在下面使用了这个查询（我从网上获取表大小，仅聚集索引，因此它不包括 NC 索引）来获取我的数据库中特定表的大小。出于计费目的（我们按部门使用的空间量向部门收费），我需要计算出此表中每个部门使用了多少空间。我有一个查询来标识表中的每个组。我只需要弄清楚每个组占用了多少空间。

由于表中的字段，每行的空间可能会大幅波动VARCHAR(MAX)，所以我不能只取平均大小 * 部门的行数。当我使用上述DATALENGTH()方法时，我只得到下面查询中使用的总空间的 85%。想法？

SELECT 
s.Name AS SchemaName,
t.NAME AS TableName,
p.rows AS RowCounts,
(SUM(a.total_pages) * 8)/1024 AS TotalSpaceMB, 
(SUM(a.used_pages) * 8)/1024 AS UsedSpaceMB, 
((SUM(a.total_pages) - SUM(a.used_pages)) * 8)/1024 AS UnusedSpaceMB
FROM 
    sys.tables t with (nolock)
INNER JOIN 
    sys.schemas s with (nolock) ON s.schema_id = t.schema_id
INNER JOIN      
    sys.indexes i with (nolock) ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p with (nolock) ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a with (nolock) ON p.partition_id = a.container_id
WHERE 
    t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
    AND i.type_desc = 'Clustered'
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB desc

有人建议我为每个部门创建一个过滤索引或对表进行分区，这样我就可以直接查询每个索引使用的空间。可以通过编程方式创建过滤索引（并在维护窗口期间或当我需要执行定期计费时再次删除），而不是一直使用空间（在这方面分区会更好）。

我喜欢这个建议，并且通常会这样做。但老实说，我以“每个部门”为例来解释我为什么需要这个，但老实说，这并不是真正的原因。由于保密原因，我无法解释我需要这些数据的确切原因，但它类似于不同的部门。

关于这张表上的非聚集索引：如果我能得到 NC 索引的大小，那就太好了。但是，NC 索引占聚集索引大小的比例不到 1%，因此我们可以不包括这些索引。但是，无论如何我们将如何包含 NC 索引？我什至无法获得聚集索引的准确大小:)

sql-server clustered-index

2 个回答

  1. Best Answer

                              Please note that the following info is not intended to be a comprehensive
    description of how data pages are laid out, such that one can calculate
    the number of bytes used per any set of rows, as that is very complicated.

    数据并不是唯一占用 8k 数据页空间的东西：

    • 有预留空间。您只能使用 8192 个字节中的 8060 个（这 132 个字节一开始就不是您的）：

      • 页头：正好是 96 个字节。
      • 插槽数组：这是每行 2 个字节，表示每行在页面上的起始位置的偏移量。此数组的大小不限于剩余的 36 个字节 (132 - 96 = 36)，否则您将被有效地限制为最多只能在数据页上放置 18 行。这意味着每一行比你想象的要大 2 个字节。此值不包含在 报告的“记录大小”中DBCC PAGE，这就是为什么它在此处单独保存，而不是包含在下面的每行信息中。
      • 每行元数据（包括但不限于）：
        • 大小取决于表定义（即列数、可变长度或固定长度等）。信息取自 @PaulWhite 和 @Aaron 的评论，可在与此答案和测试相关的讨论中找到。
        • Row-header：4个字节，其中2个表示记录类型，另外两个是NULL Bitmap的偏移量
        • 列数：2字节
        • NULL 位图：当前有哪些列NULL。每组 8 列 1 个字节。对于所有列，甚至是NOT NULL那些列。因此，最少 1 个字节。
        • 可变长度列偏移数组：最少 4 个字节。2 个字节来保存可变长度列的数量，然后每个可变长度列 2 个字节来保存它开始的偏移量。
        • ALLOW_SNAPSHOT_ISOLATION ON版本信息：14 字节（如果您的数据库设置为或，则会出现此信息READ_COMMITTED_SNAPSHOT ON）。
      • 有关此问题的更多详细信息，请参阅以下问题和答案：插槽阵列和总页面大小
      • 请参阅 Paul Randall 的以下博客文章，其中包含有关数据页面布局方式的一些有趣细节：Poking about with DBCC PAGE (Part 1 of ?)

    • 未存储在行中的数据的 LOB 指针。所以这将占DATALENGTH+pointer_size。但这些都不是标准尺寸。有关此复杂主题的详细信息，请参阅以下博客文章：(MAX) 类型（如 Varchar、Varbinary 等）的 LOB 指针的大小是多少？. 在链接的帖子和我已经完成的一些额外测试之间，（默认）规则应如下所示：

      • 从 SQL Server 2005 开始，任何人都不应再使用的旧版/已弃用 LOB 类型（TEXT、NTEXT和IMAGE）：
        • 默认情况下，始终将其数据存储在 LOB 页面上，并始终使用 16 字节指针指向 LOB 存储。
        • 如果 使用 sp_tableoption设置text in row选项，则：
          • 如果页面上有空间存储值，并且值不大于最大行内大小（可配置范围为 24 - 7000 字节，默认为 256），则将其存储在行内，
          • 否则它将是一个 16 字节的指针。
      • 对于 SQL Server 2005 中引入的较新 LOB 类型（VARCHAR(MAX)、NVARCHAR(MAX)和VARBINARY(MAX)）：
        • 默认：
          • 如果该值不大于 8000 字节，并且页面上有空间，则将其存储在行中。
          • Inline Root — 对于 8001 到 40,000（实际上是 42,000）字节之间的数据，如果空间允许，在 ROW 中将有 1 到 5 个指针（24 - 72 字节）直接指向 LOB 页。初始 8k LOB 页面为 24 字节，每个额外的 8k 页面为 12 字节，最多可再增加四个 8k 页面。
          • TEXT_TREE — 对于超过 42,000 字节的数据，或者如果 1 到 5 个指针不能容纳在行中，那么将只有一个 24 字节指针指向 LOB 页指针列表的起始页（即“text_tree “ 页）。
        • 如果 使用 sp_tableoption设置large value types out of row选项，则始终使用指向 LOB 存储的 16 字节指针。
      • 我说“默认”规则是因为我没有针对某些功能（例如数据压缩、列级加密、透明数据加密、始终加密等）的影响测试行内值。

    • LOB 溢出页：如果值为 10k，则需要 1 个完整的 8k 页溢出，然后是第 2 页的一部分。如果没有其他数据可以占用剩余空间（或者甚至被允许，我不确定该规则），那么您在第二个 LOB 溢出数据页上有大约 6kb 的“浪费”空间。

    • 未使用空间：一个 8k 的数据页就是：8192 字节。它的大小没有变化。然而，放置在上面的数据和元数据并不总是很好地适合所有 8192 字节。并且行不能拆分到多个数据页上。因此，如果您有 100 个字节剩余但没有行（或没有适合该位置的行，取决于几个因素）可以容纳在那里，数据页仍然占用 8192 个字节，并且您的第二个查询仅计算数据页。你可以在两个地方找到这个值（请记住，这个值的一部分是保留空间的一部分）：

      • DBCC PAGE( db_name, file_id, page_id ) WITH TABLERESULTS;寻找ParentObject= "PAGE HEADER:" 和Field= "m_freeCnt"。该Value字段是未使用的字节数。
      • SELECT buff.free_space_in_bytes FROM sys.dm_os_buffer_descriptors buff WHERE buff.[database_id] = DB_ID(N'db_name') AND buff.[page_id] = page_id;这与“m_freeCnt”报告的值相同。这比 DBCC 更容易，因为它可以获取许多页面，但也需要首先将页面读入缓冲池。

    • < 100保留的空间FILLFACTOR。新创建的页面不遵守FILLFACTOR设置，但执行 REBUILD 将在每个数据页面上保留该空间。保留空间背后的想法是，它将被非顺序插入和/或更新使用，这些插入和/或更新已经扩展了页面上的行大小，因为可变长度列被更新的数据略多（但不足以导致分页）。但是您可以轻松地在数据页上保留空间，这些空间自然不会获得新行，也不会更新现有行，或者至少不会以会增加行大小的方式进行更新。

    • Page-Splits（碎片化）：需要将行添加到没有空间容纳该行的位置会导致页面拆分。在这种情况下，大约 50% 的现有数据被移动到新页面，新行被添加到 2 个页面之一。但是您现在有更多的可用空间，DATALENGTH计算中没有考虑到这些空间。

    • 标记为删除的行。当您删除行时，它们并不总是立即从数据页中删除。如果不能立即删除它们，它们将被“标记为死亡”（Steven Segal 参考），稍后将通过幽灵清理过程物理删除（我相信这就是名称）。但是，这些可能与该特定问题无关。

    • 鬼页？不确定这是否是正确的术语，但有时数据页在聚集索引的重建完成之前不会被删除。这也DATALENGTH将占比加起来更多的页面。这通常不应该发生，但几年前我遇到过一次。

    • 稀疏列：稀疏列在大部分行NULL用于一列或多列的表中节省空间（主要用于固定长度数据类型）。该SPARSE选项使NULL值类型增加 0 个字节（而不是正常的固定长度量，例如 4 字节INT），但是，非 NULL 值每个固定长度类型占用额外的 4 个字节，可变数量变长类型。这里的问题是DATALENGTH不包括 SPARSE 列中非 NULL 值的额外 4 个字节，因此需要重新添加这 4 个字节。您可以SPARSE通过以下方式检查是否有任何列：

      SELECT OBJECT_SCHEMA_NAME(sc.[object_id]) AS [SchemaName],
       OBJECT_NAME(sc.[object_id]) AS [TableName],
       sc.name AS [ColumnName]
FROM   sys.columns sc
WHERE  sc.is_sparse = 1;

      然后对于每一SPARSE列，更新原始查询以使用：

      SUM(DATALENGTH(FieldN) + 4)

      请注意，上面添加标准 4 字节的计算有点简单，因为它仅适用于固定长度类型。并且，每行还有额外的元数据（据我所知），这减少了可用于数据的空间，只需至少有一个 SPARSE 列。有关更多详细信息，请参阅使用稀疏列的 MSDN 页面。

    • 索引和其他（例如 IAM、PFS、GAM、SGAM 等）页面：就用户数据而言，这些不是“数据”页面。这些将扩大表格的总大小。如果使用 SQL Server 2012 或更新版本，可以使用sys.dm_db_database_page_allocations动态管理功能 (DMF) 查看页面类型（SQL Server 早期版本可以使用DBCC IND(0, N'dbo.table_name', 0);）：

      SELECT *
FROM   sys.dm_db_database_page_allocations(
               DB_ID(),
               OBJECT_ID(N'dbo.table_name'),
               1,
               NULL,
               N'DETAILED'
              )
WHERE  page_type = 1; -- DATA_PAGE

      nor （带有那个 WHERE 子句）都DBCC IND不会sys.dm_db_database_page_allocations报告任何索引页面，只有DBCC IND会报告至少一个 IAM 页面。

    • DATA_COMPRESSION：如果您在聚集索引或堆上启用ROW或PAGE压缩，那么您可以忘记到目前为止提到的大部分内容。96 字节的页头、每行 2 字节的插槽阵列和每行 14 字节的版本信息仍然存在，但数据的物理表示变得非常复杂（比压缩时已经提到的要复杂得多）没有被使用）。例如，使用行压缩，SQL Server 尝试使用尽可能小的容器来适应每一列、每一行。所以如果你有一个BIGINT列，否则（假设SPARSE也没有启用）总是占用 8 个字节，如果值在 -128 和 127 之间（即有符号的 8 位整数），那么它将只使用 1 个字节，如果值可能适合SMALLINT, 它只占用 2 个字节。整数类型要么是要么NULL不0占用空间，并且在映射列的数组中简单地表示为是NULL或“空”（即）。0还有很多很多其他的规则。有 Unicode 数据（NCHAR, NVARCHAR(1 - 4000)，但没有 NVARCHAR(MAX)，即使存储在行中）？SQL Server 2008 R2 中添加了 Unicode 压缩，但鉴于规则的复杂性，如果不进行实际压缩，则无法预测所有情况下“压缩”值的结果。

    所以实际上，您的第二个查询虽然在磁盘上占用的总物理空间方面更准确，但只有在执行REBUILD聚集索引时才真正准确。在那之后，您仍然需要考虑任何FILLFACTOR低于 100 的设置。即便如此，总是有页眉，并且通常有足够多的“浪费”空间，由于太小而无法容纳其中的任何行，因此根本无法填充表，或者至少是逻辑上应该进入该槽的行。

    关于第二次查询在确定“数据使用”方面的准确性，退出页头字节似乎是最公平的，因为它们不是数据使用：它们是业务成本开销。如果数据页上有 1 行并且该行只是 a TINYINT，那么 1 字节仍然需要数据页存在，因此需要 96 个字节的标题。该 1 个部门是否应该为整个数据页收费？如果该数据页随后被部门#2 填满，他们会平均分配“间接”成本还是按比例支付？似乎最容易将其退出。在这种情况下，使用8乘以的值number of pages太高。怎么样：

    -- 8192 byte data page - 96 byte header = 8096 (approx) usable bytes.
SELECT 8060.0 / 1024 -- 7.906250

    因此，使用类似的东西：

    (SUM(a.total_pages) * 7.91) / 1024 AS [TotalSpaceMB]

    针对“number_of_pages”列的所有计算。

    AND，考虑到使用DATALENGTH每个字段不能返回每行元数据，应将其添加到每个表查询中，在其中获取DATALENGTH每个字段，过滤每个“部门”：

    • 记录类型和偏移到 NULL 位图：4 字节
    • 列数：2 个字节
    • Slot Array：2字节（不包括在“记录大小”中但仍需要考虑）
    • NULL 位图：每 8 列 1 个字节（对于所有列）
    • 行版本控制：14 字节（如果数据库有ALLOW_SNAPSHOT_ISOLATION或READ_COMMITTED_SNAPSHOT设置为ON）
    • 可变长度列偏移数组：如果所有列都是固定长度的，则为 0 字节。如果任何列是可变长度的，那么 2 个字节，加上每个可变长度列的 2 个字节。
    • LOB 指针：这部分非常不精确，因为如果值为 ，则不会有指针NULL，如果该值适合该行，则它可以比指针小得多或大得多，并且如果该值存储在外-行，那么指针的大小可能取决于有多少数据。但是，由于我们只需要一个估计值（即“swag”），因此 24 字节似乎是一个很好的使用值（嗯，和其他任何东西一样好;-)。这是每个MAX字段。

    因此，使用类似的东西：

    • 通常（行标题 + 列数 + 槽数组 + NULL 位图）：

      ([RowCount] * (( 4 + 2 + 2 + (1 + (({NumColumns} - 1) / 8) ))

    • 一般来说（自动检测是否存在“版本信息”）：

      + (SELECT CASE WHEN snapshot_isolation_state = 1 OR is_read_committed_snapshot_on = 1
                 THEN 14 ELSE 0 END FROM sys.databases WHERE [database_id] = DB_ID())

    • 如果有任何可变长度列，则添加：

      + 2 + (2 * {NumVariableLengthColumns})

    • 如果有任何MAX/ LOB 列，则添加：

      + (24 * {NumLobColumns})

    • 一般来说：

      )) AS [MetaDataBytes]

    这并不准确，如果您在堆或聚集索引上启用了行或页面压缩，这将再次不起作用，但绝对应该让您更接近。

    关于 15% 差异之谜的更新

    我们（包括我自己）非常专注于思考数据页面的布局以及如何DATALENGTH解释我们没有花很多时间查看第二个查询的事情。我针对单个表运行该查询，然后将这些值与报告的值进行比较sys.dm_db_database_page_allocations，它们与页数的值不同。凭直觉，我删除了聚合函数 和GROUP BY，并将SELECT列表替换为a.*, '---' AS [---], p.*。然后就很清楚了：人们必须小心在这些模糊的互联网上他们从哪里获取信息和脚本；-)。问题中发布的第二个查询并不完全正确，尤其是对于这个特定的问题。

    • 小问题：除此之外GROUP BY rows（并且在聚合函数中没有该列）没有多大意义，之间的 JOIN 在sys.allocation_units技术上sys.partitions并不正确。分配单元有 3 种类型，其中一种应该 JOIN 到不同的字段。很多时候partition_id都是hobt_id一样的，所以可能永远不会有问题，但有时这两个字段确实有不同的值。

    • 主要问题：查询使用该used_pages字段。该字段涵盖所有类型的页面：数据、索引、IAM 等。当只关注实际数据时，还有另一个更合适的字段：data_pages.

    我在考虑到上述项目的情况下调整了问题中的第二个查询，并使用了支持页面标题的数据页面大小。我还删除了两个不必要的 JOIN：（sys.schemas替换为对 的调用SCHEMA_NAME()）和sys.indexes（聚集索引始终是index_id = 1并且我们index_id在 中sys.partitions）。

    SELECT  SCHEMA_NAME(st.[schema_id]) AS [SchemaName],
        st.[name] AS [TableName],
        SUM(sp.[rows]) AS [RowCount],
        (SUM(sau.[total_pages]) * 8.0) / 1024 AS [TotalSpaceMB],
        (SUM(CASE sau.[type]
           WHEN 1 THEN sau.[data_pages]
           ELSE (sau.[used_pages] - 1) -- back out the IAM page
         END) * 7.91) / 1024 AS [TotalActualDataMB]
FROM        sys.tables st
INNER JOIN  sys.partitions sp
        ON  sp.[object_id] = st.[object_id]
INNER JOIN  sys.allocation_units sau
        ON  (   sau.[type] = 1
            AND sau.[container_id] = sp.[partition_id]) -- IN_ROW_DATA
        OR  (   sau.[type] = 2
            AND sau.[container_id] = sp.[hobt_id]) -- LOB_DATA
        OR  (   sau.[type] = 3
            AND sau.[container_id] = sp.[partition_id]) -- ROW_OVERFLOW_DATA
WHERE       st.is_ms_shipped = 0
--AND         sp.[object_id] = OBJECT_ID(N'dbo.table_name')
AND         sp.[index_id] < 2 -- 1 = Clustered Index; 0 = Heap
GROUP BY    SCHEMA_NAME(st.[schema_id]), st.[name]
ORDER BY    [TotalSpaceMB] DESC;
    • 19

  2. 也许这是一个垃圾答案，但这是我会做的。

    所以DATALENGTH只占总数的86%。还是很有代表性的分裂。来自 srutzky 的优秀答案的开销应该相当均匀。

    我将使用您的第二个查询（页面）作为总数。并使用第一个（数据长度）来分配拆分。许多成本是使用标准化分配的。

    而且你必须考虑一个更接近的答案会增加成本，所以即使是在拆分中失败的部门也可能会支付更多。

    • 6

相关问题