Cloud SQL for SQL Server: データベース管理のベスト プラクティス

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

Cloud SQL for SQL Server は、Google Cloud Platform 上で SQL Server データベースのセットアップ、維持、管理を容易に行えるようにするフルマネージド データベース サービスです。Cloud SQL は運用の手間を大幅に軽減してくれますが、Cloud SQL 上で稼働する SQL Server データベースが最適なパフォーマンスを発揮するためには、引き続き管理が必要です。このブログでは、主要なデータベース管理作業を取り上げていますので、既存の社内データベース管理者（DBA）向けのチェックリスト、ポリシー、手順書、ランブック、IT 運用ガイドと合わせてご確認ください。

一般的な DBA の任務と DBA 向けチェックリスト

SQL Server の DBA は、以下に示すさまざまな業務に携わります。

1. データベースの作成

2. データベース オブジェクト（テーブル、ビュー、ストアド プロシージャなど）の管理

3. ユーザーとセキュリティの管理

4. データベースのバックアップと、必要に応じた復元操作の実施

5. 特定顧客向けのデータベース インスタンスのクローン作成とプロビジョニング

6. インスタンスの状態のモニタリングと、必要に応じた予防措置または是正措置の実施

7. データベースのパフォーマンスのモニタリングと調整

8. 重大なエラーの診断と、GCP サポート サービスへの報告

大規模なエンタープライズ環境では、多くの場合、複数人の DBA で業務を分担します。中小規模のデータベース環境では、1 人ですべてのデータベース管理業務を行うこともあります。

DBA 向けチェックリスト

以下の DBA 向けチェックリストは、Cloud SQL インスタンス上で SQL Server データベースの管理を開始する際に役立ちます。チェックリストに記載の作業の中には、データベースの使用状況とお客様の業務要件に応じて、毎日実行するものと、週 1 回または月 1 回実行するものがあります。

1. SQL Server エラーログや SQL Server エージェント ログなどのエラーログを確認する。

2. Cloud SQL のメンテナンスの時間枠を確認、設定する。

3. バックアップと復元: 構成、ジョブの成否、RTO 要件と RPO 要件との整合性を確認する。

4. 高可用性（HA）と障害復旧（DR）: 構成を確認し、組織の HA 要件と DR 要件との整合性を確認する。

5. データベースの復元を週 1 回テストし、DBCC CheckDB を週 1 回実行する。

6. ワークロード（トランザクションとバッチ）、スケジュールされたジョブ、処理、メンテナンスの時間枠のモニタリング: ワークロード、スケジュール、ランタイムを確認する。スケジュール、ランタイムの所要時間（Min、Max、Avg、stDev）など、各ワークロードを追跡できているか確認する。

7. Cloud SQL インスタンスに接続するアプリケーションを確認する。接続パターンのほか、Read Uncommitted（確定していないデータまで読み取る）と Read Committed（確定した最新データを常に読み取る）の違いなどの読み取り分離要件を理解する。

8. インデックスのデフラグメンテーションや統計情報の更新など、データベースの標準的なメンテナンス ジョブを確認する。

Cloud SQL の構成

Cloud SQL for SQL Server は、インスタンス レベルとデータベース レベルで構成可能です。インスタンス レベルの構成オプションのほとんどは、Cloud Console で管理できます。インスタンス レベルの構成は、すべてのデータベースのパフォーマンスに影響するため、時間をかけて確認する価値があります。

インスタンス レベルの構成

vCPU、メモリ、ストレージなどのインスタンス構成は、Cloud Console で管理されます。必要に応じて Cloud Console を使用して変更してください。

tempdb

tempdb は、SQL Server のシステム データベースで、SQL Server の一時的なオブジェクトの内部処理に使用されます。現在、tempdb は、インスタンスの vCPU 数に応じて、4 つまたは 8 つのデータファイルで作成されます。以前は、デフォルトの構成である 1 つのデータファイルでは、最適なパフォーマンスが得られないことがよくありました。Microsoft は、こちらのリンクで、tempdb の競合を減らすためにファイルを追加することを推奨しています。tempdb 内のすべてのファイルは同じサイズ、同じファイル拡張設定にする必要があります。また、tempdb が自動拡張する必要がないように、あらかじめサイズを決めておくこともおすすめします。8 vCPU 未満の場合、tempdb ファイルの数を vCPU 数と一致させる必要があります。8 vCPU 以上の場合は、8 つの tempdb ファイルを使用します。Microsoft の SQL Server Management Studio（SSMS）または T-SQL ステートメントを使用して、tempdb の構成を変更します。

