SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Database Experimentation Assistant (DEA) の情報をまとめてみる (2018/6 版)

leave a comment

以前、Database Experimentation Assistant Technical Preview を日本語環境で動作させてみる という投稿を書いたのですが、正式版がリリースされてからの情報がキャッチアップできていなかったので、この機会にまとめてみたいなと。

■DEA で実施できることは？？

Database Experimentation Assistant (DEA) は、SQL Server のデータベースに対しての、A/B テストの実行を支援するためのツールとなります。
このツールで実施できる A/B テストの考え方は次のようになります。

  1. 確認するワークロード (SQL) を実際に SQL Server で発生させ、その最中に実行されたクエリをキャプチャ
  2. 環境 A/B に同一データのデータベースを配置する
  3. 「1.」で取得したワークロードを環境 A/B に対して実行
  4. 実行の結果を比較して、どのような変化が発生したかを確認

これらのアシスタントを実施してくれるのが DEA となります。
以下のような二つの環境で実行したクエリの情報を比較し、どのように性能が変わったかを確認することができます。
image
投稿を書いている時点の最新版は 2.5 となり、MicrosoftR Database Experimentation Assistant? からダウンロードすることができます。
日本語の情報があまりないのですが、海外の MS のブログではいろいろと情報が紹介されており、最初に、次の情報を確認するのがよいかと。

SQLSaturday でも DEA のセッションが実施されたことがあるようですね。

使用方法の基本は DEA 2.0 のころから変わっておらず、チュートリアルも 2.0 ベースのものが多いですが、これらの情報は最新版でも有効ですので、2.0 の情報を確認しておくと、大体は問題ないかと。
最初に記載したように、DEA は、二つの環境に対して同一のワークロードを実行して、比較をすることができるツールです。
ソース (ワークロードを取得する環境) と、ターゲット (ワークロードを再生する環境) は、SQL Server のみサポートとなっていますので、

  • 他 RDBMS から SQL Server を移行する際に、クエリの互換性を確認する

ために使用するのではなく、

  • SQL Server のバージョンアップや設定変更 (インスタンス / DB / インデックス等) により、ワークロードにどのような変化 (パフォーマンスの改善 / 後退 / エラーの発生) があるか

を確認するツールとなっています。
DEA を有効に活用することで、DB の設定変更や、インデックスのメンテナンスを実施した場合、それが既存のワークロードに対してどのような影響を与えるかを「機械的に測定」することが可能となります。
対応している SQL Server のバージョンですが、内部的には「分散再生コントローラー / 分散再生クライアント」を使用していますので、これらのツールのバージョンに依存し、情報については Distributed Replay Requirements で公開されています。
最新の SQL Server 2017 の場合は、SQL Server 2005 以降のトレースを使用することができるようになっていますので、第三世代以降の SQL Server で利用することができるようになっています。
SQL Server 2017 のトレースを取得する場合、CU1 以降を使用しないと、トレースの再生でエラーとなりますので、2017 は累積修正プログラムにも気を付けてください。最初、これを知らなくて、2017 上でうまく動作しなくて四苦八苦しました…。

現時点の対象は、分散再生の仕組みの制約上、「分散再生クライアントのサービスアカウントから接続が可能な、Windows 認証の SQL Server」のみとなっています。
「SQL Database」や、「Managed Instance」に対しても実行できればよいのですが、現状は認証周りの制約があり、トレースの再生先については限定されています。
SQL Server on Linux に対しても実行することはできるのですが、Linux を AD に参加させ、「Active Directory のドメインユーザーを使用した Windows 認証」を使用する必要がありますので、Windows / Linux の SQL Server 間での性能の比較については、認証基盤に注意する必要があります。
分散再生が「SQL Server 認証」をサポートすると、汎用性が高くなりますので、この辺は今後に期待ですかね。
 

■分散再生コントローラー / クライアントを理解する

DEA は単体の機能ではなく、分散再生コントローラー / クライアントが持っている、次の機能をラップし、DEA 特有の機能としてのレポーティング機能を追加したものになります。

  1. 再生用のワークロードのキャプチャ
  2. ワークロードの再生

キャプチャとワークロードの再生の部分については、分散再生コントローラー / クライアントの機能を理解しておく必要があります。
ドキュメントとしては、次の内容を一読しておけば問題ありません。

