SQL Server Row Goal の話し

2023/03/03 にあるはずの meetup app osaka@7 のネタ。

色々賢くなってるようなので、ちょっと確認。

データは、Release AdventureWorks sample databases · microsoft/sql-server-samples · GitHub の AdventureWorksDW2019 使ってます。
SQL Server のバージョンは、 2022 使ってますが、もうちょい古くてもこの機能入ってます。

Row Goal は、クエリ内に特定のキーワード(top, offset fetch, exists, etc...) のような全部見なくても良さげ？みたいなのが入った時に、良しなにやってくれそうな感じやつです。

同じクエリで片方は Row Goal を無効にした場合の実行プランを見比べます。

select top(25) * from [FactProductInventory]
;
select top(25) * from [FactProductInventory]
option (use hint ('disable_optimizer_rowgoal') )
;

実行プランをみると

上のプランは、25行読む予定で25行読んだで。
下のプランは、776286行読む予定で25行で終わったで。
みたいな感じです。
何もしてないシンプルなクエリだとまあ全部読まなくて良かったねで済みそうですね。
ここのポイントは、何行読む予定(見積行)の数によって、実行プランが変わる場合があるよってのを次の例で試してみます。

試すクエリはこんなやつです。

select top(100) * 
from 
  [FactInternetSales] inner join [DimCustomer] on
    [FactInternetSales].[CustomerKey] = [DimCustomer].[CustomerKey]
order by 
  [DimCustomer].[EmailAddress]
  , [DimCustomer].[CustomerKey]
  , [FactInternetSales].[SalesOrderNumber]
  , [FactInternetSales].[SalesOrderLineNumber]

テーブルくっ付けて、並び替えて先頭 100 件取る感じ。
where 条件は無しで、join の結合箇所も [DimCustomer] が PK というくらいのインデックスしかないです。
そうすると、 [FactInternetSales] と [DimCustomer] を スキャン して結合、ソートして先頭100件というようなプランになります。
これは top があっても、ソートしないとダメでインデックスでソート済みでもないので全部見ないとダメーって感じになり、Row Goal が有効でも良しなになりません。

set statistics io on

select top(100) * 
from 
  [FactInternetSales] inner join [DimCustomer] on
    [FactInternetSales].[CustomerKey] = [DimCustomer].[CustomerKey]
order by 
  [DimCustomer].[EmailAddress]
  , [DimCustomer].[CustomerKey]
  , [FactInternetSales].[SalesOrderNumber]
  , [FactInternetSales].[SalesOrderLineNumber]
option (use hint ('disable_optimizer_rowgoal') )

;
select top(100) * 
from 
  [FactInternetSales] inner join [DimCustomer] on
    [FactInternetSales].[CustomerKey] = [DimCustomer].[CustomerKey]
order by 
  [DimCustomer].[EmailAddress]
  , [DimCustomer].[CustomerKey]
  , [FactInternetSales].[SalesOrderNumber]
  , [FactInternetSales].[SalesOrderLineNumber]
;

set statistics io off

プランも一緒なので、IO 量も変わらず。

(100 rows affected)
テーブル 'Worktable'。スキャン数 0、論理読み取り数 0、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'FactInternetSales'。スキャン数 1、論理読み取り数 1249、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'DimCustomer'。スキャン数 1、論理読み取り数 984、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

(1 row affected)

(100 rows affected)
テーブル 'Worktable'。スキャン数 0、論理読み取り数 0、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'FactInternetSales'。スキャン数 1、論理読み取り数 1249、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'DimCustomer'。スキャン数 1、論理読み取り数 984、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

(1 row affected)

Completion time: 2023-02-25T13:23:48.6981772+09:00

ソートの先頭列は、[DimCustomer] テーブルを使ってるので、[DimCustomer] を読みながら [FactInternetSales] を繋いでみてくれたら Top が生きてきそうです。
ここで 1個インデックスを追加してみます。

create index [IX_FactInternetSales_CustomerKey] on [FactInternetSales] ([CustomerKey])

[FactInternetSales] で結合条件にしている [CustomerKey] にインデックスを作りました。
先ほどと同じクエリを実行すると、こんな感じのプランに変わります。

Row Goal 無効にしてるクエリは何も変わりませんが、無効にしてないクエリは、プランが変わりました。
[DimCustomer] を並び変えて順番に [FactInternetSales] を繋いでいって、100行になったら終わりみたいなプランですね。
IO 量をみると、

(100 rows affected)
テーブル 'Worktable'。スキャン数 0、論理読み取り数 0、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'FactInternetSales'。スキャン数 1、論理読み取り数 1249、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'DimCustomer'。スキャン数 1、論理読み取り数 984、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

(1 row affected)

(100 rows affected)
テーブル 'FactInternetSales'。スキャン数 32、論理読み取り数 401、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'Worktable'。スキャン数 0、論理読み取り数 0、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'DimCustomer'。スキャン数 1、論理読み取り数 984、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

(1 row affected)

Completion time: 2023-02-25T13:31:33.0897307+09:00

元のプランより、[FactInternetSales] の読み取り数が減っています。1249 => 401
これが Row Goal の効果で読む行減るんちゃうか？ってなってプランが変わって実際に読む量減ってやったーって感じです。

さらにインデックスを追加してみましょう。
今度は、[DimCustomer] の ソートに使用している列をインデックスにします。

create index [IX_DimCustomer_EmailAddress] on [DimCustomer] ([EmailAddress])

このインデックスを追加することで、さっきのプランで [DimCustomer] をスキャン(Clustered Index Scan)してソートしていたのが無くなり、このインデックスをスキャンだけに変わるはずです。
また同じクエリを実行してみましょう。

予想通りソート操作が無くなりました。*1
IO 量は

(100 rows affected)
テーブル 'Worktable'。スキャン数 0、論理読み取り数 0、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'FactInternetSales'。スキャン数 1、論理読み取り数 1249、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'DimCustomer'。スキャン数 1、論理読み取り数 984、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

(1 row affected)

(100 rows affected)
テーブル 'FactInternetSales'。スキャン数 32、論理読み取り数 401、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'DimCustomer'。スキャン数 1、論理読み取り数 110、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

(1 row affected)

Completion time: 2023-02-25T13:38:44.6290791+09:00

先ほどと比べると、[DimCustomer] の読み取り数が、984 => 110 と減りました。

気にせず恩恵受けてますが、賢くなっていってますねー。
ただ、何らかの原因で有効化した場合に遅いプランになる時があったら、その時は無効化してねとのことで。
SQL Server 2014、2016、2017に追加されたクエリ実行プランの KB4051361-オプティマイザー行の目標情報 - Microsoft サポート

*1:DimCustomer の Key Lookup は増えてますが