sql - 谁能解释一下 SQL Server 2005 表的大小

Question

我正在使用 SQL Server 2005 并且有一个表:

int Code1,
int Code2, 
real Val1,
real Val2,
real Val3,

Code1 和 Code2 作为主键，并且是聚集索引的一部分(只有一个索引)。每个参数占用4个字节(每行占用20个字节)。

表中有2450万条记录，填充因子为100%，索引占用2MB，页大小为4k。

假设每页填充尽可能多的记录，则每页应包含 204 条记录，即 4080 字节(%99.6 页填充)

因此，我预计该表在磁盘上占用的大小约为 500MB(20 字节 * 24.5 M 记录)，但事实是该表占用了 773MB。

我尝试了收缩和重新索引，但表大小没有改变。

我不是 SQL 专家，有人可以帮忙吗？

Answer 1

首先，SQL Server中的页面大小是8 KB，并且无法更改；这是您无法控制的系统设置。

在这 8192 个字节中，作为用户，您大约可以使用 8060 个字节 - 其余的是 header 和控制结构等。

因此，在您的情况下，每行占用 20 个字节，您应该能够每页获得 403 行。这样您就可以得到大约 60,795 个数据页，每页 8 KB = 486 MB。

但是:出于性能原因，SQL Server 不会根据需要分配每个页面 - SQL Server 将为数据库预先分配给定的大小。当您在 SQL Server Management Studio 中创建新数据库时，您将看到默认情况下 SQL Server 分配 3 MB 空间，并且当需要更多空间时会增加 1 MB。这些设置是可以更改的 - 您没有提及它们是什么。

此外，出于性能原因，SQL Server 通常不会将未使用的数据页“返回”到操作系统。这是一项相当昂贵的操作，并且很有可能在某个时候再次需要这些操作。索引页也是如此 - 如果您可能在该表上有另一个索引(即使只是为了尝试一些东西)并且它使用了许多页面，那么默认情况下这些页面不会返回到操作系统。

此外，根据数据插入表的方式，数据结构中可能存在一些“漏洞” - 并非所有页面都可能完全达到 100% 填充。为了保持 B 树的平衡，SQL Server 甚至可能选择将页面分成两部分，即使它们还没有 100% 满。

总而言之:是的，从理论上和数学上讲，您的数据库应该大约有 486 MB 的数据和 2 MB 的索引 - 但如果文件大小超过 770 MB，那真的有多糟糕？真的很痛吗？？

<小时/>

通过这个检查 DMV(动态管理 View )的 T-SQL 脚本，您可以非常深入、详细地了解表索引结构、每个索引级别使用了多少页，以及如何数据页面上的填充因子 - 非常有用且有助于了解!

SELECT 
    t.NAME 'Table name',
    i.NAME 'Index name',
    ips.index_type_desc,
    ips.alloc_unit_type_desc,
    ips.index_depth,
    ips.index_level,
    ips.avg_fragmentation_in_percent,
    ips.fragment_count,
    ips.avg_fragment_size_in_pages,
    ips.page_count,
    ips.avg_page_space_used_in_percent,
    ips.record_count,
    ips.ghost_record_count,
    ips.Version_ghost_record_count,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.avg_record_size_in_bytes,
    ips.forwarded_record_count
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN  
    sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN  
    sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
    T.NAME = 'your-table-name-here'
ORDER BY
    AVG_FRAGMENTATION_IN_PERCENT, fragment_count