SQL Serverのテーブルサイズ見積もり手法について書いていきます。
今回、DBにテーブルを実際に作成し、見積もりの値と実測値の比較をしました。
結論として、実測値とほぼ一致している値を見積もることができました。
今回想定するのは、下記のケースです。
- データベースデザイン時のテーブルサイズ見積もりたい
- 新規に作成するテーブルと似た構成のテーブルがない
- テーブル構成、格納するデータ件数が判明している
準備するもの
今回は下記のSQL Serverのテーブルを想定して見積もりを行います。
テーブル定義は下記の通りとします。
| No | 論理名 | 物理名 | データ型 | Not Null | デフォルト | 備考 |
|---|---|---|---|---|---|---|
| 1 | 会員ID | member_id | varchar(10) | YES(PK) | ||
| 2 | 会員名 | member_name | nvarchar(32) | YES(PK) | ||
| 3 | 性別 | sex | char(1) | |||
| 4 | 生年月日 | birthday | date | |||
| 5 | 年齢 | age | tinyint | |||
| 6 | 登録日時 | regist_date | datetime2 | YES |
インデックス定義は下記の通りとします。
| No | インデックス名 | カラムリスト | ユニーク | 備考 |
|---|---|---|---|---|
| 1 | PK_member | member_id,member_name | Yes | |
| 2 | IDX_member_1 | regist_date |
データ件数：100万件
データタイプ：ヒープ
とします。
実際に計算してみる
下記サイトを参考にしました。
計算手順
但し下記の変数の値はそれぞれ下記とします。
[ブロックサイズ] = 8192バイト
[ページヘッダ] = 96
[レコードヘッダ] = 4バイト
1レコードの平均長を求める
[1レコードの平均長] = [レコードヘッダ]+[NULLビットマップ]+[可変長列追跡]+Σ([列データ])
[レコードヘッダ] = 4
[NULLビットマップ] = TRUNC(2+(列総数 + 7)/8)
※TRUNC・・・小数点以下を切り捨てる
[可変長列追跡] = 2 + ( 可変長列の数 * 2)
Σ([列データ]) = 全列データのバイト数合計
varchar(n) = n + 2
nvarchar(n) = 2n + 2
char(n) = n + 2
date = 3
tinyint = 1
datetime2 = 7 ※有効桁数が 3 または 4 の場合
計算すると、
[NULLビットマップ] = TRUNC(2+(6 + 7)/8)= 3
[可変長列追跡] = 2 + ( 2 * 2 ) = 6
Σ([列データ]) = 12 + 66 + 3 + 3 + 1 + 7 = 92
[1レコードの平均長] = 4 + 3 + 6 + 92 = 105
その他のデータ型のサイズは下記を参照ください。
1ブロックに収まるレコード数を求める
[1ブロックに収まるレコード数]=TRUNC([データ格納部]/([1レコードの平均長]+2))
[データ格納部] = [ブロックサイズ] – [ページヘッダ]
計算すると、
[データ格納部] = 8192 – 96 = 8096
[1ブロックに収まるレコード数]=TRUNC( 8096 / (105 + 2)) = 75
テーブル容量算出
[テーブル容量]=CEILING([レコード件数]/[1ブロックに収まるレコード数],1)*[ブロックサイズ]
※CEILING・・・CEILING(X,1) Xを1の倍数で切り上げ
計算すると、
[テーブル容量] = CEILING( 1,000,000 / 75 , 1 ) * 8192 = 109,232,128
ROUND(109,232,128 /(1024*1024) , 2) ≒ 104.17(MB)
※ROUND・・・ROUND(X,2) Xを小数点第2位で四捨五入
実際に100万行のデータをSQL ServerにINSERTして確認すると98.89(MB)となり、
おおよそ一致していました。
まとめ
以上、SQL Serverのテーブルサイズ見積もり手法でした。
間違い等ございましたら、ご指摘いただけるとありがたいです。
また、SQL Serverを勉強する上で下記の書籍を参考にさせていただきました。
どちらもおすすめです。
コメント