Cloud SQL - SQL Server のパフォーマンス分析とクエリ調整

※この投稿は米国時間 2022 年 7 月 15 日に、Google Cloud blog に投稿されたものの抄訳です。

以下のブログ投稿では、データベース管理者が Cloud SQL for SQL Server の調整と最適化に使用できる一般的なパフォーマンス分析ツールやテクノロジーをご紹介します。各セクションで、一般的なパフォーマンス上の問題と、こうした問題を分析、対処、修正するためのツールや戦略について説明します。このブログを読み終えた後に、各セクションで説明されているツールやプロセスを非本番環境のデータベースに適用いただくと、これらがデータベースの管理と最適化にどのように役立つか理解を深めることができますのでぜひご検討ください。また、一般的なパフォーマンスの問題と、ここで説明するツールやプロセスを使用したトラブルシューティングおよび修正方法について説明する続編のブログも投稿する予定です。

1. はじめに: Cloud SQL for SQL Server インスタンスに接続する。

Cloud SQL への接続で最も一般的なユースケースには、ノートパソコンからの VPN 経由での接続と、GCP でのジャンプホストの使用があります。

SQL Server のデータベース管理者で、SQL Server Management Studio（SSMS）を使用して VPN 経由でローカルのノートパソコンから接続する方は、このクイックスタート ドキュメントを確認して、プライベート IP アドレスで構成された Cloud SQL インスタンスに接続してください。

なお、データベース管理者のなかには、単一のジャンプホストに接続して複数の Cloud SQL for SQL Server インスタンスの一元管理を希望する方もいらっしゃるかもしれません。その場合は、Google Compute Engine（GCE）VM がプロビジョニングされ、データベース管理者はリモート デスクトップ プロトコル（RDP）ツールを使用してジャンプホストに接続し、Cloud SQL データベースを管理します。Cloud SQL への接続に関するオプションの包括的なリストについては、Cloud SQL for SQL Server インスタンスを接続するをご覧ください。

2. アクティビティ モニタリング: 何が実行中かを把握する。

緊急のサポートへの問い合わせに対応する際、データベース管理者は現在インスタンス上で何が実行中なのかを即座に判断する必要があります。これまで、トリアージと分析をサポートするためにデータベース管理者は、sp_Who や sp_Who2 など、システムのストアド プロシージャに依存してきました。

現在、何が実行されているかを判断するには、Adam Machanic 氏の sp_WhoIsActive ストアド プロシージャのインストールを検討してください。現在実行中のステートメントを表示し、計画の詳細を取得するには、以下のステートメントを使用してください。次の例では、プロシージャ sp_WhoIsActive が dbtools データベースの dbo スキーマにインストールされています。

EXEC dbtools.dbo.sp_WhoIsActive @get_plans=1

また、Brent Ozar 氏の sp_BlitzFirst ストアド プロシージャもご覧ください。SQL-Server-First-Responder-Kit に含まれています。例については、ドキュメントをご確認ください。

また、Brent Ozar 氏の sp_BlitzFirst ストアド プロシージャもご覧ください。SQL-Server-First-Responder-Kit に含まれています。例については、ドキュメントをご確認ください。

3. SQL Server クエリストアを使用してクエリを最適化する。

クエリの最適化は、データベース管理者の毎週のチェックリスト項目として、積極的に実行するのが最適です。その際に役立つのが SQL Server クエリストア機能です。データベース管理者にクエリプラン、履歴、有用なパフォーマンス指標を提供します。SQL Server クエリストアを始める前に、次の Microsoft SQL Server のパフォーマンス監視に関する記事「クエリストアを使用してパフォーマンスを監視する」を確認することをおすすめします。クエリストアはデータベース レベルで有効化されるため、各ユーザーのデータベースで有効化される必要があります。クエリストアを有効化する方法については、以下の例をご覧ください。

ALTER DATABASE <<DBNAME>>

SET QUERY_STORE = ON (WAIT_STATS_CAPTURE_MODE = ON);

クエリストアを有効化したら、SSMS を使用してクエリストアの構成を確認します。データベースを右クリックし、クエリストアのプロパティを表示します。プロパティと設定の詳細については、Microsoft の記事「クエリストアを使用してパフォーマンスを監視する」をご確認ください。

スクリーンショット: AdventureWorksDW2019 データベースでクエリストアを有効化する。

ビジー状態のインスタンスでクエリストアが有効化されると、通常、数分以内にクエリデータを確認できるようになります。または、いくつかのテストクエリを実行して、分析用のデータを生成します。次に、クエリストア ノードを開き、利用可能なレポートを検索します。

下の例では、「Top Resource Consuming Queries」を選択しました。次に、合計時間でテーブルをソートし、リソースを最も消費するトップクエリの実行プランを確認しました。実行プランを確認すると、テーブル スキャンが発生していることに気づきました。その後、すべての列を選択するのではなく、特定の列を選択するようにクエリを修正するようユーザーに依頼し、必要な列を含めるために基礎となるテーブルにクラスタ化されていないインデックスを追加することで問題を修正できました。

インデックス変更の例:

クエリを時系列で追跡するには、クエリを右クリックして、[Track Query] を選択します。

