Database Performance Analyzer [DPA] でSQL Serverのパフォーマンスを見つけ、分析し、最適化へ

投稿日: 2022年2月17日 作成者: climb
このエントリーをはてなブックマークに追加
LINEで送る

問題点


SQL Serverのプロとして、アプリケーションのパフォーマンスは最優先事項の一つです。 私たちのアプリケーションには数多くの非効率性があり、社内外のユーザの生産性を維持するために常に努力しています。 私たちのチームは一日中リアルタイムのデータを収集し、多くの指標を得ています。これは素晴らしいことですが、最大のパフォーマンス向上をもたらす問題を特定することは非常に困難です。 私たちはしばしば、思いつきでアイデアを出しています。 コードリリースのプロセスに時間をかけることで、測定可能なパフォーマンスの向上が得られることを知る必要があります。 アプリケーションの非効率性を是正し、SQL Server DBAチームが最も重要な問題に労力を集中できるようにするには、どうすればよいでしょうか？

解決策


サーバ、データベース、オブジェクトの各レベルで大量のパフォーマンスメトリクスが存在する中で、データの相関、集約、傾向を把握することは難しく、日常的に対処すべき項目は増え続けています。 さらに一歩進んで、Microsoft SQL Serverのパフォーマンス問題に真に優先順位をつけることは、非常に困難です。 多くの場合、最も注目されるパフォーマンス問題は、一般的に多くの苦情に基づいています。 これは必ずしも最大の問題ではなく、別の問題である可能性があります。

SolarWindsでは、長年にわたり、応答時間分析に焦点を当てたSQL Serverのパフォーマンス問題の発見と分析によって、世界中の企業をサポートしてきました。 Database Performance Analyzer (DPA)の最新版では、データベース管理者がデータベースエンジンのワークロードを最適化できるように、ワークロード全体を分析することで、さらに一歩進んだサービスを提供しています。 この最適化は、日々のワークロード全体の分析に基づき、クエリやテーブルレベルでの最大の問題点を特定し、変更を正当化するための推定パフォーマンス効果とともに具体的な提案を行います。 ここでは、クエリ・パフォーマンス分析とテーブル・チューニング・アドバイザーについて説明し、データベース・パフォーマンスの最適化を効率化する方法を紹介します。

SolarWinds Database Performance Analyzerのクエリパフォーマンス分析では、待ち時間が最も長いクエリに焦点を当て、最も問題のあるクエリの部分を掘り下げ、コードを含めてクエリを改善するための推奨事項を得ることで、クエリのパフォーマンスを効率的に改善する手段を提供します。 これにより、以下のようなパフォーマンス問題をトラブルシューティングするための最も重要な情報を、インテリジェントな方法で、できるだけ早くDBAの前に提示します。

Database Performance Analyzerのクエリ・パフォーマンス分析


クエリパフォーマンス分析では、待ち時間が最も長いクエリに焦点を当て、最も問題のあるクエリの部分を掘り下げ、コードを含めてクエリを改善するための推奨事項を得ることで、クエリのパフォーマンスを効率的に改善する手段を提供します。 これにより、以下のようなパフォーマンス問題をトラブルシューティングするための最も重要な情報を、インテリジェントな方法で、できるだけ早くDBAの前に提示します。

  • SQL データベース全体のワークロードで最もコストのかかる SQL クエリとストアドプロシージャ
  • クエリ最適化を開始するための各 SQL クエリの最も高い待機タイプ
  • 焦点を当てるべき非効率的なクエリプランのステップ
  • ボトルネックを特定するための、スタック内の多数の階層におけるパフォーマンスメトリクスの相関関係
  • 日次ベースでコードを実行し、待機タイプへの影響を判断


このプロセスは、DPA にログインして 30 日間のトレンドページを確認することから始まります。このページでは、個々のクエリを掘り下げて、関連するコード、そのメトリクス、推奨事項を確認することができます。

30日間のトレンド表示からクエリハッシュをクリックすると、そのクエリの過去30日間のすべてのパフォーマンス指標が表示され、時間枠の一貫性が保たれます。新しいクエリパフォーマンス分析ビューには、折りたたみ/拡張可能な複数のセクションがあり、最も重要なデータを強調して表示することができます。このビューのデータは、いくつかの方法で分かりやすく表示されます。

  • クエリの主な待ち時間に関連するリソースメトリクスがある場合、それらはメトリクスセクションに表示されます。
  • このクエリが重大なブロッキング（ブロッカー、ブロッキー、デッドロックのいずれか）に関与していた場合は、ブロッキングセクションが展開されます。
  • 設定した時間枠内でオプティマイザがこのクエリに複数のプランを使用した場合、プランセクションが拡張されます。

