logo标签列表

有人能解释一下SQL Server 2005表的大小吗？

sqlsql-serversql-server-2005
4

我正在使用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字节* 2450万条记录），但事实是表占用了773MB。
我尝试了收缩和重建索引，但表格大小没有改变。
我不是SQL专家，有人可以帮忙吗？
5个回答
7
首先，在SQL Server中，页面大小为8 KB，无法更改；这是系统设置，您无法控制。
在这8192字节中，用户大约可以使用8060个字节 - 其余的是头文件、控制结构等。
因此，在您的情况下，每行占用20字节，您应该能够获得每页403行。所以，您大约有60,795个数据页，每个页面为8 KB，则为486 MB。
然而，出于性能原因，SQL Server不会在需要时分配每个页面 - SQL Server将为数据库预先分配给定的大小。当您在SQL Server管理工具中创建新数据库时，默认情况下，SQL Server会分配3 MB的空间，并在需要更多空间时增加1 MB。这些设置是可更改的 - 您没有提到它们是什么。
同样，出于性能原因，SQL Server通常不会“返回”未使用的数据页回操作系统。那是一个相当昂贵的操作，很可能再次需要它们。相同的情况也适用于索引页 - 如果您可能在该表上拥有另一个索引（即使只是为了尝试一些东西），并且它使用了若干页面，则默认情况下不会将它们返回到操作系统。
此外，根据数据插入到表中的方式，数据结构中可能存在一些“空隙” - 并非所有页面都可能完全填满。为了保持平衡的B树，即使它们尚未完全填满，SQL Server甚至可能选择将页面分成两个。
总之：理论上和数学上，您的数据库应该大约为486 MB的数据和2 MB的索引 - 但如果文件大小为770 MB以上，情况真的有多糟糕吗？这真的会伤害吗？
使用此T-SQL脚本检查DMV（动态管理视图），您可以深入详细地了解表索引结构，每个索引级别上使用了多少页，以及数据页中的填充因子 - 这非常有用和有帮助！
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
4

我将尝试估计您的表格大小，注意我使用90%的填充规则作为经验法则。

Row header                   4  bytes
Fixed data size             20  bytes (2 X 4 bytes for int + 3 x 4 bytes for real)
Variable size columns count  2  bytes
NULL bitmap columns count    2  bytes
Total for one row           28  bytes
Available page size       8060  bytes
Page header                 96  bytes
Rows per page (max)        284  (Available page size - Page Header) / Total for one row
Rule of thumb page fill     90% 
Rows per page (expected)   255 
Number of rows               2.45E+07 
Number of pages          96079 
Pages per MB               128 
Total MB                   751
可用页面大小为8096，但单行最大仅限于8060，而页面头部位于该分配之外 - 在您的计算中将其扣除。 8096数据+ 96页眉= 8192，即8k。 - Andrew
0

你提到主键是聚集索引的一部分，不是整个聚集索引吗？

我有一个想法，如果聚集索引不是唯一的（我的意思是实际上没有明确声明为UNIQUE或PRIMARY KEY），那么SQL Server需要创建一个行ID（RID），我相信这是一个GUID，因此占用8个字节。

如果启用了快照隔离，则行中还会出现额外的开销。如果在打开读取提交的快照时插入或更新了数据，则始终会有8字节的RID和6字节的事务序列号（XTS）。

顺便说一句：为什么要使用100的FILLFACTOR？如果数据从不更改，那没问题，但否则由于页面拆分，它会影响性能。

0

其他人已经正确地提到页面大小为8k，但可用于数据的数量为8096，8060是单个行存储在页面上的最大长度（不使用LoB或SLoB）。 （在设计时提到了差异作为架构保险）。

有各种开销可以应用，从行唯一标识符到可空位图 - Microsoft发布了一份指南，介绍如何计算聚集表/堆的大小。

聚集索引：http://msdn.microsoft.com/en-us/library/ms178085(SQL.90).aspx)

堆：http://msdn.microsoft.com/en-us/library/ms189124(SQL.90).aspx)

关于缩小数据库的话题，也被称为“邪恶” - 请阅读Paul Randal对缩小操作的描述，尽可能避免使用它：http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx。
0

拥有100％的FILLFACTOR并不意味着每个页面都被填满到最大容量-它只是意味着SQL Server会尝试这样做，仅适用于叶节点。

此外，您确实需要非常认真地询问未来性能与空间使用情况的问题。对于那么多记录，过于紧密的填充因子意味着每次新插入甚至更新都可能触发相当大规模的重排，而这取决于使用情况，这也可能意味着死锁升级。并不是说您可能没有一些很好的理由来紧密打包并担心磁盘空间，但您需要非常认真地提出这些问题。现在购买更大的磁盘相当便宜。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接