主页 / dba / 问题 / 189484
Accepted
Tom V
Asked: 2017-10-28 02:08:15 +0800 CST

查找数据库中所有表的未压缩大小

  • 772

在 Dynamics AX 中有一种缓存机制，可以将表配置为加载到内存中并进行缓存。此缓存限制为一定数量的 KB 以防止内存问题。我正在谈论的设置被调用entiretablecache，并在请求单个记录后立即将整个表加载到内存中。

直到最近，我们依靠一些脚本来验证具有此设置的表的大小，以查看表大小是否超过此限制。

然而现在，压缩开始发挥作用，像sp_spaceused或sys.allocation_units这样的东西似乎报告了压缩数据实际使用的空间。

显然，应用程序服务器正在处理未压​​缩的数据，因此 SQL Server 中磁盘上的数据大小无关紧要。我需要未压缩数据的实际大小。

我知道sp_estimate_data_compression_savings但顾名思义，这只是一个估计值。
我希望尺寸尽可能正确。

我能想到的唯一方法是一些复杂的动态 SQL 创建与压缩表具有相同结构的未压缩表，将压缩数据插入该影子表中，然后检查该影子表的大小。
不用说，这有点乏味，并且需要一段时间才能在数百 GB 的数据库上运行。

Powershell 可能是一个选项，但我不想遍历所有表以select *对它们执行 a 以检查脚本中的大小，因为这只会淹没缓存并且可能也需要很长时间。

简而言之，如果可能的话，我需要一种方法来获取每个表的大小，因为它一旦被解压缩并且在呈现给应用程序的等式中会出现碎片。我对不同的方法持开放态度，首选 T-SQL，但我不反对 Powershell 或其他创造性方法。

假设应用程序中的缓冲区是数据的大小。bigint 始终是 bigint 的大小，字符数据类型是每个字符 2 个字节（unicode）。BLOB 数据也采用数据的大小，枚举基本上是一个 int，数字数据是 numeric(38,12)，datetime 是 datetime 的大小。此外，没有NULL值，它们要么存储为空字符串，要么存储1900-01-01为零。

没有关于如何实现的文档，但假设是基于一些测试以及 PFE 和支持团队使用的脚本（显然也忽略了压缩，因为检查是内置在应用程序中的，应用程序无法分辨如果基础数据被压缩），它还会检查表大小。例如，此链接指出：

避免对大型表使用 EntireTable 缓存（在 AX 2009 中超过 128 KB 或 16 页，在 AX 2012 中超过“整个表缓存大小”应用程序设置 [默认值：32KB 或 4 页]）——改为使用记录缓存。

sql-server cache

2 个回答

  1. Best Answer

    我需要未压缩数据的实际大小。
    ...
    我希望尺寸尽可能正确。

    虽然对这些信息的渴望当然是可以理解的，但由于错误的假设，获取这些信息，尤其是在“尽可能正确”的情况下，比每个人预期的要棘手。无论是做问题中提到的未压缩影子表的想法，还是@sp_BlitzErik 在评论中关于恢复数据库并解压缩检查的建议，都不应假设未压缩表的大小==内存中所述数据的大小在应用服务器上：

    1. 表中的所有行都被缓存了吗？还是只是在一个范围内？这里的假设是一切，这可能是正确的，但我认为至少应该提到这可能不是这种情况（除非文档另有说明，但无论如何这是一个小问题，只是不想它不被提及）。

      问题已更新为状态：是的，所有行都被缓存。

    2. 结构开销

      1. 在数据库方面：数据库方面的
        页面和行开销：页面上适合多少行取决于许多可能会导致估算的因素。即使 aFILLFACTOR为 100（或 0），页面上仍有可能剩余一些未使用的空间，因为它不足以容纳整行。这是对页眉的补充。此外，如果启用了任何快照隔离功能，我相信版本号会占用每行额外的 13 个字节，这将导致估计值下降。还有其他与行的实际大小相关的细节（NULL 位图、可变长度列等），但到目前为止提到的项目应该单独说明这一点。
      2. 在应用服务器端：
        使用什么类型的集合来存储缓存的结果？我假设这是一个 .NET 应用程序，那么它是一个 .NET 应用程序DataTable吗？通用列表？排序字典？每种类型的集合都有不同数量的偷听。我不希望任何选项必然反映数据库端的 Page 和 Row 开销，特别是在规模上（我确信少量的行可能没有足够的变化，但你不是在寻找差异以数百字节或仅几 kB 为单位）。
    3. 数据类型
      1. 在 DB 端：
        CHAR/VARCHAR数据以每个字符 1 个字节存储（暂时忽略双字节字符）。XML被优化为不会占用几乎与文本表示所暗示的一样多的空间。此数据类型创建一个元素和属性名称的字典，并用它们各自的 ID 替换文档中对它们的实际引用（实际上还不错）。否则，字符串值都是 UTF-16（每个“字符”2 或 4 个字节），就像NCHAR/一样NVARCHAR。DATETIME2介于 6 到 8 个字节之间。DECIMAL介于 5 到 17 个字节之间（取决于精度）。
      2. 在应用服务器端：
        字符串（再次假设 .NET）始终为 UTF-16。没有对 8 位字符串进行优化，例如什么是有效VARCHAR的。但是，字符串也可以是“interned”，这是一个可以多次引用的共享副本（但我不知道这是否适用于集合中的字符串，或者如果是，是否适用于所有类型的集合）。XML可能会或可能不会以相同的方式存储在内存中（我将不得不查找）。DateTime总是 8 个字节（像 T-SQL DATETIME，但不像DATE, TIME, or DATETIME2）。Decimal始终为16 个字节。

    综上所述：在数据库端几乎没有什么可以在应用服务器端获得相当准确的内存占用大小。在加载特定表后，您需要找到一种方法来询问应用程序服务器本身，因此要知道它有多大。而且我不确定调试器是否会让您看到已填充集合的运行时大小。如果不是，那么接近的唯一方法是遍历表的所有行，将每列乘以适当的.NET大小（例如INT= * 4、VARCHAR= DATALENGTH() * 2、NVARCHAR= DATALENGTH()、XML= ? 等），但这仍然留下了问题集合的开销加上集合的每个元素。

    给定问题中的一些新定义，人们可能会执行以下查询以获得相当接近的结果。表是否被压缩并不重要，尽管由每个人来确定在生产环境中扫描所有行是否合适（可能从恢复或非高峰时间进行）：

    SELECT
   SUM( DATALENGTH([NVarcharColumn_1]) + DATALENGTH([NVarcharColumn_N]) ) + 
   SUM( (DATALENGTH([VarcharColumn_1]) + DATALENGTH([VarcharColumn_N])) * 2 ) + 
   SUM(4 * [number_of_INT_columns]) +
   SUM(8 * [number_of_BIGINT_and_DATETIME_columns]) +
   SUM(16 * [number_of_DECIMAL/NUMERIC_and_UNIQUEIDENTIFIER_columns]) +
   etc..
