在SQLSERVER中估算堆大小

1、在SQL SERVE中估算堆大小一个数据库实例能够以最佳状态运行是以良好的设计为基 础并配合合理的硬件部署和软件协助实现的。 本文针对占有市场 份额很大的数据库产品 SQL SERVER讨论数据库实例设计方面 的优化技术。 在数据库的设计中， 我们可以依据概念结构设计的 结果得出数据库的具体模式结构， 从而进行合理的部署。 数据库 的初始大小是设计中的一个关键问题。 数据库初始大小过大， 会 浪费我们不必要的硬盘空间，数据库初始大小过小， SQLServer 会经常根据需要进行数据库的扩容，从而增加了系统运行负担， 因此数据库的初始大小是优化技术的一个关键。 那么数据库的初 始大小应该设计为多

2、大合适呢？SQL SERVERJ数据库存储空间的大小由以下几个因素决定： 堆或者聚集索引的大小， 非聚集索引的大小。 本篇仅对堆的大小 估算进行讨论。一、估算堆的大小的步骤1. 估算表中将存储的行数：Num_Rows表中的行数。2. 指定固定长度和可变长度列的数量， 并计算存储所需的空 间：计算每组列在数据行中所占据的空间。 列的大小取决于数据 类型和长度说明。Num_Cols、列数（固定长度和可变长度）； Fixed_Data_Size= 所有固定长度列的总字节数；Num_Variable_Cols=可变长度列的数量； Max_Var_Size=所有可 变长度列的最大字节数。3. 保留行中称

3、为空位图的部分以管理列的为空性。计算大 小： Null_Bitmap=2+ ( Num_Cols+7)/8 )取整数部分。4. 计算可变长度数据的大小： 假设所有可变长度列均百分之 百充满。 如果表中有可变长度列， 确定在行中存储这些列所用的 空间： Variable_Data_Size=2+ ( Num_Variable_Colsx2 ) +Max_Var_Size 如果预计可变长度列占用的存储空间比例较低， 可以按照该比例调整 Max_Var_Size 值，从而对整个表的大小得 出一个更准确的估计。如果没有可变长度列，则 Variable_Data_Size 取值为 0。5. 计算总的行大

4、小： Row_Size=Fixed_Data_Size+Variable_Data_Size+Null_Bitmap +4 其中的数值 4 是数据行的行标题开销。6. 计算每页的行数(每页大小为 8K,页首的12个字节用于 存储页的基本信息，因此有 8096可用字节)：Rows_Per_Page=8096/ (Row_Size+2)，由于行的存储不能跨页， 因此每页的行数应向下舍入到最接近的整数。 公式中的数值 2是 计算行数时引入的行大小余量。7. 计算存储所有行所需的页数：Num_Pages=Num_Rows/Rows_Per_Pa估e计的页数应向上舍入到 最接近的整数。8. 计算在堆中存

5、储数据所需的空间量(每页的总字节为8192）:堆大小（字节） =8192xNum_Pages二、下面以 employees 表为例， 估算存储该表所需的堆空间大小employees 表结构如下:Create table Employees（ EmployeeID int ，LastName nvarchar （ 20），FirstName nvarchar （ 10），Title nvarchar（ 30），Birthday datatimeHireDate datatime Adress nvarchar （ 60），City nvarchar（ 15），PostalCard nvarcha

6、r （ 10），HomePhone nvarchar （ 12），）其中的固定长度列有 EmployeeID， Birthday ，HireDate 共 计 3 列。其中可变长度列有 LastName，FirstName ，Title ，Adress，City , PostalCard , HomePhone共计 7 列。固定长度列的总字节数:Fixed_Data_Size= （ int ） 4+（ datetime ） 8+（ datetime ） 8=20假设所有可变长度列均 100%充满， Max_Var_Size 取表中的Address 列的长度 60，则可变长度列的总字节数：Vari

7、able_Data_Size=2+ （7x2） +60=76保留空位图大小： Null_Bitmap=2+ （ 10+7）/8 ）=3（下取 整）行的总大小： Row_Size=20+76+3+4=103每页的行数： Rows_Per_Page=8096/（ 103+2） =77 假定该表将存储 1000 条记录，则存储所有行所需的页数： Num_Pages=1000/77=13 （上取整）该表存储所需的空间量：8K*13=104K。估算出数据库中所有表（堆）的大小求总和就得到数据库的初始大小。假定数据库EMP中还有5张表，每张表的堆大小估算结果分别为，220K, 171K, 128K，350

8、K, 760K。则数据库的初始大小为=104K+220K+171K+128K+350K+760K=1733K约 2M （上取整）。定义数据库的物理结构CREATE DATABASE EMPON（ name=EMP_DAT，Filenamed： MPfileEMP_DAT.MDF，Sieze=2M，Maxsize=5M，Filegrowth=1M）LOG ON（name=EMP_log， Filenamed：EMPfileEMP_LOG.LDF，Sieze=1M，Maxsize=3M， Filegrowth=1M ）三、总结 讨论数据库的初始大小， 对于某些实例非常有效。 比如数据 增加不大的一类管理系统， 一旦数据录入结束， 后期运行维护时 产生的数据非常小， 这样数据库基本无扩容， 只要确定好数据库 的初始大小即可。在确定物理数据库的各文件属性时，SIZE 属性就是本文讨论的主要问题。 另外，数据库文件的部署 （ FILENAME 属性），数据库的增长方式（ Filegrowth 属性）也影响数据库 实例的运行效率。数据库的增长方式有两种， 按M字节增长和按 百分比增长，一般情况下，我们将数据库的增长方式设置为按 M 字节增长较优。 如果考虑到数据库的安全性， 可以将数据库文件 同 RAID 磁盘阵列技术结合， 合理部署数据库文件的