上の2つのセクションは、このクエリの分析中に発見された事項のために用意されています。

  • クエリアドバイザ （Query Advisor）- 推奨されないインデックス（クラスタ化されたインデックス、クラスタ化されていないインデックス、カバーリングインデックス）、重要な待機タイプ、異常な使用パターン（実行回数が多い期間）、where節の欠落、テーブルのフルスキャンなど。
  • Table Tuning Advisors – このクエリが1つ以上のテーブルに対して非効率なワークロードを引き起こしていたことを示します（詳細は後述します）。

クエリ自体に変更がないにもかかわらず、より多くの待ち時間が発生している場合は、このビューで提供される他のセクションの1つが、以下のような理由を説明するのに役立ちます。

  • 実行回数の増加、または物理的な読み取りと論理的な読み取りの比較（統計）
  • プランの変更(プラン)
  • システムリソースの圧迫(メトリクス)
  • 同時実行（ブロッキング）

下の例では、選択したクエリの主なアクティビティが「メモリ／CPU」であることを示しています。このため、メモリとCPUのメトリクスがメトリクス・セクションに表示されています。しかし、優勢な待ち時間がディスク関連（例えばpageiolatch_ex）であったならば、ディスクのレイテンシーやその他の関連メトリクスがビューに取り込まれていたでしょう。

画面右側の「プラン(Plan)」リンクをクリックすると、クエリプラン全体と関連するコスト、そしてページ下部の「プランアドバイス(Plan Advice)」を確認することができます。プランアドバイスには、推奨事項に従った場合のメリットを正当化するための関連する影響も含まれています。

クエリの分析を続けるために、チャートレベルのオプションで、特定のレポートに基づいて関連するカウンターを追加することができます。 また、トップチャートはスティッキーで、各セクションの上に表示され、30日トレンドチャートのデータとの相関性を高めます。

また、特定のチャートに統計情報を追加できるだけでなく、66個の個別指標を追加して、作業量に応じてチャートをカスタマイズすることもできます。


さらに、クエリパフォーマンス分析では、クエリレベルでのパフォーマンス指標と関連する推奨事項のほか、「SQL TEXT」タブをクリックしてクエリに関連するT-SQLコードを確認したり、「SUPPORTING DATA」タブをクリックしてクエリプランに対応するデータベース、ユーザ、ファイル、マシン、プログラムなどを確認することができます。

Query Performance Analyzer はどのように役立ちますか？


Query Performance Analyzer は、以下のように SQL Server 担当者を支援します。

  • 最も重要なパフォーマンス情報をインテリジェントに表示し、DBA によるパフォーマンスチューニングプロセスを効率化
  • ワークロードに最も関連するメトリクスを確認するためにインタフェースをカスタマイズ可能
  • クエリパフォーマンスの 30 日間のトレンドを表示し、クエリプランの詳細にまで掘り下げることが可能。
  • コードのコピー、ペースト、最小限の更新で、パフォーマンスの改善を迅速に実行
  • パフォーマンスに関する推奨事項の正当化

・クエリの非効率性
・インデックスのチューニング
・インデックスの欠落
・述語の問題
・データタイプの不一致
・古い統計情報
・大きなテーブル

  • カスタマイズ可能なビジュアルでクエリパフォーマンスの全体像を提供

 Database Performance AnalyzerのTable Tuning Advisors

DPAがすでに収集しているデータに基づいて、最新リリースの2つ目の新機能は「テーブル・チューニング・アドバイザー」と呼ばれています。 これらの推奨事項は、DPAがすでに収集している膨大なデータの副産物です。 DPAはすでに膨大な量のデータを収集していますが、現在は、テーブルレベルでのパフォーマンスへの影響や、特定の推奨事項がお客様の環境の多数のプロセスにどのように影響するかを理解するために、待機タイプを毎日分析しています。 これらはすべて、オブジェクトレベルで集約された独自のアルゴリズムにより、インデックスの問題、データタイプの不一致、WHERE句の述語の問題などを把握するために、監視対象のSQL Serverインスタンスに負荷をかけることなく実現されています。 これによりDBAは、ユーザに大きな影響を与える前に、パフォーマンスの問題に積極的に取り組むことができます。

DPAのTable Tuning Advisorsは以下のような支援を行います。

・テーブルの最も非効率的なクエリの特定
・多数のクエリに影響を与えるテーブルレベルの問題の関連付け
・不足しているインデックスの特定
・読み込み回数が多く、返されるデータの割合が少ないクエリの発見。これはWHERE句の条件に問題がある可能性があります。
・WHERE句の述語に注目し、パターンや非効率性を判断する。
・古くなった統計情報のハイライト
・「churn 」と呼ばれる挿入、更新、削除のトランザクションの割合を検出する。
・ユーザエクスペリエンスに悪影響を与える前にパフォーマンス問題を解決することで、組・織内のプロアクティブなDBAとなる。

DPA の Table Tuning Advisor にアクセスするには、30 日間のトレンドページで、画面右上の TUNING タブをクリックします。