FROM [SchemaName].[TableName] WITH (NOLOCK) -- assuming no Snapshot Isolation

    但请记住，这并没有考虑到集合或集合元素的开销。并且不确定我们是否可以在没有调试器的情况下获得该值（或者可能像 ILSpy 之类的东西，但我不建议这样做，因为它可能违反 EULA，具体取决于当地法律）。

    • 7

  2. 从您的问题来看，您似乎有一个最大的缓存大小S，并且您不想将超过该大小的表加载到缓存中。如果这是真的，那么您不需要知道每个表的确切大小。您只需要知道一个表是大于还是小于最大缓存大小S。根据表的列定义和行数，这是一个容易得多的问题。

    我同意 Solomon Rutzky 的出色回答，因为查看未压缩数据不是可行的方法，而且可能很难为缓存中表的真实大小得出一个很好的近似值。但是，我将在问题的框架内工作，并假设您可以根据静态数据类型的列定义和动态列的实际长度开发一个足够接近的公式。

    如果您将数据类型映射到缓存大小，那么您应该能够评估某些表，甚至无需查看其中的数据：

    1. 如果表只有静态数据类型（没有字符串或 blob），那么您可以通过查看sys.partitions并使用列定义计算表的大小来近似行数。
    2. 如果一个有很多行的表有足够多的静态数据类型列，那么您可以在不查看其数据的情况下将其消除为太大。例如，具有 1000 万行和 5BIGINT列的表的数据大小可能为 10000000 * (8+8+8+8+8) = 400 M 字节，这可能大于您的缓存大小限制S。它是否也有一堆字符串列也没关系。
    3. 如果一个只有几行的表足够小，那么您可以简单地通过假设每个动态数据类型具有最大可能大小来确认它低于限制。例如，包含一BIGINT列和一NVARCHAR(20)列的 100 行表可能不超过 100 * (8 + 2 * 20) = 4800 字节。
    4. 如果一个表在 SQL Server 中的压缩大小比它大一些，S那么它极不可能放入缓存中，这可能是真的。您必须进行测试以确定是否存在这样的值。
    5. 您可能会很幸运，因为所有动态列都恰好有关于它们的统计信息。统计信息包含有关平均长度的信息，这些信息可能对您的目的来说足够准确。

    您可能需要查询不符合上述任何条件的表的数据。您可以使用一些技巧来最大程度地减少对性能的影响。我想说您在这里有两个相互竞争的优先级：您重视准确性，但也不想扫描数据库中的所有数据。可以在计算中添加某种缓冲区。我不知道排除略低于最大缓存大小的S表或包含略高于最大缓存大小的表是否更可接受。

    以下是使查询表数据更快的一些想法：

    1. TABLESAMPLE对于大表，只要您的样本量足够大，您就可以使用。
    2. 对于具有聚集键的大型表，在聚集键上批量处理它们可能很有用。不幸的是，我不知道有一种方法可以SUM()根据该聚合的值来计算提前退出的 a 。我只见过这样的工作ROW_NUMBER()。但是您可以扫描表的前 10%，保存计算的数据大小，然后扫描接下来的 10%，依此类推。对于对于缓存来说太大的表，您可以通过提早退出使用这种方法来节省大量工作。
    3. 对于某些表，您可能很幸运能够覆盖所有动态列上的索引。根据行大小或其他因素，一次扫描每个索引可能比执行表扫描更快。如果在读取单个列上的索引后表大小太大，您也可以提前退出此过程。
    4. 您的动态列的平均长度可能不会随着时间的推移而发生很大变化。保存您计算的平均长度并在计算中使用这些值一段时间可能是实用的。您可以根据表中的 DML 活动或其他一些指标来重置这些值。
    5. 如果可以对所有表运行测试以开发算法，那么您可以利用数据中的模式。例如，如果您首先从最小的开始处理表，您可能会发现，一旦您连续处理 10 个（我计算了这个数字）表，这些表对于缓存来说太大了，那么任何更大的表都不太可能适合缓存。缓存。如果可以排除一些可能适合缓存的表，这可能是可以接受的。

    我意识到我没有在这个答案中包含任何 SQL 代码。让我知道为我在这里讨论的任何想法编写演示代码是否会有所帮助。

    • 6

相关问题