基本的な構成としては、次のようになっていますので、この構成がイメージできれば問題ないかと。
image
 
分散再生の基本的な情報は次のようになります。
構成ファイル

コントローラー / クライアントは、Windows のサービスとして登録されており、次のような構成ファイルを持っています。

  • 分散再生コントローラー (SQL Server Distributed Replay Controller)
    • C:\Program Files (x86)\Microsoft SQL Server\140\Tools\DReplayController\DReplayController.config
      • ログの記録レベル
  • 分散再生クライアント (SQL Server Distributed Replay Client)
    • C:\Program Files (x86)\Microsoft SQL Server\140\Tools\DReplayClient\DReplayClient.config
      • 使用するコントローラーのサーバー名
      • 作業 / 結果ディレクトリ
      • ログの記録レベル

ポイントとなるのは、「分散再生クライアント」の構成ファイルですね。
コントローラーは、クライアントからの接続要求を待ち受けるので、構成ファイルの意識はあまりしないでも問題ないかと思いますが、クライアントについては、「どのコントローラーに接続を行うか」の設定を構成ファイルに保持しています。
クライアントのインストール時に、コントローラーを指定するのですが、コントローラーを省略した場合「.」(localhost) が指定された状態になります。
コントローラーとクライアントを異なる環境で実行する場合は、使用するコントローラーのサーバー名の制御は意識しておいた方がよいかと。
clip_image001
DCOM の構成
クライアントは DCOM は登録されていないのですが、コントローラー側では「DReplayController」という DCOM が登録され利用されます。
image
分散再生のサービスである、次の二つのサービスのサービスアカウントは、上記の DCOM に対してアクセスできる必要があります。

  • SQL Server Distributed Replay Controller
  • SQL Server Distributed Replay Client

必要となる権限については、Controller および Client のサービス アカウントの変更 で確認できます。
実際には次のようなアクセス権を検討する必要があります。

  • Dreplay.exe を明示的に実行して、対話型でコントローラーに接続するユーザー
    • ローカルからの起動 / リモートからの起動
    • ローカルからのアクティブ化 / リモートからのアクティブ化
    • ローカルアクセス / リモートアクセス
  • 分散再生クライアントのサービスアカウント
    • ローカルからの起動 / リモートからの起動
    • ローカルからのアクティブ化 / リモートからのアクティブ化
    • ローカルアクセス / リモートアクセス

 
コマンドラインツール
分散再生については、コマンドラインツールとして「DReply.exe」が提供されています。

コントローラーとクライアントの接続の確認については、次の手順で実行できます。

  1. コントローラーで「C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\Dreplay.exe /status」を起動します。
    (140 の個所については、SQL Server のバージョンによって異なります。上記バージョンは 2017 のものです)
    このタイミングで「SQL Server Distributed Replay Controller」サービスが起動し、クライアントからの受付の要求待ちの状態となります。
    image
  2. この状態で、「SQL Server Distributed Replay Client」サービスを起動します。
  3. コントローラーとクライアントが正常に通信できる状態であれば「Dreplay.exe /status」のコンソールに次のようにクライアントの登録が表示されます。
    image

 
ここまで終われば DEA で接続をするための準備は完了です。
コントローラーとクライアントを別の環境に構築した場合などは、次の情報でファイアウォールの通信についても確認するとよいかと。

 

■DEA を使ってみる

使用方法については、DEA 2.0: How to Use Database Experimentation Assistant を確認してもらえればよいかと。
ポイントとなるのはワークロードの取得の方法です。
トレースするワークロードを取得する際、

  • クエリが 30 回は実行されるようにする

のがポイントとなります。
DEA でレポートを取得する際に、改善されたかどうかの判断対象となるのは、30 回以上実行されているかどうかが一つの基準となります。

Cannot Evaluate: the sample size for the query is too small for statistical analysis; in order for A/B testing analysis DEA requires the same queries to have at least 30 executions on each target.