DPA TUNING タブでは、左側に Query Advisors が、右側に Table Tuning Advisors が表示されます。 画面左上のドロップダウンボックスをクリックして、過去30日以内の日付を選択すると、過去の推奨事項を確認することができます。 Query Advisorsは、上記の新機能と同じですが、優先順位をつけてデータにアクセスするための新しい手段です。 右側は新しいTable Tuning Advisorsで、テーブルが優先順に表示され、アラートが最初に表示され、次に情報メッセージが表示されます。

この例では、LineItemの推奨項目をクリックすると、左に非効率的なSQL文の優先順位をつけたリストが表示され、右に文ごとの詳細が表示されます。 このテーブルでは、1つのクエリプランがステップ32、17、62で複数回テーブルスキャンを行っており、l_orderkeyとl_taxにインデックスを付けることが有効です。 また、同じインデックスで改善される7つの追加のSQL文を見ることができます。 ワークロードをテーブルレベルで分析し、クエリプランを解析し、非効率なステップを分解して推奨事項を正当化することで、1つのインデックスセットを作成して多数のクエリを修正し、多数のアプリケーションやプロセスを改善することができます。

また、このインターフェースでは、時間経過による変化を理解するために、表の過去の推奨値を確認することができ、インターフェースの左上にある日付のドロップダウンボックスで選択することができます。

インデックスの推奨事項の右側にある「Show index DDL」オプションをクリックすると、対応するコードが表示された次の画面がポップアップ表示されます。 このコードをクリップボードにコピーすることで、推奨インデックスのテストを開始し、DPAによる影響予測とユーザのテスト環境で見られる改善効果が一致するかどうかを検証することができます。

テーブル単位での分析の最後に、パフォーマンスの推奨値と正当性の下に、一般的なメタデータ、平均データ回転数（テーブルの挿入、更新、削除のトランザクションの割合）、既存のインデックスとカラムとそのデータタイプ、ヌル構成、デフォルト値などの「現在のテーブル情報」があります。 これらの情報は、他のツールを使用することなく、テーブルをレビューする際に役立ちます。

Table Tuning Advisors はどのように役立つのですか？

Table Tuning Advisors は以下のように SQL Server プロフェッショナルを支援します。

●ユーザコミュニティに大きな問題が発生する前に、パフォーマンス問題に積極的に対処する
●パフォーマンス改善のためにアラートの優先順位付け
●テーブルレベルの非効率性を集約し、1回の修正で多くのプロセスを改善できる可能性
●コード変更を正当化するためのパフォーマンス改善の見積もり
●テストと実装のプロセスを効率化するためのコードのコピー、ペースト、更新
●定期的に行われるインデックスの再構築と比較して、インデックスに関連するメリットを判断するために、データ・チャーンの割合を理解する


Database Performance Analyzerを使い始めるには？


１．アプリケーションに対するSQL Serverコードの変更を特定、診断、検証、テスト、導入するのに必要な時間を考えてみてください。
1-1.  新しいQuery Tuning AdvisorsとTable Tuning Advisorsを使えば、DPAはこのプロセスに優先順位をつけて大幅に効率化し、ビジネスの中核となる取り組みに集中することができます。
２.サードパーティのアプリケーションでパフォーマンスの問題が発生し、ユーザを助けるための選択肢がないとお悩みの方も、あきらめないでください。
2-1.  DPAは、サードパーティ・アプリケーションの非効率性を特定し、ユーザを悩ませているパフォーマンス問題の解決策を提供するためにベンダーと協力する手段を提供します。
3. DPAは、本番環境のパフォーマンス問題を迅速に特定して修正したり、QA環境のパフォーマンス問題を組織に影響を与える前に特定したりすることができるため、お客様の環境で実際に使用してみてください。
3-1. DPAの詳細・デモについては、お問合せください。
3-2. DPAの評価版でユーザ自身の環境でお試しください。
４．DPAのQuery Performance AnalyzerとTable Tuning Advisorがチームにどのように役立つかを説明する。
4-1. システムで発生している待ち時間に基づいて、インスタンスのワークロード全体でパフォーマンスの問題を優先する。
4-2. ユーザの環境に合わせて完全にカスタマイズ可能な30日間のパフォーマンスメトリクスの傾向を確認
4-3. 根本原因の問題を解決するために提案された修正案で、パフォーマンスの改善を正当化する
4-4. プラグ＆プレイコードで実装を効率化
4-5. これらの新機能のためのエージェントレスアーキテクチャによるパフォーマンスオーバーヘッドの排除、シンプルな新アルゴリズムによるDBAの迅速な意思決定へのデータ提供
5.  DPAを使用した後は、その結果をチームで共有し、SQL Serverアプリケーションのパフォーマンスを根本的に改善するための次のステップを決定します。
5-1. DPA は、 Windows および Linux オンプレミスのデータベースインスタ ンス SQL Server のためだけのソリューションではないことを覚えておいてください。DPAは、Oracle、MySQL、PostgreSQL、クラウド（Azure、Azure SQL Database、Amazon AWS、RDS、Auroraなど）、VMwareなどをサポートしています。

関連したトピックス