主页 / dba / 问题 / 289288
Accepted
Mario
Asked: 2021-04-06 06:03:27 +0800 CST

非聚集索引中的列占用的空间

  • 772

我计划在具有数百万行的大表中的现有非聚集索引之一中添加键列索引，而不是创建新的非聚集索引。我有什么方法可以通过在现有的非聚集索引中添加一个新的键列来知道索引消耗了多少额外空间，并且是否有任何数据/索引比率最佳实践，因为我知道最好不要有更多每张表超过 5 个索引。

为了清楚起见，我需要向存储团队解释添加新列后索引消耗的额外空间，然后只有我可以将该列添加到现有索引中。

sql-server nonclustered-index

3 个回答

  1. 这有什么办法让我知道通过添加一个新的键列索引消耗了多少额外空间

    创建一个新索引并检查其大小：

    select schema_name(t.schema_id) schema_name,
       t.name table_name, 
       i.name index_name, 
       ps.used_page_count / 8. / 1024 size_mb,
       row_count,
       row_count / nullif(ps.used_page_count,0) rows_per_page
from sys.tables t
join sys.indexes i
  on t.object_id = i.object_id
join sys.dm_db_partition_stats ps
  on i.object_id = ps.object_id
 and i.index_id = ps.index_id
 order by table_name, index_name

    是否有任何数据/索引比率最佳实践，因为我知道每个表最好不要有超过 5 个索引

    从候选键和外键的索引开始，然后根据需要添加其他索引以提高查询性能，同时监控丢失的索引和未使用的索引。

    • 2
  2. Best Answer

    这取决于您要添加的列中值的大小。正如 David 所建议的那样，最准确的了解方法是在开发或测试环境中创建一个索引，看看它在那里有什么影响。

    不过你可以估计。如果添加的列是 8 字节长（datetime例如一列）并且有 100M 行，那么您可以期望它向索引的叶页添加大约 800,000,000 字节。如果它是可变宽度列，那么您需要根据可能的数据长度进行估计，或者如果您可以对生产数据库运行查询，您可以使用SELECT SUM(DATALENGTH(ColumnBeingAddedToIndex)) FROM TheTable.

    这仅考虑了添加到索引中叶页的额外数据，但作为估计，它应该足够准确，因为这将是迄今为止最大的因素。非叶子页面也会占用一些额外的空间。

    如果为您的索引启用了压缩，上述内容也没有考虑到压缩。压缩数据可能更难以建模，因此在这种情况下，您可以通过在真实数据上创建索引来重新进行测试，这是唯一真正准确的方法。

    如果不知道您的表/索引定义，就不可能给出更准确的答案。

    • 2

  3. 如果您无法在较低的环境中创建新索引或更改索引，那么您可以尝试获取一个有代表性的样本，例如 100,000 个并将它们转储到一个新表中，然后在这个较小的样本上创建您的索引。如果您主要关心的是空间，那么请使用您觉得合适的最小代表性样本。请记住：样本越小，您的最终估计就越差。

    一旦您在代表表中创建了索引的大小，那么您可以通过乘以实际表中的行数并除以代表样本表中的行数来估计实际表的大小。

    但同样，在较低环境中的全尺寸表上创建索引将是最佳解决方案。请记住，当更新、插入和删除发生时，随着时间的推移索引变得碎片化，所需的空间也会发生变化。

    • 0

相关问题