参照ページ数の確認

はじめに

今までの実験で、テーブル定義によってIn-row dataとRow-overflow dataのページが作成されることを確認してきたが、単純に読み込んだページ数を確認するだけならSET STATISTICS IO ONを利用するのが簡単だ。
※実はSET STATISTICS IOの結果を理解するために今までのステップを踏んできた。

実験

dbo.yokkameテーブルを作成。セットする文字列長により、In-rowには列c1, c2が収まり、列c3, c4, c5がRow-overflow dataページに格納されるはず。
そしてデータは5行投入するので、In-rowが5ページ、Row-overflowが3x5=15ページ作成されるはずだ。

if Exists(
    Select * 
    From INFORMATION_SCHEMA.TABLES 
    Where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'yokkame'
)

Drop Table dbo.yokkame;
GO

CREATE TABLE dbo.yokkame (
    [c1] [int] NOT NULL,
    [c2] [nvarchar](3600) NULL,
    [c3] [nvarchar](3800) NULL,
    [c4] [nvarchar](3800) NULL,
    [c5] [nvarchar](3800) NULL
);
GO

-- データ投入
Declare @s nvarchar(3800);
Set @s = replicate('0123456789',380);

Insert into dbo.yokkame Values(1, replicate('0123456789', 360), @s, @s, @s);
Insert into dbo.yokkame Values(2, replicate('0123456789', 360), @s, @s, @s);
Insert into dbo.yokkame Values(3, replicate('0123456789', 360), @s, @s, @s);
Insert into dbo.yokkame Values(4, replicate('0123456789', 360), @s, @s, @s);
Insert into dbo.yokkame Values(5, replicate('0123456789', 360), @s, @s, @s);
GO

今まで同様、dbcc indの結果を確認する。

予想通り、PageType=1のIn-row dataが5行、PageType=3のRow-overflow dataが15行作成された。

これをSET STATISTICS IO ONにしてSeelctするとこうなる。
※結果をメッセージペインに切り替えてます。

詳細はMS公式サイトを見てもらうとして、確かにIn-rowで5ページ、Row-overflowで（表記上はLOBとしてまとめられているが）15ページの読み取りが発生している。

追加実験

上図において「物理読み取り数」はIn-row, Row-overflowともにゼロとなっている。
これはInsertした時点のデータがバッファプール（メモリキャッシュ）に残っているからだと考えられる。
ではサービス起動直後ならばディスクからの初回読み取りが発生するので、物理読み取り数がカウントされるのではないだろうか。

ということで、SQLServerのサービスを再起動し、全行Selectしてみた。

結果はこちら。

予想に反して、In-rowの物理読み取りが1、Row-overflowの物理読み取りが2となった。
しかも論理読み取り数は先ほどの結果と変わらない。

これは実際のI/Oは連続した8ページ（エクステント）単位で行われる、ということに起因していると思われる。
dbcc indの結果（上図）を見ていただくと、In-row dataに関しては5ページ分なので、実際のdisk I/Oは1回で済んでいるはずだ。PagePIDも424～428の連続した5個となっている。
同様にRow-overflowについては全部で15行あるので、エクステントとしては2回読み取りを行っているはずだ。PagePIDも、408～415と、432～438の2区間に分かれている。

ただ公式サイトでは「物理読み取りページ数」となっていて、「物理読み取り回数」ではないので、上記の推測は外れているかもしれない。

調査は追ってやるとして、さらに大量データで確認してみよう。

追加実験その2

大量データ投入

Truncate Table dbo.yokkame;
GO

Declare @strseed nchar(10);
Set @strseed = '0123456789';

Declare @str3600 nvarchar(3600);
Set @str3600 = replicate(@strseed,360);

Declare @str3800 nvarchar(3800);
Set @str3800 = replicate(@strseed,380);


with N1(dummy) as (Select 0 union all Select 0)  -- 2 rows
,N2(dummy) as (Select 0 From N1 as T1 cross join N1 as T2) -- 4rows
,N3(dummy) as (Select 0 From N2 as T1 cross join N2 as T2) -- 16rows
,N4(dummy) as (Select 0 From N3 as T1 cross join N3 as T2) -- 256rows
,N5(dummy) as (Select 0 From N4 as T1 cross join N2 as T2) -- 1024rows
,IDs(id) as (Select ROW_NUMBER() over (order by (select null)) from N5)

Insert into dbo.yokkame
Select id, @str3600, @str3800, @str3800, @str3800
From IDs;
GO

上記CTE式の部分は参考書籍の真似なのだが、こんな使い方があるのかとびっくり。
View定義などで長いSelect文を分解して視認性を上げたいときにCTEを使うことは知っていたけど（複数定義を並べるとき、前方で定義したものを後方で使えるので便利）、これは思いつかなかった。
しかも見て分かりやすい。
最後のorder by (select null)も脱帽。
すっすいません、知りませんでした．．．

次にdbcc indの結果を集計して、合計何ページ分を消費しているか確認する。

Declare @result_ind table(
    PageFID int,
    PagePID int,
    IAMFID int,
    IAMPID int,
    ObjectID int,
    IndexId int,
    PartitionNumber bigint,
    PartitionID bigint,
    iam_chain_type sysname,
    PageType int,
    IndexLevel int,
    NextPageFID int,
    NextPagePID int,
    PrevPageFID int,
    PrevPagePID int
);

Declare @stat nvarchar(1000);
Set @stat = 'dbcc ind(''GHJK'', ''dbo.yokkame'', -1)';

Insert into @result_ind Exec(@stat);

Select iam_chain_type, Count(*) as number_of_pages
From @result_ind
Where PageType in(1, 3)
Group by iam_chain_type;
GO

※なお作業中の食欲を抑えるため、DB名は変更済。
さて、上記の結果はというと．．．

Row-overflow dataは、レコード数の3倍あるはず。
1024 x 3 = 3072
予定どおりの結果となった。

そして最後に、サービスを再起動してSelectしてみます。

SET STATISTICS IO ON で Selectした結果は．．．

LOBのほうは予想通り。物理読み取り数384ってことは、384 x 8 = 3,072
なので、これは「物理的に読みに行った回数」ってことでいいのではないかと思う。
※混合エクステントになると単純に8倍にならなくなると思うので、エクステント数ではなく読み取り回数なのだろう。

一方In-rowのほうは、物理読み取りではなく「先行読み取り数」にカウントが入った。
値自体は1,024で問題なし。

ひとまず今回はここまで。