Azure SQL Data Warehouse (SQL DW) の分散クエリプランをひも解いてみよう
SQL Server を利用するアプリ開発やデータベース管理に携わる方々の中には、SQL Server のチューニング プロセスの中で、クエリー プランを確認してチューニング指針を立てたというご経験のある方が多いのではないでしょうか。
そのプロセスでは SQL Server Management Studio (SSMS) を用いてクエリ プランを表示させ、アクセス パスを確認することが一般的だったと思います。
具体的には、SSMS のツール バーで "推定実行プランの表示" や "実際の実行を含める" ボタンを押下しておくことでクエリ プランを簡単にグラフィカルに表示させることができました。
Azure SQL Data Warehouse （以降 Azure SQL DW と略します）の場合には、このクエリプランの確認方法がちょっと変ってきます。
Azure SQL DW には EXPLAIN という Transact-SQL ステートメントが用意されています。
Transact-SQL ステートメントなので SSMS で実行することもできるのですが、EXPLAIN は結果を XML テキストとして返すので、これを表示・分析するには SQL Server Data Tools か Visual Studio で実行するのが便利です。ちょっと残念なのは、クエリ プランをグラフィカルに表示させることはできません。
下のスクリーン ショットでは、1 行目 に EXPLAIN ステートメントがあり、そのあとにクエリプランを表示させたい SQL ステートメントが続いています。ところで、EXPAIN で表示するクエリ プランは、正確には Azure SQL DW の分散クエリのクエリ プランになるのですが、この分散クエリという言葉の意味については後ほど説明しましょう。
さて、結果ペインの XML をクリックすると、XML エディタが表示されます。
上のスクリーン ショット 12 行目に total_cost としてクエリの推定コスト 3846 が表示されています。SQL Server と同じく、コストの値に単位はなく、大きいか小さいかの相対指標として用います。この値を小さくするチューニングを施すことで、実際のクエリの実行時間も短縮されることになります。
| <dsql_operations total_cost="3846.37034857954" total_number_operations="29"> |
またこの行には、total_number_operations として、クエリが 29 の処理から成り立っていることも表示されています。この処理は XML エディタで operation_type を検索するとハイライト表示させることができます。
チューニングすべきポイントに到達するように、重要ではない処理は XML エディタ上で折りたたんでしまいましょう。
まず RND_ID という処理では、一時オブジェクトを作成する際のランダム ID を取得しているだけなので折りたたんでしまいましょう。
また operation_type="ON" という処理は、これから処理される Transact-SQL ステートメントを示しますが、ほとんどの場合一時テーブルを作成するクエリ ステートメントを示しているだけなので、やはり折りたたんでしまいましょう。（上のスクリーン ショットだと 19行目に CREATE TABLE ステートメントが見えています）
そうすると見やすくなったので、残った処理の中から operation_cost の大きい処理を探します。
今回の例では、上のスクリーン ショット 65 行目の処理の operation_cost 値が 3840 を示しており、全体のクエリコスト 3846 の 99.8% をこの処理に費やしていることが分かります。
さて、これでチューニング対象が決まりました。
チューニングを進めるには、上のスクリーン ショット 64 行目の operation_type="BROADCAST_MOVE" の意味を知る必要があります。
Operation_type に現れる処理の中で、クエリ コストが大きくなる可能性のある処理を下表の一覧にまとめました。BROADCAST_MOVE の説明として、"結合処理のためにテーブルのレプリカを全ての計算ノードに作成します" とありますが、これはいったいどういう意味でしょうか?
| 処理の表記 | 説明 |
| SHUFFLE_MOVE | 結合処理のためにそれぞれの計算ノードに分散したデータを特定の列のハッシュ計算結果にしたがって再配置します |
| PARTITION_MOVE | COUNT(*) のような集計結果を計算ノードから制御ノードへ渡します |
| BROADCAST_MOVE | 結合処理のためにテーブルのレプリカを全ての計算ノードに作成します |
| TRIM_MOVE | 外部結合に必要なデータを計算ノードにコピーします |
| MOVE | いったん制御ノードに集められたデータが、後続の処理のために計算ノードに戻されます |
| ROUNDROBIN_MOVE | データをラウンド ロビンで再配置します |
この意味を理解するために、そして上表の説明を理解するために、Azure SQL DW のアーキテクチャにつて見てみましょう。
https://azure.microsoft.com/ja-jp/documentation/articles/sql-data-warehouse-overview-what-is/
Azure SQL DW は、1 つの制御ノードと複数の計算ノードから構成される MPP アーキテクチャを採用しています。この MPP アーキテクチャを採用することで、計算ノードを追加するだけで、それに比例するコンピュート パワーを使えるようになりました。
いっぽう、テーブルのデータはというと、それぞれの計算ノードが担当するストレージに分散された状態になるので、各計算ノードで実行されるクエリが、必要に応じて相互にデータを融通しあうことがあります。（クエリが分散して処理される形態を分散クエリと呼びます）
データを融通しあうには、ノード間のデータ移動と一時テーブルへの I/O が発生するので、その量が大きいとき、それはオーバー ヘッドとなってしまいます。なお、テーブルのデータが分散されるそれぞれのストレージを、ディストリビューションと呼びますので覚えておいてくださいね。
ここまでくると、operation_type="BROADCAST_MOVE" が、そのデータの移動が発生している状態であることにもう気づかれたのではないでしょうか?
上表の説明では、「結合処理のためにテーブルのレプリカを全ての計算ノードに作成」とあるので、今問題にしているクエリの結合処理を確認してみましょう。
上の XML エディターのスクリーン ショット 66 行目から 71 行目をよく見てみてみると、一時テーブルの列名に s_suppkey や n_nationkey といった列名がみられます。
| SELECT [T1_1].[s_suppkey] AS [s_suppkey]FROM (SELECT [T2_1].[s_suppkey] AS [s_suppkey]FROM [tpch].[dbo].[supplier] AS T2_1INNER JOIN[tempdb].[dbo].[TEMP_ID_15] AS T2_2ON ([T2_1].[s_nationkey] = [T2_2].[n_nationkey])) AS T1_1 |
これらの列名は、それぞれ supplier テーブルと nation テーブルの列名から採られているので、この二つのテーブルの結合処理でデータの移動が多く発生しているのではないかと推測できます。
では、これらのテーブルの分散方式はどうなっているのでしょうか?
テーブルの分散方式は、CREATE TABLE ステートメントの WITH 句の中で、ハッシュ （HASH） なのかラウンド ロビン （ROUND_ROBIN） なのかを指定します。下の表にその特徴をまとめました。
| 分散方式 | 特徴 |
| HASH |
|
| ROUND_ROBIN |
|
既存のテーブルの分散方式は、SSMS や Visual Studio (または SSDT) で簡単に確認することができます。オブジェクト エクスプローラーでテーブルを展開すると、アイコンに 2 種類あるのに気づきます。左上から右下にかけて帯が見えるのがラウンド ロビンテーブルで、上下に分かれたように見えるのがハッシュ テーブルです。
supplier テーブルも nation テーブルもラウンド ロビンで分散されているのが分かりますね。
ラウンド ロビンの特徴に「列値に関係なくデータを順番に分散」とあること、そして、クエリ コストの大きかった BROADCAST_MOVE の説明に「結合処理のためにテーブルのレプリカを全ての計算ノードに作成」とあることから、二つのテーブルの分散方式をラウンド ロビンからハッシュへ変更することで、データの移動を小さく抑えられないでしょうか?
実際、HASH の説明には「ハッシュ アルゴリズムによって同じ値にハッシュされた行は同じディストリビューションに配置」とあるので、結合列のデータで同じ値をもつ行が同じディストリビューションにみつかるように感じますね。
では supplier テーブルと nation テーブルを ハッシュ分散に変更した supplier_hash テーブルと nation_hash テーブルで EXPLAIN を表示させましょう。
上のスクリーン ショットのとおり total_cost は 3846 から 30.46 に下がりました。
下のスクリーン ショットから、問題だった部分の operation_cost が 3840 から 30.36 に下がっているのが分かります。
実際のクエリ実行時間を計測してみると、ハッシュ テーブルに変更する前が 1 分 52 秒だったのに対し、変更後は 42 秒にまで縮まりました。（推定クエリコストの値は、クエリ処理の実測値と直接比例関係にありませんので、その点ご注意ください）
いかがでしたか?
ちょっとややこしかったかもしれませんが、EXPLAIN の内容をひも解くことで、クエリの実行時間をチューニングできることが分かりました。
今回の例ではデータの移動を改善することでクエリをチューニングしましたが、実際に計算ノードで処理されるクエリのプランを確認するのは、また次の機会にいたしましょう。
N