SQL Serverの実行計画にある「コスト」は何を計算しているのか

担当してしているシステムが重たいSQLでつぶれまして、
今後の再発を防ぐために、アプリ側で重たいSQLをはじくことになりました。

こういう場合、SQLの実行時間に上限を設定するのが一般的だと思うのですが、
諸事情により、時間での制限が使いにくく。

ということで、SQLの実行計画の推定コストが一定以上だったら殺す、という
手が使えないかなと思い、その前段として、コストってざっくり何なの、を
調べてみました。

この辺りは、ちゃんと調べることが不可能なくらいカオスだろうと
認識してはいるのですが、背に腹は代えられない事情もありまして。

なお、検証環境はSQL Server 2022ですが、
互換性レベルは110(SQL Server 2012)で行っています。
古くてすみません。

## 結論
最初に結論は以下。ただ、あまり信じないでください。
- Cost = EstimateIO + EstimateCPU
- EstimateIO = Read IO + Write IO x 6.76
- 単純なSQLなら、Estimate IOがコストの大半
- ただし、HashのEstimateCPUは意外と高い

Readの量は、そのクエリの結果を返すために読まれたデータの総量。
1行を返すためでも、100行を読んだなら、100行分がコストになる。
IOの単位は謎。


## 準備
以下の3テーブルを作成
- test1 ・・・基本テーブル
- test2 ・・・比較用テーブル。基本テーブルと行数は同じ、行あたりのデータ量は半分。
- test3 ・・・比較用テーブル。クラスタ化インデックスなし。他はtest1と同じ。 

### 評価用テーブル作成
```SQL:SQL
create table test1 (
	col1 int not null
	,col2 char(10) not null 
	,col3 varchar(4000) null
    ,constraint [pk_test1] primary key clustered (col1)
);

create table test2 (
	col1 int not null
	,col2 char(10) not null 
	,col3 varchar(4000) null -- 定義長は同じ。調整は入れるデータで。
    ,constraint [pk_test2] primary key clustered (col1)
);

create table test3 (
	col1 int not null 
	,col2 char(10) not null
	,col3 varchar(4000) null
);

```
### 評価用データ投入(test1)
```SQL:SQL
SET IMPLICIT_TRANSACTIONS OFF;

declare 
	@i int = 1 -- ループカウンタ兼col1の値用
	, @j int = 1 -- ループカウンタ
	, @col2_val char(10) -- col2の値用。@iをゼロ埋め10桁で入れる 
	, @col3_val varchar(4000) -- col3の値用。col2の値を繰り返して長くしたもので入れる

-- トランザクションの開始
begin transaction;

while @i <= 100000 --とりあえず、10万行ほど作成
begin
	-- col2列の値作成。col1の値をゼロ埋め10桁にしたもので
	set @col2_val = format(@i,'D10');
	
	-- col3列の値の生成。col2の値を2^8=256回繰り返したもので
	set @col3_val = format(@i,'D10');
	set @j = 0;
	while @j < 8 
	begin
		set @col3_val = @col3_val + @col3_val;
		set @j = @j + 1;
	end

	-- テーブルへ挿入
	insert into test1 values (@i,@col2_val,@col3_val);

	-- 1000件ごとにcommit
	if @i % 1000 = 0
	begin
		commit transaction;
		begin transaction;
	end

	set @i = @i + 1; -- ループのカウントアップ
end
commit transaction; -- コミット
```
### 評価用データ投入(test2)
test2にもデータ投入。col1、col2はtest1同じ。col3はtest1のデータの半分。
```SQL:SQL
insert into test2 select col1, col2, substring(col3,1,1280) col3 from test1
```
### リビルドと統計情報の更新
```SQL:SQL
alter index pk_test1 on test1 rebuild;
update statistics test1;

alter index pk_test2 on test2 rebuild;
update statistics test2;
```

確認。
```SQL:SQL
select top 10 * from test1;
select top 10 * from test2;
```
結果。
![picture001.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/944269/28629001-c04c-48b5-dffd-015d0580495a.png)

## コストを計算させてみる
いろいろコストを計算させてみます。

### 検索するデータ量の評価1

検索するデータ量でコストがどう変わるか、行数と、行あたりの長さの観点で確認します。
- SQL1はtest1の検索で、10000行を返す
- SQL2もtest1の検索で、行数はSQL1の半分
- SQL3はtest2の検索。行数はSQL1と同じだが、1行あたりの長さが半分