30 回実行されていないようなクエリについても比較はされますが、「Cannot Evaluate」というカテゴリの対象となります。このカテゴリでも比較はできるのですが、改善されたかどうかの判断がひと目ではできなくなりますので、ワークロードを取得する際には、「同様のクエリが複数回使われるた状態のキャプチャを取得する」ことを意識しておくことがポイントです。
トレースのキャプチャと再生については、分散再生の仕組みが理解できていれば問題はないのですが、「トレースを再生した結果を用いてレポートを作成する」場合は、日本語環境では注意が必要です。
私が DEA を紹介する機会は何回かあったので、その中で紹介するようにはしているのですが、DEA でレポートを作成する際には、「レポート作成に使用する SQL Server のインスタンスに接続をして作成」する必要があります。
このとき、「DEA を起動しているユーザーからレポート作成用の SQL Server に接続」されるのですが、「DEA を起動しているユーザーの SQL Server のログイン」の言語設定については「English」を設定してください。
image
DEA で再生結果の比較をする際には、ベースとなる DB の bacpac をインポートして、比較結果を格納する DB を作成しているのですが、日付の形式の問題で、言語の設定が日本語のログインで処理を実行した場合、レポートの作成が失敗します。
日本語の SQL Server を使用していても、レポートを作成するログインの言語設定が「English」であれば処理を正常に実施することができますので、DEA の「NEW REPORT」を実行する際のログインの言語設定は気を付けてください。
(レポートの作成は、DEA を起動しているユーザーを使用して、レポート作成用の SQL Server に「Windows 認証」により接続が行われ実行されます)
image
 

■DEA のレポートを確認する

DEA のレポートは、以下の画像のように「分散再生で再生された二つの実行結果のトレースを比較」することで作成されます。
image
「Target 1 の結果が、Target 2 ではどのように変化したか」を機械的に確認することができるのが、このツールの特徴となります。
以下のレポートは、

  • Target 1 の状態からチューニングを実施し、DEA により効果を確認したもの

となっています。
image
レポートは、次の二つで構成されています。

  • By Execution Count : 同様のクエリのハッシュ単位でサマリーして、カテゴリをカウントしたもの
  • By Distinct COunt : カテゴリでサマリーして件数をカウントしたもの

 
DEA のレポートでは「Threshold」(閾値) を設定することができ、どの程度、差があるものを改善 / 後退とするかを設定することができるようになっています。
影響をクエリ単位の処理時間からも確認することができますので、Target 1 から変更を行った、Target 2 で処理がどのように変化したかを、詳細に確認することができます。
image
image
この際、実行回数が 「30回未満のクエリ」はカテゴリが、「Cannot Evaluate」として検出されます。
このカテゴリとして検出されたものについても処理時間の比較は出来ますので、実行回数の少ないクエリについては、改善の度合いを機械的に判断するのではなく、手動で判断するようなことも検討する必要があります。
image
 
DEA は性能の改善だけでなく、SQL Server のアップグレードの影響にも利用することができるようになっています。
Target 1 のトレースでは、エラーになっていないクエリが、Target 2 でエラーになった場合、次のように「Error」のカテゴリとしてクエリが検出されます。
image
エラーのレポートではどのクエリがエラーとなったかを確認できますので、「アップグレードによって動作しなくなったクエリ」を確認することが可能です。
SQL Server のアップグレードだけでなく「互換性レベルの変更による影響」の観点でも、DEA を活用することもできます。
image
DEA は、実際にクエリを再生することで、結果を取得し、処理結果のレポートを作成するツールであるため、「ワークロードをキャプチャした際に使用したデータベースと同様の環境」を準備するところから検討をする必要があります。
しかし、昨今は、SQL Server は各種クラウドプラットフォームで、IaaS タイプの環境を瞬時に作成することができるようになっており、「検証をする環境の準備を行うためのハードル」は一昔前より大幅に下がっていますので、「検証する環境を準備することが前提となるツール」を利用するための環境の準備は、すぐに実施することができます。
(大容量のデータベースでテストをする場合の検証用データベースの移行をどうするか等の検討は必要ですが)
初期準備に手間はかかるかもしれませんが、ある程度の頻度で変更が発生する環境では、このようなツールを利用することで、効率よくソフトウェアの利用を行いたいものですね。
データベース観点での CI/CD ということも昨今は情報として流れてくることもありますので、こういう製品を使いこなし、製品の利点を最大限に生かした環境の維持や、効率化を検討していきたいものです。

Share

Written by Masayuki.Ozawa

6月 10th, 2018 at 3:50 pm

Posted in SQL Server

Tagged with ,

«
»

Leave a Reply