備忘録ITITDB

SQL Server テーブルサイズの見積もり手法

SQL Serverのテーブルサイズ見積もり手法について書いていきます。
今回、DBにテーブルを実際に作成し、見積もりの値と実測値の比較をしました。
結論として、実測値とほぼ一致している値を見積もることができました。

今回想定するのは、下記のケースです。

  • データベースデザイン時のテーブルサイズ見積もりたい
  • 新規に作成するテーブルと似た構成のテーブルがない
  • テーブル構成、格納するデータ件数が判明している

準備するもの

今回は下記のSQL Serverのテーブルを想定して見積もりを行います。
テーブル定義は下記の通りとします。

No論理名物理名データ型Not Nullデフォルト備考
1会員IDmember_idvarchar(10)YES(PK)
2会員名member_namenvarchar(32)YES(PK)
3性別sexchar(1)
4生年月日birthdaydate
5年齢agetinyint
6登録日時regist_datedatetime2YES

インデックス定義は下記の通りとします。

Noインデックス名カラムリストユニーク備考
1PK_membermember_id,member_nameYes
2IDX_member_1regist_date

データ件数:100万件
データタイプ:ヒープ
とします。

実際に計算してみる

下記サイトを参考にしました。

ヒープ サイズの見積もり - SQL Server

計算手順

但し下記の変数の値はそれぞれ下記とします。
[ブロックサイズ] = 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

その他のデータ型のサイズは下記を参照ください。

データ型 (Transact-SQL) - SQL Server

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を勉強する上で下記の書籍を参考にさせていただきました。
どちらもおすすめです。

コメント

タイトルとURLをコピーしました