担当してしているシステムが重たい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の結果参照
検索条件の組み方に依存しそうな面もあるが、
ある程度予想してくれることを期待してもよさそう。
個人的に、今回の調査の目的はこの評価で、
最低限の結果は得られたと思うので、このくらいにしておきます。
所感
かなり基本的な処理に絞ったにもかかわらず、謎の計算が散見され、
やはりこの辺りはカオスなんだなぁ、という感想です。
※結論・結果は冒頭にまとめてあります。