担当してしているシステムが重たい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

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

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

insert into test2 select col1, col2, substring(col3,1,1280) col3 from test1

リビルドと統計情報の更新

SQL

alter index pk_test1 on test1 rebuild;
update statistics test1;

alter index pk_test2 on test2 rebuild;
update statistics test2;

確認。

SQL

select top 10 * from test1;
select top 10 * from test2;

結果。

コストを計算させてみる

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

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

検索するデータ量でコストがどう変わるか、行数と、行あたりの長さの観点で確認します。

SQL1はtest1の検索で、10000行を返す
SQL2もtest1の検索で、行数はSQL1の半分
SQL3はtest2の検索。行数はSQL1と同じだが、1行あたりの長さが半分

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

結果

結果から、主要な列を抜き出すと以下。
各列の意味は、MicrosoftのSET SHOWPLAN_ALLのページを参照。

処理	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

set showplan_all on
go

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

結果

処理	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

set showplan_all on
go

select * from test1; -- SQL1
insert into test2 select * from test1 -- SQL2
insert into test3 select * from test1 -- SQL3

結果

処理	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

set showplan_all on
go

insert into test2 select col1 * 10, col2, col3 from test1 -- SQL4

処理	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

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;

結果

階層	処理	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

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;

処理	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

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

結果

階層	処理	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の結果参照

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

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

所感

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

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