Azure SQL Data Warehouse でクエリーのパフォーマンス問題を調査する

  • Article

まとめ

SQL Server Data Tools を使って DMV (動的管理ビュー) の情報を確認することでパフォーマンス問題の原因特定が行えます。
幾つかの DMV を順に参照していくことで、
「どのクエリーで処理時間がかかっているか？」
「クエリー内のどのステップで時間がかかっているか？」
「ステップ内のどの部分で時間がかかっているか？どのような実行プランになっているか？」
を確認して問題特定します。

 

[目次]

パフォーマンス調査の準備
1)  最新の SQL Server Data Tools (SSDT) をダウンロード、インストールします
2)  SSDT を起動し [表示] – [SQL Server オブジェクト エクスプローラー] を開きます
3)  SQL Server オブジェクト エクスプローラーで “SQL Server” を右クリックして [SQL Server の追加] から対象の Azure SQL Data Warehouse を追加します

パフォーマンス調査のための情報採取
1)  処理時間のかかっているクエリー(の request_id) を特定します
2)  問題クエリーをステップ単位に掘り下げ、問題ステップを特定します
3)  Compute (実行) ノードのクエリー処理詳細を確認します
4)  DMS (ノード間データ移動) の処理詳細を確認します
5)  実行プランを確認して問題点をより詳細に把握します

 

パフォーマンス調査の準備

SQL Server Data Tools を使って Azure SQL DW の情報を取得し、パフォーマンス問題の原因を特定します。パフォーマンスの情報は DMV (動的管理ビュー) から取得します。

 

1)  最新の SQL Server Data Tools (SSDT) をダウンロード、インストールします

SQL Server Data Tools Preview in Visual Studio 2015
https://msdn.microsoft.com/ja-JP/mt429383

日本語版をインストールする場合は以下リンクからインストーラーをダウンロードします。

ssdt_1

2)  SSDT を起動し [表示] - [SQL Server オブジェクト エクスプローラー] を開きます

ssdt_2

 

3)  SQL Server オブジェクト エクスプローラーで "SQL Server" を右クリックして [SQL Server の追加] から対象の Azure SQL Data Warehouse を追加します

ssdt_3 ssdt_4

 

 

 パフォーマンス調査のための情報採取

基本的に、実行中のクエリーもしくはクエリー実行からそれほど時間経過がないうちに以下手順で調査を行います。情報取得元の DMV は各 Azure SQL DW ノードのメモリ上に保持されており、ノードを破棄 (削除・一時停止) すると同時に基本的には破棄されてしまうためです。

1)  処理時間のかかっているクエリー(の request_id) を特定します

 

対象となるユーザーデータベースに接続し、以下クエリーを実行することで処理時間の最も長い 10 個のクエリーが表示されます。

SELECT TOP 10 *
FROM sys.dm_pdw_exec_requests
ORDER BY total_elapsed_time DESC;

上記クエリーはメモリ上に残っているクエリーの中で最も合計処理時間 (total_elapsed_time) が大きいクエリーを返します。total_elapsed_time の単位はミリセカンド (ms) です。450000 と表示されていた場合、450 秒を示します。今現在実行されているものに絞りたい場合は以下のように WHERE status = 'Running' を追加します。

SELECT TOP 10 *
FROM sys.dm_pdw_exec_requests
WHERE status = 'Running'
ORDER BY total_elapsed_time DESC;

 

上記クエリーの取得結果は以下のようになります。

ssdt_23

resource_class 列を確認することで各クエリーがどのリソースクラスで実行されているかが分かります。Azure SQL DW でのよくあるパフォーマンス問題の 1 つとして、「適切なリソースクラス」が割り当てられていないことがあります。リソースクラスの既定値は smallrc であり、smallrc の場合 小さなリソース(例：メモリ 100MB) しか割り当てられません。リソースを割り当てて処理時間を短くしたい場合は、リソースクラスをより大きなものに変更します。詳細情報は以下リンク先で確認できます。

 

SQL Data Warehouse での同時実行とワークロード管理
https://azure.microsoft.com/ja-jp/documentation/articles/sql-data-warehouse-develop-concurrency/

 

2)  問題クエリーをステップ単位に掘り下げ、問題ステップを特定します

 

Azure SQL DW は複数のノードで構成されます。各ノードでの処理に加えて、ノード間での処理も行われます。それらがステップとして順に実行されて結果が返ってきます。問題クエリーの どのステップがパフォーマンス問題を引き起こしているのか以下クエリーで確認します。

対象クエリー実行完了後にこのクエリーを実行します。以下例で QID10154 となっている部分は 1) で確認した request_id を指定します。

SELECT * FROM sys.dm_pdw_request_steps
WHERE request_id = 'QID10154'
ORDER BY step_index;

 

上記クエリーを実行すると以下のような結果が返ります。step_index が 0 の操作から順に行われていることが確認できます。

ssdt_24

上記結果から以下のことが分かります。

total_elapsed_time ：

各ステップでの処理時間が分かります。まずは、この列の値が最も大きなステップに着目します。

 

location_type ：

