如何估计SQL Server索引大小 [英] How do I estimate SQL Server index sizes

问题描述

虽然估算直行和表格大小是相当简单的数学，但我们发现猜测每个索引将占用多少空间（对于给定的表格大小）具有挑战性。我们可以学习哪些方面来计算索引的更好估计和增长率？

While estimating straight row and table sizes are fairly simple math, we find it challenging to guess just how much space each index will occupy (for a given table size). What areas can we learn to calculate a better estimate and growth rate for indexes?

推荐答案

索引叶有一个标识数据的前导码page（7个字节加上可变长度列的一些目录信息，如果有的话）加上一个键值的副本，这些副本的大小与这些列的表数据相同。表中的每一行都有一个。索引的较高级别要小得多，通常不到叶子的1％，除非你索引一个非常宽的密钥。

An index leaf has a preamble identifying the data page (7 bytes plus some directory information for variable length columns, if any) plus a copy of the key value (s) which will be the same size as the table data for those columns. There's one for each row in the table. The higher up levels of the index are much smaller, usually less than 1% of the leaves unless you are indexing a very wide key.

填充因子留下一些空间这样更新和插入不会产生过多的叶子分裂流量。

The fill factor leaves some space free so that updates and inserts do not generate excessive leaf splitting traffic.

编辑：此MSDN链接描述了页面级结构，尽管它对单个索引行的格式有点了解。 此演示文稿在某种程度上进入磁盘日志条目和数据页的物理格式。 这一个更多细节，包括索引数据结构。数字和固定长度的列具有盒子上显示的大小;你必须估计varchar列的平均大小。

This MSDN link describes the page-level structures, although it's a bit light on the format of the individual index rows. This presentation goes into the physical format of disk log entries and data pages to some extent. This one more detail and includes the index data structures. Numeric and fixed length columns have the size it says on the box; you would have to estimate the average size of varchar columns.

作为参考，可以找到有关Oracle块格式的一些文档这里和这里。

For reference, some documents on Oracle's block format can be found Here and Here.

这篇关于如何估计SQL Server索引大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！