tempdb の構成を確認するには、以下のクエリを実行します。

データベース フラグ（別名トレースフラグ）

Cloud SQL データベース フラグは、SQL Server コミュニティの間ではトレースフラグとも呼ばれ、インスタンスの挙動とパフォーマンスに影響を及ぼします。サポート対象のすべてのデータベース フラグの一覧については、サポートされているフラグをご確認ください。インスタンスに現在実装されているデータベース フラグを確認するには、次の T-SQL ステートメントを実行します。

構成設定 

SQL Server の DBA は、多くの場合、システムのストアド プロシージャ「sp_configure」を使用して、インスタンス レベルの構成設定を行ったり、設定を表示したりします。Cloud SQL では、sp_configure によるインスタンス設定の変更はサポートされていません。代わりに、データベースの設定やデータベース スコープの構成を使用してください。すべての sp_configure 設定を確認する場合、DBA は、Microsoft の SQL Server Management Studio（SSMS）や Azure Data Studio などのクエリツールを使用して、以下の SQL ステートメントを実行できます。

最後に、Cloud SQL for SQL Server インスタンスの包括的な概要については、インスタンス レベルおよびデータベース レベルの情報収集をサポートするクエリを含む、Glen Berry の SQL Server 2017 Diagnostic Information Queries をご確認ください。  

データベース レベルの構成

データベース スコープの構成

データベース スコープの構成とデータベースのプロパティから、DBA は特定のデータベースのデータベース レベルの設定を表示し、設定を行えます。特定のデータベースで使用されているすべてのデータベース スコープの構成を表示するには、次の SQL ステートメントを実行します。

特定のデータベースのデータベース スコープの構成を変更するには、以下のサンプル構文を変更します。

データベース互換性レベル、パフォーマンス、ワークロードに応じたその他の一般的なデータベース スコープの構成の例としては、PARAMETER_SNIFFING、OPTIMIZE_FOR_AD_HOC_WORKLOADS、LEGACY_CARDINALITY_ESTIMATION が挙げられます。 

特定のデータベースのデータベース設定を確認するには、以下の SQL ステートメントを実行します。

特定のデータベースの設定を変更するには、以下の SQL ステートメントを実行します。

自動縮小と自動終了

データベースのマウントとフラグメンテーションに関連するパフォーマンスの問題を回避するには、すべてのデータベースの自動終了と自動縮小をオフに設定する必要があります。

並列処理の最大次数（MAXDOP）

SQL Server は、並列処理最大次数オプションを適用して、並列プラン実行時に使用するプロセッサの数を制限します。たとえば、Cloud SQL インスタンスに 40 個の vCPU がプロビジョニングされている場合、SQL オプティマイザは、並列クエリの実行時に 40 個の vCPU をすべて使用することもできます。

過剰な並列処理により生じることのあるパフォーマンスの問題は、通常長いクエリ実行時間として露呈します。クエリの実行時に複数の並列スレッドが実行され、データを返すまでに一部のスレッドが他のスレッドよりも長く待機しているように見える場合は、クエリに OPTION（MAXDOP 8）ヒントを追加することを検討してください。たとえば、クエリに OPTION（MAXDOP 8）ヒントが含まれている場合、SQL Server は並列処理を 8 個の vCPU のみに制限します。

MAXDOP は、Cloud SQL 上の SQL Server のデータベース レベルで設定されます。以下の T-SQL ステートメントを実行して、特定のデータベースの MAXDOP を設定し、設定内容を表示します。

データベースとデータファイルの拡張

データベースの拡張は、インスタンス レベルとデータベース レベルで有効にする必要があります。

1. インスタンス レベルでは、[ストレージの自動増量を有効にする] チェックボックスをオンにして、Cloud SQL インスタンスの自動拡張を有効にします。

2. データベース レベルでは、DB オーナーが責任者として自動拡張を有効にします。詳細については、Microsoft の構成パラメータに関するドキュメントをご覧ください。

拡張率（%）ではなく、MB 単位を適用するには、データベース ファイルの自動拡張設定を更新します。データベースによって、データファイルの拡張単位を 128 MB または 256 MB、トランザクション ログの拡張単位を 256 MB にまず設定し、必要に応じてファイルの拡張単位を増やしてください。場合によっては、データファイル用に 1,024 MB、ログファイル用に 4,096 MB といった、より大きな拡張単位を適用することもできます。いずれにしても、データベースの IO 動作とアプリケーションの要件を理解することで、ワークロードに応じて最適な拡張単位を選択できます。