#### SQL
```SQL:SQL
set showplan_all on
go

select * from test1 where col1 <= 10000; -- SQL1
select * from test1 where col1 <=  5000; -- SQL2
select * from test2 where col1 <= 10000; -- SQL3
```
#### 結果
![picture002.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/944269/a135fe55-3780-2895-36b9-a3da7edb648a.png)

結果から、主要な列を抜き出すと以下。
各列の意味は、Microsoftの[SET SHOWPLAN_ALL](https://learn.microsoft.com/ja-jp/sql/t-sql/statements/set-showplan-all-transact-sql?view=sql-server-ver16)のページを参照。

|処理|EstimateRows|EstimateIO|EstimateCPU|AvgRowSize|TotalSubCost|EstimateExecutions|
|--|--|--|--|--|--|--|
|SQL1全体|10144.63||||2.518886||
|SQL1のpk_test1のseek|10144.63|2.50757|0.0113161|2025|2.518886|1|
|SQL2全体|5351.344||||1.329909||
|SQL2のpk_test1のseek|5351.344|1.323866|0.006043478|2025|1.329909|1|
|SQL3全体|9746.19||||1.216966||
|SQL3のpk_test2のseek|9746.19|1.206088|0.01087781|2025|1.216966|1|

#### 考察
- Cost = EstimateIO + EstimateCPU。これは、まあ、そうでしょう
- いずれのSQLもコストの大半はEstimateIO。これは納得できる
- SQL2のコストはSQL1の約半分。検索行数が半分なので、これも納得できる
- SQL3のコストもSQL1の約半分。1行あたりのデータ量が半分なので、これも納得できる
- AvgRowSizeが怪しい。SQL1とSQL3が同じなので、テーブルの定義長だけ見ていそう

SQL3は、SQL1と比較して、AvgRowSizeが同じ、EstimateRowsもほぼ同じなのに、
どうしてコストが半分だと分かるのだろう？
うーん。まあ、こんなところから悩んでいても仕方ないので、次に行きます。


### 検索するデータ量の評価2
結果の行数にかかわらず、SQLを返すために裏で検索する全データの量が
コストになることの確認。
- SQL1 全件検索
- SQL2 インデックスのない列に条件を設定。返すのは1行だが全件検索

#### SQL
```SQL:SQL
set showplan_all on
go

select * from test1; -- SQL1
select * from test1 where col2 = '0000000010'; -- SQL2
```

#### 結果
![picture003.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/944269/b2e1841a-cca6-b476-dcb0-3b38fd355e33.png)

|処理|EstimateRows|EstimateIO|EstimateCPU|AvgRowSize|TotalSubCost|EstimateExecutions|
|--|--|--|--|--|--|--|
|SQL1全体|100000||||24.80513||
|SQL1のpk_test1のscan|100000|24.69498|0.110157|2025|24.80513|1|
|SQL2全体|1.003103||||24.77634||
|SQL2のParallelism(Gather)|1.003103|0|0.02864181|2025|24.77634|1|
|SQL2のpk_test1のscan|1.003103|24.69498|0.036719|2025|24.7317|1|

#### 考察
- いずれのSQLもコストの大半はEstimateIO。これは納得できる
- SQL1のEstimateRowsは10000。SQL2は約1。これもOK
- SQL1とSQL2のコストはほぼ同じ。想定通り

これは想定通り。

### Read IOとWrite IOのコスト評価
ReadのIOとWriteのIOでは、当然、後者のコストが高いはずなので、
そのあたりどう評価されるか確認。

クラスタ化インデックスの有無も評価したかったので、test3も評価。

- SQL1 test1から単純検索(Read)
- SQL2 test2への単純挿入(Write)
- SQL3 test3への単純挿入(Write)

#### SQL
```SQL:SQL
set showplan_all on
go

select * from test1; -- SQL1
insert into test2 select * from test1 -- SQL2
insert into test3 select * from test1 -- SQL3
```
#### 結果
![picture004.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/944269/059d29da-6bb0-b041-9fa7-78278be5437e.png)

|処理|EstimateRows|EstimateIO|EstimateCPU|AvgRowSize|TotalSubCost|EstimateExecutions|
|--|--|--|--|--|--|--|
|SQL1全体|100000||||24.80513||
|SQL1のpk_test1のscan|100000|24.69498|0.110157|2025|24.80513|1|
|SQL2全体|100000||||191.9303||
|SQL2のtest2へのinsert|100000|167.0251|0.1|9|191.9303|1|
|SQL2のpk_test1のscan|100000|24.69498|0.110157|2025|24.80513|1|
|SQL3全体|100000||||191.9303||
|SQL2のtest3へのinsert|100000|167.0251|0.1|9|191.9303|1|
|SQL2のpk_test1のscan|100000|24.69498|0.110157|2025|24.80513|1|

#### 考察
- SQL2を見ると、検索IOコスト24.69498に対して、挿入IOコストは167.0251
- つまり、WriteはReadの6.76倍くらいで計算されている
- SQL2とSQL3を比較すると、クラスタ化インデックスの有無でコストは変わらない

Writeが6.76倍は、まあ納得。そんなものかと。

でも、クラスタ化インデックスの有無でコスト変わらないって何。
入れるときに、col1でソートが必要なのに。
取得元のtest1のデータがすでにソートされているから?

ちょっと、col1でのソート情報が分からなくなるように、計算を追加して再確認。

```SQL:SQL
set showplan_all on
go

insert into test2 select col1 * 10, col2, col3 from test1 -- SQL4
```
![picture005.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/944269/1468254c-aa42-2ef9-090a-902d6c7d31e5.png)


|処理|EstimateRows|EstimateIO|EstimateCPU|AvgRowSize|TotalSubCost|EstimateExecutions|
|--|--|--|--|--|--|--|
|追加SQL全体|100000||||191.9403||
|追加SQLのtest2へのinsert|100000|167.0251|0.1|9|191.9403|1|
|追加SQLの col1 * 10 の計算|100000|0|0.01|2025|24.81513|1|
|追加SQLのpk_test1のscan|100000|24.69498|0.110157|2025|24.80513|1|

変わらない。なぜだ。
これも分かりそうにないので、次に行きます。

### JOINのコスト評価1(基本)

テーブルのJOINでコスト計算がどうなるか確認します。
- SQL1 loop join。結合相手のインデックスをseekするパターン。よくあるやつ
- SQL2 hash join。SQL1と比較するため、あえて非効率なパターンを使用

#### SQL
```SQL:SQL
set showplan_all on
go

-- SQL1
select * 
from test1 inner loop join test2 on test2.col1 = test1.col1 + 1
where test1.col1 <= 1000; 

-- SQL2
select * 
from test1 inner hash join test2 on test2.col1 = test1.col1 + 1
where test1.col1 <= 1000; 
```
#### 結果
![picture006.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/944269/7b9387db-4708-2415-51e2-ae71d1d5c78d.png)

|階層|処理|EstimateRows|EstimateIO|EstimateCPU|AvgRowSize|TotalSubCost|EstimateExecutions|
|--|--|--|--|--|--|--|--|
|1|SQL1全体|947.0847||||3.257773||
|1-1|SQL1 netsted loop|947.0847|0|0.003958814|4041|3.257773|1|
|1-1-1|SQL1のtest1.col1+1の計算|947.0847|0|0.00009470848|2029|0.2377518|1|
|1-1-1-1|SQL1のpk_test1のseek|947.0847|0.2364583|0.001198793|2025|0.2376571|1|
|1-1-2|SQL1のtest2のpk_test2のseek|1|0.003125|0.0001581|2025|3.016062|947.0847|
|2|SQL2全体|947.0847||||14.66374||
|2-1|SQL2 hash match|947.0847|0|1.967523|4041|14.66374|1|
|2-1-1|SQL2のtest1.col1+1の計算|947.0847|0|0.00009470848|2029|0.2377518|1|
|2-1-1-1|SQL2のpk_test1のseek|947.0847|0.2364583|0.001198793|2025|0.2376571|1|
|2-1-2|SQL2のtest2のpk_test2のscan|100000|12.34831|0.110157|2025|12.45847|1|

#### 考察
- SQL1の結果から、1000件を1回のseekで持ってくる処理(1-1-1-1)のコストは0.2376571
- SQL1の結果から、1件をseekして持ってくる処理×1000回(1-1-2)のコストは3.016062
- 比率は12.69倍くらいで、大きいことは大きいが、まあ、こんなものか
- SQL2の結果から、hash match処理(2-1)でCPUコストを1.967523使っており、思ったより大きい

hash matchのCPUコストが思ったより重い。

試しに、test2から流れてくるレコード数を1000件にを絞っても、CPUコストは1.318153。
matchをかける処理も負荷は発生させるが、hashテーブルを作る処理が重く見える。

```SQL:SQL
set showplan_all on
go

-- SQL3
select * 
from test1 inner hash join test2 on test2.col1 = test1.col1 + 1
where test1.col1 <= 1000 and test2.col1<1000; 
```
![picture007.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/944269/739803cd-deeb-f3bd-6deb-9068fad752d3.png)

|処理|EstimateRows|EstimateIO|EstimateCPU|AvgRowSize|TotalSubCost|EstimateExecutions|
|--|--|--|--|--|--|--|
|SQL3全体|9.690329||||1.658588|NULL|
|SQL3 hash match|9.690329|0|1.318153|4041|1.658588|1|
|SQL3のtest1.col1+1の計算|947.0847|0|0.00009470848|2029|0.2377518|1|
|SQL3のpk_test1のseek|947.0847|0.2364583|0.001198793|2025|0.2376571|1|
|SQL3のtest2のpk_test2のseek|799.7376|0.1016435|0.001036711|2025|0.1026802|1|


### JOINのコスト評価2(組み合わせ爆発)
ひとつひとつのテーブル(検索結果)の行数は少ないが、
JOINによって爆発的に行数が増えるパターンを、
コストで評価できるかの確認です。

100行しかないテーブルでも、3つ単純に結合すると
100×100×100 = 100万行のデータが発生してしまう、など。

今回、担当システムを潰してくれたのが、この手のクエリでした。

#### SQL
```SQL:SQL
set showplan_all on
go

insert into test3
	select 1.col1, t2_1.col2, t2_2.col3
	from 
		test1 t1 
		inner join test2 t2_1 on t1.col1 < t2_1.col1
		inner join test2 t2_2 on t1.col1 < t2_2.col1 
	where 
		t1.col1 <= 100 
		and t2_1.col1 between 101 and 200
		and t2_2.col1 between 201 and 300

```
#### 結果
![picture008.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/944269/ee40eaf8-df0f-d2e2-8b62-f06dbe5fdd3b.png)

|階層|処理|EstimateRows|EstimateIO|EstimateCPU|AvgRowSize|TotalSubCost|EstimateExecutions|
|--|--|--|--|--|--|--|--|
|1|SQL全体|639968.1|0|0|0|1073.406|0|
|1-1|作成したデータをtest3へ挿入|639968.1|1068.89|0.639968|9|1073.406|1|
|1-1-1|t2-1とt1-1のjoin結果からt-2-2へnested loopでjoin|639968.1|0|2.675066|2025|3.875766|1|
|1-1-1-1|t2-1からt1-1へnested loopでjoin|7767.666|0|0.03246884|21|0.09374595|1|
|1-1-1-1-1|pk_test2をseekしt2-1を作成|81.97532|0.01275463|0.0002471729|21|0.0130018|1|
|1-1-1-1-2|t1-1をスプール|94.75614|0.01|0.0001171561|11|0.04594501|81.97532|
|1-1-1-1-2-1|pk_test1をseekしt1-1を作成|94.75614|0.02608796|0.0002612318|11|0.02634919|1|
|1-1-1-2|t2-2をスプール|82.38873|0.01|0.00011503|2015|0.9149632|7767.666|
|1-1-1-2-1|pk_test2をseekしt2-2を作成|82.38873|0.01275463|0.0002476276|2015|0.01300226|1|

#### 考察
- 1-1-1のEsimateRowsが約64万件で、実際の行数(100万件)をある程度予期できている
- 全体のTotalSubCostは1073.406で、単純10万行挿入(※)の191.9303の5倍程度

※「Read IOとWrite IOのコスト評価」のSQL3の結果参照

検索条件の組み方に依存しそうな面もあるが、
ある程度予想してくれることを期待してもよさそう。

個人的に、今回の調査の目的はこの評価で、
最低限の結果は得られたと思うので、このくらいにしておきます。

## 所感
かなり基本的な処理に絞ったにもかかわらず、謎の計算が散見され、
やはりこの辺りはカオスなんだなぁ、という感想です。

※結論・結果は冒頭にまとめてあります。