#今回の学習テーマ
- SQLServerのストレージの基本単位はページである。
- ディスクI/Oはページ単位で行われる。
- 1ページは8KBである。
- ページの先頭には96byteのヘッダ領域がある。
- 実際にデータを格納できるのはページあたり8,060byteである。
出典は公式サイトより
このあと何をやるにしても、データの格納のされかたを理解しておくことが重要だと思うので、ここを突っ込んでみたい。
なお96byteのヘッダ情報については、
ページ番号、ページの種類、ページ上の空き容量、そのページを所有しているオブジェクトのアロケーション ユニット ID が含まれます。
とあるので、今回はひとまずそれで良しとする。
きっと後でじっくり見る機会があるでしょう。
#実験
まずは準備。
-- 実験用テーブル作成
Use DORAYAKI;
GO
if Exists(
Select *
From INFORMATION_SCHEMA.TABLES
Where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'gantan'
)
Drop Table dbo.gantan;
GO
CREATE TABLE [dbo].[gantan](
[c1] [int] NOT NULL,
[c2] [nvarchar](50) NULL,
[c3] [nvarchar](100) NULL,
[c4] [bit] NOT NULL,
[c5] [nchar](10) NULL,
[c6] [smallint] NULL
);
GO
-- 実験用データ投入
-- c1の値が3, 4, 5のレコードについて、実際の格納のされかたを確認する。
Insert into dbo.gantan(c1, c2, c3, c4, c5, c6) Values(1, 'amai', 'koshian', 0, '2ko-set', 12);
Insert into dbo.gantan(c1, c2, c3, c4, c5, c6) Values(2, 'amai', 'koshian', 1, '2ko-set', 12);
Insert into dbo.gantan(c1, c2, c3, c4, c5, c6) Values(3, null, 'koshian', 0, '2ko-set', 12);
Insert into dbo.gantan(c1, c3, c4, c5, c6) Values(4, 'koshian', 0, '2ko-set', 12);
Insert into dbo.gantan(c1, c2, c3, c4, c5, c6) Values(5, 'amai', 'koshian', 0, '2ko-set', 12);
GO
Update dbo.gantan
Set c2 = null
Where c1 = 5;
GO
このようなデータが出来上がりました。
次にdbcc indで格納されているページidを取得します。
dbcc ind('DORAYAKI', 'dbo.gantan', -1);
その結果
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 142 NULL NULL 1877581727 0 1 72057594044219392 In-row data 10 NULL 0 0 0 0
1 368 1 142 1877581727 0 1 72057594044219392 In-row data 1 0 0 0 0 0
(2 rows affected)
DBCC の実行が完了しました。DBCC がエラー メッセージを出力した場合は、システム管理者に相談してください。
えっエラーメッセージを出力した場合は．．．相談とか考えたくないですね。（汗）
結果は2行表示されますが、PageType=1の行が実際にデータが格納されているページの情報になります。
※PageType=10のほうは追って調べることといたします。
ページID(PagePID) = 368 ということで、そのページの情報を出力。
dbcc traceon(3604);
dbcc page('DORAYAKI', 1, 368, 3);
結果表示はかなり長くなりますので、今回必要とするページヘッダの情報を抜粋・編集したものがこちら。
上から順に5行分を掲載しています。
1-2-3-4--5-6-7-8--9-0-1-2--3-4-5-6--7-8-9-0-
30001f00 01000000 f832006b 006f002d 00730065 0........2.k.o.-.s.e
00740020 00200020 000c0006 00000200 30003e00 .t. . . ........0.>.
61006d00 61006900 6b006f00 73006800 69006100 a.m.a.i.k.o.s.h.i.a.
6e00 n.
30001f00 02000000 a932006b 006f002d 00730065 0.......©2.k.o.-.s.e
00740020 00200020 000c0006 00000200 30003e00 .t. . . ........0.>.
61006d00 61006900 6b006f00 73006800 69006100 a.m.a.i.k.o.s.h.i.a.
6e00 n.
30001f00 03000000 f832006b 006f002d 00730065 0........2.k.o.-.s.e
00740020 00200020 000c0006 00020200 28003600 .t. . . ........(.6.
6b006f00 73006800 69006100 6e00 k.o.s.h.i.a.n.
30001f00 04000000 f832006b 006f002d 00730065 0........2.k.o.-.s.e
00740020 00200020 000c0006 00020200 28003600 .t. . . ........(.6.
6b006f00 73006800 69006100 6e00 k.o.s.h.i.a.n.
30001f00 05000000 a832006b 006f002d 00730065 0.......¨2.k.o.-.s.e
00740020 00200020 000c0006 00020200 28003600 .t. . . ........(.6.
6b006f00 73006800 69006100 6e00 k.o.s.h.i.a.n.
今回は参考文献を片手に上記のヘッダ情報を攻略。分かるまで1日半かかった。
自分なりにまとめた情報がこちら。なお実例欄は1行目のデータをもとにしています。
本当はデータが横に並んでいるから横で作ろうと思ったんだけど、長くなりそうだったので縦にしました。ご勘弁を。項目名の英語も多少アレンジしてます。
#振り返り
- 疲れた。
- スキーマ定義とは関係なく、固定長フィールドが先に配置されることが分かった。
- 当たり前だけど、可変長フィールドは行ごとに格納位置の計算が入るから固定長よりパフォーマンスが出ないだろうと思った。
- 1ページで収まらないときどうすんのよという疑問は次回にて。
#疑問点（1/5追記）
- bit型のデータの書き込まれる値がわからない。上図例では0と1に対応するのは「f8」と「a9」であったが、今日違うスキーマのテーブルで実験したところ、「a8」と「a9」であった。もしかすると「bit」の名のとおり、この1バイトのビット列の中の特定のビットだけを参照しているのかもしれない。8と9という値を考えると、最下位ビットだけ見ているとか。だとすれば他のビットは何に使っているのだろうう、という新たな疑問が．．．
- ※ご存知の方いらっしゃいましたら是非ご一報ください。