バックアップ、高可用性、データベース クローン

バックアップ

Cloud SQL インスタンス上の各データベースについて、組織の目標復旧時間（RTO）と目標復旧時点（RPO）を理解し、文書化することが重要です。Cloud SQL 上のデータベースは、「シンプル」復旧モードで動作します。インスタンスのバックアップ スケジュールと保持の設定を確認してください。インスタンスのバックアップは、オンデマンドで実行することも、必要に応じて実行スケジュールを設定することもできます。データベースのバックアップは、Cloud SQL ではデータベースのエクスポートと呼ばれます。gcloud コマンドまたは Cloud Console を使用して、データベースを .bak ファイルとして gcp バケットにエクスポートできます。データベースのテーブルに大量のデータをインポートしたり、テーブルから大量のデータをエクスポートしたりする場合は、Microsoft bcp ユーティリティを使用します。

DBCC CHECKDB

DBCC CHECKDB は、指定したデータベース内のすべてのオブジェクトの整合性を確認するために使用します。DBA は、DBCC CHECKDB を週 1 回実行することで、データベースが破損していないか確認できます。本稼働サーバーで DBCC を実行するのではなく、データベースのクローンを作成してそのクローンで DBCC CHECKDB を実行するか、バックアップを別のインスタンスに復元してからそのインスタンスで DBCC CHECKDB を実行してください。

高可用性

インスタンス高可用性は、Cloud Console を使用して有効化します。レプリケーションやミラーリングなど、SQL Server のさまざまな HA テクノロジーとは異なり、Cloud SQL ではリージョン永続ディスク（RePD）を使用して、1 つのリージョン内の 2 つのゾーンにデータを複製します。業務上重要なインスタンスについては、高可用性を有効にすることをおすすめします。

データベース クローン

Cloud SQL インスタンスのクローンを作成すると、元のインスタンスのコピーが新しいインスタンスとして作成されます。新しいインスタンスは、元のインスタンスから完全に独立しています。クローン作成は、以下の理由で有用です。

1. データベースまたはアプリケーションをアップグレードする前に、データベースをフォークできる。

2. Blue/Green コードデプロイとアプリケーション デプロイができる。

3. 開発、試験、またはその他の目的のため、他のチームおよび部門向けにインスタンスをプロビジョニングできる。

クローンは、Cloud Console または gcloud コマンドラインを使用して作成できます。

データベースのメンテナンス

インスタンスのメンテナンス

Cloud SQL インスタンスでは、バグの修正、セキュリティ侵害の防止、アップグレードの実施のために時折更新が必要です。メンテナンスは、平均して 1～2 か月に 1 回行われ、実施時にはインスタンスのダウンタイムが発生します。更新を適用すると、Cloud SQL が再起動するため、サービスが中断される可能性があります。Cloud SQL の再起動は通常、90 秒以内に終了します。再起動の間、HA プライマリ インスタンスはフェイルオーバーせず、アプリケーションは接続できなくなります。Cloud SQL データベースのメンテナンス スケジュールを確認し、バックアップやデータベース メンテナンスなどの他のスケジュールされたジョブのほか、既存の IT 運用のスケジュールと整合しているかをご確認ください。

インデックスのメンテナンス

SQL Server のインデックスは、データの取得に必要な IO 量を最小限に抑えるうえで重要な役割を果たします。最適に設計、管理されたインデックスは、データ取得に必要な IO を大幅に削減させ、SQL Server がより多くの同時ユーザーにサービスを提供できるようにします。管理されていないインデックスまたは設計が不十分なインデックスは、逆の効果をもたらします。インデックス作成方法を策定するときに考慮すべき重要な点が複数あります。

1. インデックスを作成する前に、DBA は、データ構造、テーブルタイプ、既存のインデックス、クエリ実行計画、データアクセス パターン、クエリの応答要件など、多くのデータポイントを慎重に検討、分析する必要があります。

2. インデックスは、データ取得（Select）を高速化する一方で、Insert、Update、Delete の各操作の速度を低下させることもあります。

3. 適切なタイプのインデックスを設計するには、作成するインデックスのタイプ、基礎となるテーブルのタイプ、インデックス列、列の順序など、多くの要因を考慮する必要があります。

