在SQL SERVER中估算堆大小

摘要：SQL SERVER是占有市場份額較大的一個關系數據庫管理系統，本文討論在數據庫設計階段的一項優化技術，估算堆的大小。

關鍵詞：數據庫；SQL SERVER；計算

中圖分類號：G250.74 文獻標志碼：A 文章編號：1674-9324（2013）22-0146-03

一個數據庫實例能夠以最佳狀態運行是以良好的設計為基礎并配合合理的硬件部署和軟件協助實現的。本文針對占有市場份額很大的數據庫產品SQL SERVER，討論數據庫實例設計方面的優化技術。在數據庫的設計中，我們可以依據概念結構設計的結果得出數據庫的具體模式結構，從而進行合理的部署。數據庫的初始大小是設計中的一個關鍵問題。數據庫初始大小過大，會浪費我們不必要的硬盤空間，數據庫初始大小過小，SQL Server會經常根據需要進行數據庫的擴容，從而增加了系統運行負擔，因此數據庫的初始大小是優化技術的一個關鍵。那么數據庫的初始大小應該設計為多大合適呢？

SQL SERVER的數據庫存儲空間的大小由以下幾個因素決定：堆或者聚集索引的大小，非聚集索引的大小。本篇僅對堆的大小估算進行討論。

一、估算堆的大小的步驟

1.估算表中將存儲的行數：Num_Rows=表中的行數。

2.指定固定長度和可變長度列的數量，并計算存儲所需的空間：計算每組列在數據行中所占據的空間。列的大小取決于數據類型和長度說明。Num_Cols=總列數（固定長度和可變長度）；Fixed_Data_Size=所有固定長度列的總字節數；Num_Variable_Cols=可變長度列的數量；Max_Var_Size=所有可變長度列的最大字節數。

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.計算總的行大小：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_Page估計的頁數應向上舍入到最接近的整數。

8.計算在堆中存儲數據所需的空間量（每頁的總字節為8192）：堆大小（字節）=8192xNum_Pages。

二、下面以employees表為例，估算存儲該表所需的堆空間大小

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

假設所有可變長度列均100%充滿，Max_Var_Size取表中的Address列的長度60，則可變長度列的總字節數：Variable_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，350K，760K。則數據庫的初始大小為=104K+220K+171K+128K+350K+760K=1733K，約2M（上取整）。

定義數據庫的物理結構

CREATE DATABASE EMP

ON

（name=’EMP_DAT’，

Filename’d：MPfileEMP_DAT.MDF’，

Sieze=2M，

Maxsize=5M，

Filegrowth=1M

）

LOG ON

（name=’EMP_log’，

Filename’d：EMPfileEMP_LOG.LDF’，

Sieze=1M，

Maxsize=3M，

Filegrowth=1M

）

三、總結

討論數據庫的初始大小，對于某些實例非常有效。比如數據增加不大的一類管理系統，一旦數據錄入結束，后期運行維護時產生的數據非常小，這樣數據庫基本無擴容，只要確定好數據庫的初始大小即可。在確定物理數據庫的各文件屬性時，SIZE屬性就是本文討論的主要問題。另外，數據庫文件的部署（FILENAME屬性），數據庫的增長方式（Filegrowth屬性）也影響數據庫實例的運行效率。數據庫的增長方式有兩種，按M字節增長和按百分比增長，一般情況下，我們將數據庫的增長方式設置為按M字節增長較優。如果考慮到數據庫的安全性，可以將數據庫文件同RAID磁盤陣列技術結合，合理部署數據庫文件的fliename屬性。將文件部署于不同的磁盤上，既加快了數據庫的讀寫速度，又提高的數據的安全性。合理設計、恰當部署是數據庫在設計階段進行優化的起點，SQL Server的數據庫優化技術還體現在諸多方面，比如索引的設計優化、查詢的優化等。因篇幅有限，在此不多討論。

參考文獻：

[1]明日科技.SQL SERVER 2005開發技術大全[M].北京：人民郵電出版社，2007.

[2]粘新育.SQL Server 2005數據庫應用技術[M].北京：中國鐵道出版社，2011.

[3]岳莉，劉凌，江軍.Study of Real-time Database Based on Common Information Model.International Conference on Intelligent Systems Design and Engineering Application，（ISDEA2013）

作者簡介：岳莉（1977-），女，碩士，講師，現為山東職業學院信息工程系教師，主要從事計算機應用、計算機軟件等方向的教學及應用研究工作。