インデックス変更前と変更後のプランは以下に示すとおりです。

両方のプランを選択し、[Compare Plans] を選択すると、プラン変更前と変更後が表示されます。

SQL Server クエリストアは、Cloud SQL のデータベース管理者が T-SQL ステートメントを取得、分析、調整するために利用できる便利な組み込みのパフォーマンス調整ツールです。クエリストアが SQL Server データベースの管理と最適化にどのように役立つか、時間をかけて確認する価値があります。

4. インスタンスとデータベースの健全性、構成、パフォーマンスを分析する。

SQL Server コミュニティでは、SQL Server インスタンスやデータベースを確認および分析するための無料ツールやスクリプトを多数提供しています。人気のあるスクリプト リソースを以下に示します。

Glen Berry 氏の SQL Server Diagnostic Queries は、移行の計画時にオンプレミス インスタンスを評価したり、GCP でデータベースを実行した後に構成やパフォーマンスを分析したりするのに便利です。SQL Server Diagnostic Queries の使用方法や結果の解釈のサポートについては、Glen Berry 氏の YouTube 動画をご覧ください。

Brent Ozar 氏の SQL-Server-First-Responder-Kit も、SQL Server インスタンスを迅速に評価して分析するために使用される人気の高いコミュニティ ツールです。Cloud SQL for SQL Server はマスター データベースへのオブジェクトのインストールをサポートしていないため、スクリプト用に別のデータベースを作成することをおすすめします。多くのデータベース管理者は、ツール データベース（dbtools など）を作成し、そのデータベースにスクリプトやプロシージャをインストールしています。キットのインストールと使用に関するヒントは、ドキュメントおよび Brent Ozar 氏のハウツー動画をご確認ください。

5. 構成やパフォーマンスを調整してロックやブロックを削減する。

ロックやブロックに関連するパフォーマンスの問題は、インスタンスのスケールアップと、テーブル、クエリ、ストアド プロシージャなどのデータベース オブジェクトの最適化によって軽減できる場合があります。インスタンスのパフォーマンスを上げることは短期的には有効ですが、SQL とアプリケーション コードを最適化することは、長期的な安定性とパフォーマンスの向上につながります。

インスタンス コアとストレージ容量

コアとストレージ容量を増やすことはスケールアップとも呼ばれ、IO のスループットとパフォーマンスに即効性があり、多くのワークロードのパフォーマンスの問題は、CPU とストレージの構成設定を増やすことで軽減される可能性があります。ディスクのパフォーマンスは、ディスクのサイズと vCPU の数に基づきます。ストレージ容量と vCPU を追加して、IOPS とスループットを向上させることができます。

Read Committed Snapshot Isolation（RCSI）

競合を減らし高速化するためにクエリに NO LOCK を追加している場合、Read Committed Snapshot Isolation の参照をおすすめします。READ_COMMITTED_SNAPSHOT をオンにすると、SQL Server Engine はロックではなく行のバージョニングを使用します。詳しくは、Kendra Little 氏のブログ投稿「How to Choose Between RCSI and Snapshot Isolation Levels（RCSI かスナップショット分離レベルかを選択する方法）」を確認して、RCSI がデータベースのワークロードに適しているかどうかを判断してください。

強制的なパラメータ化

動的 SQL を大量に生成するアプリケーションや、パラメータなしで SQL を実行するアプリケーションに遭遇した場合、SQL クエリの新しいプラン作成に多くの CPU 時間が浪費される場合があります。アプリケーション コーディングの標準を変更したり、影響を行き渡らせたりすることができないときに、強制的なパラメータ化がデータベース パフォーマンスの向上に役立つことがあります。強制パラメータ化の詳細とその適用方法については、SQL Server Database Parameterization option and its Query Performance effects（SQL Server データベースのパラメータ化のオプションとそのクエリ パフォーマンス効果）をご確認ください。

6. インデックスと統計情報を管理する: SQL Server のメンテナンス

Ola Hallengren 氏の SQL Server Maintenance Solution は、SQL Server コミュニティ規約のデータベース メンテナンス ソリューションです。オンプレミスまたは GCE 環境では、データベース管理者はバックアップ スクリプトを含むメンテナンス ソリューション全体のインストールを選択できます。バックアップは Cloud SQL によって内部的に処理されるため、データベース管理者は統計情報とインデックスのプロシージャとサポート オブジェクトのみをインストールすることを選択できます。ソリューションの詳細については、https://ola.hallengren.com/ をご覧ください。また、関連するスクリプト、手順、ドキュメントおよび例をご覧になり、SQL Server Maintenance Solution のインストールおよび使用方法をご確認ください。

まとめ

Cloud SQL の SQL Server データベースの積極的な管理と調整により、データベース管理者は本番環境でのサポートへの問い合わせに費やす時間を短縮し、データベースのパフォーマンス、効率、スケーラビリティを向上させることができます。このブログで紹介したツールや推奨事項の多くは、GCE 上で動作する SQL Server データベースにも適用できます。このブログで説明したツールやプロセスをご理解いただけましたら、お客様のデータベースのワークフローや管理プランに取り入れることをぜひご検討ください。

- 戦略クラウド エンジニア Matthew Smith