4. インデックスを作成したら、データ量、アプリケーションの機能、ユースケース、クエリパターンの変更に応じて、インデックスを維持、モニタリング、最適化する必要があります。

インデックス最適化ルーチンを含む SQL Server Maintenance Solution を確認し、インストールすることをご検討ください。必ず、オブジェクトを非システム データベースにインストールしてください。また、インデックス構造、欠落インデックス、未使用インデックスをモニタリングするための複数の有用なクエリが含まれる、Glen Berry の SQL Server 2017 Diagnostic Information Queries もご確認ください。

統計情報のメンテナンス

SQL Server の統計情報は、クエリの実行計画を作成するため、SQL Server Optimizer によって使用されます。データが頻繁に変更されると、統計情報が古くなり、クエリ計画が最適とは言えない状態になることがあります。多くの場合、頻繁に変更されるテーブルの統計情報は、毎日更新した方がよいとされます。SQL Server の統計情報について、覚えておくべきいくつかの重要点を以下に示します。

1. 統計情報を更新することで、SQL Optiomizer が実行計画を作成するための最新の情報を得ることができます。統計情報が最新ではない場合、クエリのパフォーマンスが低下したり、クエリの実行時間にばらつきが出たりします。

2. SQL Server は、設定した割合の行が変更されたときに自動的に統計情報を更新します。

3. DBA が明示的に統計情報を作成することが推奨されていますが、SQL Server は、特定の条件下で列の統計情報を作成します。

4. 統計情報の作成は、DBA またはデータベース開発者が行う反復的プロセスであり、通常、統計情報がどこで役立つかを判断するためのワークロードの捕捉と分析、統計情報の作成、クエリ パフォーマンスのモニタリングといったステップが含まれます。

各データベースの統計情報の自動作成を有効にするだけでなく、統計情報を維持管理するためのデータベース メンテナンス ソリューションを導入し、SQL Server エージェントを使用して、統計情報のメンテナンス ジョブのスケジュールを設定してください。

モニタリングとアラート

モニタリング 

特に実行計画の収集と分析が必要な場合、アクティブな SQL Server のクエリを使用中のシステムでモニタリングすることは困難な場合があります。SQL Server MVP の Adam Machanic 氏が開発したアクティビティ モニタリング用ストアド プロシージャである sp_WhoIsActive を非本稼働データベースにインストールし、クエリのトラブルシューティングと分析に使用してください。

1. プロシージャを呼び出すときに、データベース名を追加します。

1. ex: exec dbtools.dbo.sp_WhoIsActive @get_plans=1

2. パラメータ @get_plans=1 を使用して、実行計画を捕捉します。この計画（XML 形式）を .sqlplan というファイル拡張子で保存し、SQL Server Management Studio を使用して確認します。

3. sp_WhoIsActive の出力をテーブルに記録して、さらに分析することを検討してください。

2. SQL Server のクエリストア を有効化します。

3. Paul Randall 氏による Wait Stats クエリを確認、実行して、インスタンスとデータベースの上位の待機を把握できます。

1. 現在の待機

2. 合計待機（前回のデータベース再起動以降）

SQL Server のログファイルを確認することで、DBA は、発生する可能性のある問題を予測し、既存の問題を診断して、是正措置を講じることができます。Cloud Console を使用すると、SQL Server エラーログと SQL Server エージェント ログにアクセスできます。

アラート

Cloud Monitoring アラートは、特定のパフォーマンスの問題を管理者に通知するよう構成できます。アラートの設定方法の詳細については、GCP のモニタリングとアラートをご覧ください。

データベース DevOps

SQLPackage.exe は、データベースのデプロイをサポートする Microsoft のユーティリティで、コードとオブジェクトを SQL Server にデプロイするために使用されます。SQLPackage.exe は Windows、macOS、Linux で動作します。Microsoft Visual Studio は広く使用されている IDE で、SQL Server データベースのプロジェクト テンプレートを実装しており、管理された SQL Server データベースのコードに対応しています。

Cloud SQL 上でカスタム アプリケーションを開発、デプロイする場合、SQLPackage.exe と SQL Server Dacpac を既存の DevOps のビルド、テスト、リリースのプロセスにどのように統合できるかを把握することをおすすめします。

このブログが SQL Server データベースの最適化に役立つことを願っています。Cloud SQL for SQL Server のベスト プラクティスについては、こちらのドキュメントをご覧ください。