SQL SERVER的数据库设计优化技术

【摘要】SQL SERVER是占有市场份额较大的一个关系数据库管理系统，本文讨论在数据库设计阶段的优化技术——估算数据库初始大小。

【关键词】数据库 SQL SERVER

Optimizations On DataBase Design Of SQL SERVER

Yue Li

【Abstract】

SQL SERVER is a kind of DBMS which is popular used, the paper explores the optimizations on database design.——estimating the initial size of the DataBase

【Keywords】

Database; SQL SERVER

一个数据库实例能够以最佳状态运行是以良好的设计为基础并配合合理的硬件部署和软件协助实现的。本文针对占有市场份额很大的数据库产品SQL SERVER，从数据库实例的设计阶段估算数据库初始大小来阐述优化数据库的关键技术。

根据概念结构设计的结果我们可以得出数据库的具体结构，从而进行合理的部署。数据库的初始大小就是一个关键问题。数据库初始大小过大，会浪费我们不必要的硬盘空间，数据库初始大小过小， SQL Server会经常根据需要进行数据库的扩容，从而增加了系统运行负担，因此数据库的初始大小是优化技术的另一个关键。那么数据库的初始大小应该设计为多大合适呢？

SQL SERVER的数据库存储空间的大小由以下几个因素决定：堆或者聚集索引的大小，非聚集索引的大小。本篇仅对堆进行讨论。

1. 估算堆的大小

①估算表中将存储的行数：

Num_Rows = 表中的行数

②指定固定长度和可变长度列的数量，并计算存储所需的空间：

计算每组列在数据行中所占据的空间。列的大小取决于数据类型和长度说明。

Num_Cols = 总列数（固定长度和可变长度）

Fixed_Data_Size = 所有固定长度列的总字节数

Num_Variable_Cols = 可变长度列的数量

Max_Var_Size = 所有可变长度列的最大字节数

③保留行中称为空位图的部分以管理列的为空性。计算大小：

Null_Bitmap = 2 + ((Num_Cols + 7) / 8)取整数部分

④计算可变长度数据的大小：假设所有可变长度列均百分之百充满

如果表中有可变长度列，确定在行中存储这些列所用的空间：

Variable_Data_Size = 2 + (Num_Variable_Cols x 2) + Max_Var_Size

如果预计可变长度列占用的存储空间比例较低，可以按照该比例调整 Max_Var_Size 值，从而对整个表的大小得出一个更准确的估计。

如果没有可变长度列，则 Variable_Data_Size 取值为 0。

⑤计算总的行大小：

Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4

其中的数值 4 是数据行的行标题开销。

⑥计算每页的行数（每页大小为8K，页首的12个字节用于存储页的基本信息，因此有 8096 可用字节）：

Rows_Per_Page = 8096 / (Row_Size + 2)

由于行的存储不能跨页，因此每页的行数应向下舍入到最接近的整数。 公式中的数值 2 是计算行数时引入的行大小余量。

⑦计算存储所有行所需的页数：

Num_Pages = Num_Rows / Rows_Per_Page

估计的页数应向上舍入到最接近的整数。

⑧计算在堆中存储数据所需的空间量（每页的总字节为 8192）：

堆大小（字节）= 8192 x Num_Pages

2. 下面以employees表为例，估算存储该表所需的堆空间大小。

表结构如下：

Create table Employees

(EmployeeID int,

LastName nvarchar(20),

FirstName nvarchar(10),

Title nvarchar(30),

Birthday datatime

HireDate datatime

Adress nvarchar(60),

City nvarchar(15),

PostalCard nvarchar(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

假设所有可变长度列均百分之百充满，Max_Var_Size取表中的Address列的长度60，则可变长度列的总字节数：Variable_Data_Size=2 + (7 x 2) + 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估算出数据库中所有表（堆）的大小求总和就得到数据库的初始大小。

总结：合理设计、恰当部署是数据库在设计阶段进行优化的起点，SQL Server的数据库初始大小是优化的一个方面，关于聚集索引和非聚集索引的大小，在此不多讨论，我将通过后续文章讨论。