処理がどこで行われたものかを示します。Compute は Azure SQL DW の各実行 (Compute ) ノードです。DWU 100 であれば 1台、DWU  2000 であれば 20台配置され、基本的に Compute で時間がかかっているのは、ノードをスケールアウト (DWU を上げれば) 解決する可能性が高くなります。
Control は各 Azure SQL DW データベースで 1 つのみ存在する Control ノード内での処理を示します。DMS は Data Movement Service の略で、ノード間のデータ連携処理を示します。Control や DMS のステップで処理時間がかかってパフォーマンスボトルネックが発生している場合、幾らスケールアウトしても (DWU を上げても)  多くの場合 問題解決しません。 sqldw_1

operation_type ：

各ステップの処理詳細を示します。基本的に各ノードで行われる SQL 操作とノード間のデータ移動である DMS 操作に分けられます。
SQL 操作 (Compute) には、OnOperation、RemoteOperation、ReturnOperation があります。
DMS 操作 (DMS) には、ShuffleMoveOperation、BroadcastMoveOperation、TrimMoveOperation、PartitionMoveOperation、MoveOperation、CopyOperation があります。

SQL  操作がパフォーマンスボトルネックになっている場合、ノードのリソース不足が疑われます。その場合、対象セッションに対するリソースクラスの割当てが適切でなかったり、そもそもリソースが足りない (DWU が小さい)  ことが考えられます。

一方、DMS 操作がボトルネックになっている場合、DWU を上げても追加したノード分だけパフォーマンスが改善されないでしょう。

3) Compute (実行) ノードのクエリー処理詳細を確認します

2) で確認した結果、問題が Compute ノードにある場合、以下のようなクエリーで sys.dm_pdw_sql_requests を参照して実行クエリーの詳細を確認します。

SELECT *
FROM sys.dm_pdw_sql_requests
WHERE request_id = 'QID10236'

sys.dm_pdw_sql_requests を参照することで、ディスク上の各ディストリビューション (全60 ディストリビューション) に対して各ノードから処理した詳細を確認できます。

ssdt_25

この際、各ディストリビューションに対する total_elapsed_time を見ることで、データの偏りが大きいためにパフォーマンス劣化が発生しているかを確認できます。例えば、特定のステップ (step_id) におけるノード (pdw_node_id) から特定ディストリビューション (distribution_id) のみ 処理時間 (total_elapsed_time) が大きかった場合、そのディストリビューションに格納されているデータの件数が大きくなって偏りが発生していることが分かります。

また、distribution_id 列と spid 列の値を確認することで 5) の手順で実行プランを確認できます。

 

4)  DMS (ノード間データ移動) の処理詳細を確認します

2) で確認した結果、問題が DMS にある場合、以下のようなクエリーで sys.dm_pdw_dms_workers を参照して DMS が実行した詳細を確認できます。

SELECT *
FROM sys.dm_pdw_dms_workers
WHERE request_id = 'QID10236'

sys.dm_pdw_dms_workers を参照することで各 Compute ノードで実行されている DMS の処理時間、処理件数、CPU時間などが確認できます。

ssdt_26

row_processed 列を参照して、移動されたデータ件数に大きな偏りが発生していないか確認できます。total_elapsed_time が最も大きなレコードを参照して、どのような処理がボトルネックになっているか深堀りします。distribution_id 列と sql_spid 列の値を確認することで、5) の手順で実行プランを確認できます。

 

5)  実行プランを確認して問題点をより詳細に把握します

以下のように DBCC PDW_SHOWEXECUTIONPLAN コマンドを実行することで XML 形式の実行プランが取得できます。パラメーターの 1 つ目はディストリビューションID (distribution_id)、2 つ目は SPID ( 3) の場合 spid、4) の場合 sql_spid) です。

DBCC PDW_SHOWEXECUTIONPLAN (12, 1287)

実行結果として以下のように「EXECUTION_PLAN」が返されますので、右クリックして拡張子 .sqlplan でファイル保存します。例：

ssdt_27

保存した .sqlplan ファイルを開くことでグラフィカル実行プランが参照できます。テキストエディタで XML 形式の実行プランを確認することもできます。
グラフィカルな実行プランを参照する場合は SQL Server Management Studio をインストールします。

ssdt_28

最新の SQL Server Management Studio は以下から無償でダウンロード、インストールできます。

Download SQL Server Management Studio (SSMS)
https://msdn.microsoft.com/en-us/library/mt238290.aspx

 

参考情報

DMV を利用してワークロードを監視する
https://azure.microsoft.com/ja-jp/documentation/articles/sql-data-warehouse-manage-monitor/

sys.dm_pdw_exec_requests (Transact-SQL)
https://msdn.microsoft.com/ja-JP/library/mt203887.aspx

sys.dm_pdw_request_steps (Transact-SQL)
https://msdn.microsoft.com/ja-JP/library/mt203913.aspx

sys.dm_pdw_sql_requests (Transact-SQL)
https://msdn.microsoft.com/ja-JP/library/mt203889.aspx

sys.dm_pdw_dms_workers (Transact-SQL)
https://msdn.microsoft.com/ja-JP/library/mt203878.aspx

DBCC PDW_SHOWEXECUTIONPLAN (Transact-SQL)
https://msdn.microsoft.com/ja-JP/library/mt204017.aspx