Azure SQL Databaseの自動チューニング

Azure SQL Databaseには、データベースのワークロードを継続的に監視し、パフォーマンスを向上させるためのアクションを自動的に識別・適用する「自動チューニング」機能が備わっています。

自動チューニングとは

自動チューニングは、人工知能と機械学習に基づいたパフォーマンスチューニングサービスです。以下の主な機能があります。

• パフォーマンスの監視: データベースのワークロードを常時監視します。
• 推奨事項の提示: パフォーマンス向上のためのチューニングアクションを提案します。
• 自動適用: AUTOモードに設定することで、推奨事項を自動的に適用します。

主なチューニングオプション

オプション	説明
FORCE_LAST_GOOD_PLAN	回帰した実行プラン（遅くなったクエリプラン）を検出し、以前の良好なプランを強制的に使用します。
CREATE_INDEX	パフォーマンスを向上させる可能性のあるインデックスを提案・作成します。
DROP_INDEX	長期間使用されていない、または重複しているインデックスを削除します。（注意: ユニークインデックスなどの制約に関わるインデックスは削除されません）

注意: Azure SQL Managed Instanceの場合、現在 FORCE_LAST_GOOD_PLANのみがサポートされており、T-SQLでのみ構成可能です。

サーバーレベルでの有効化

サーバーレベルで設定を行うと、そのサーバー上のすべてのデータベースに設定が継承されます（個別にオーバーライドしない限り）。

Azure Portalを使用する場合

1. Azure Portalで対象のSQL Server（論理サーバー）に移動します。
2. 左側のメニューから「自動チューニング」を選択します。
3. 有効にしたいオプションを選択し、「適用」をクリックします。

Azureのデフォルト設定は以下の通りです：

• FORCE_LAST_GOOD_PLAN: 有効
• CREATE_INDEX: 無効
• DROP_INDEX: 無効

データベースレベルでの有効化

個々のデータベースに対して、サーバーの設定を継承するか、独自の設定を行うかを選択できます。

Azure Portalを使用する場合

1. Azure Portalで対象のSQL Databaseに移動します。
2. 左側のメニューから「自動チューニング」を選択します。
3. 「サーバーからの継承」タブ、または個別の設定を行い、「適用」をクリックします。

T-SQLを使用する場合

データベースに接続し、以下のクエリを実行することで設定を変更できます。

-- サーバーの設定を継承する場合
ALTER DATABASE current SET AUTOMATIC_TUNING = INHERIT;

-- 個別に設定する場合 (例: FORCE_LAST_GOOD_PLANを有効化)
ALTER DATABASE current SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

自動チューニングとIaC/マイグレーションの競合

CREATE_INDEX や DROP_INDEX オプションを有効にすると、Azure SQL Database が自動的にスキーマ（インデックス）を変更します。しかし、Entity Framework Core (EF Core) のMigrationや、TerraformなどのIaCツールでDBスキーマを管理している場合、コード上の定義と実際のデータベースの状態に**乖離（Drift）**が発生します。

発生する問題

• スキーマの不整合: 自動作成されたインデックスはMigrationファイルに含まれていないため、dotnet ef migrations script などで生成されるスクリプトには反映されません。
• 再作成のリスク: 次回のデプロイやMigration適用時に、ツールが「意図しない変更」と見なしてインデックスを削除したり、逆に DROP_INDEX で消されたインデックスを再作成しようとする可能性があります。

ベストプラクティス

IaCやMigrationツールを使用している環境では、以下の運用を推奨します。

1. 本番環境では CREATE_INDEX / DROP_INDEX の自動適用を無効にする

   • 推奨設定: FORCE_LAST_GOOD_PLAN のみを ON にし、インデックス操作は OFF にします。
   • FORCE_LAST_GOOD_PLAN はプランのリグレッションを防ぐだけなので、スキーマ定義には影響を与えず、Migrationツールと競合しません。

2. 自動チューニングを「発見」の手段として利用する

   • 開発環境やステージング環境、あるいは本番環境の「推奨事項」のみを定期的に確認します。

   • Azure Portalや sys.dm_db_tuning_recommendations ビューを確認し、有益なインデックスがあれば、それを EF CoreのモデルやMigrationファイルに手動で追加 します。

     推奨事項確認用クエリの例:

     SELECT
         name,
         reason,
         state,
         JSON_VALUE(details, '$.implementationDetails.script') as script,
         details
     FROM sys.dm_db_tuning_recommendations
     WHERE reason LIKE '%Create_Index%';

   • これにより、コードベース（Migrationファイル）を「正」の状態（Single Source of Truth）として保ちつつ、自動チューニングの恩恵を受けることができます。

3. DROP_INDEX のリスク

   • 自動チューニングは「使用頻度の低いインデックス」を削除候補としますが、例として「年に1回の決算処理でのみ使用される重要なインデックス」などを誤って削除してしまう可能性があります。
   • アプリケーションロジックで明示的に定義しているインデックスが勝手に削除されることを防ぐため、通常は無効化が推奨されます。

トラブルシューティング

レコメンデーションの自動管理が無効になっている場合

「自動推奨管理が無効にされました」というメッセージが表示される場合、以下の原因が考えられます。

• クエリシストアが無効: クエリシストアが有効になっているか確認してください。
• 読み取り専用モード: データベースが読み取り専用、またはクエリシストアが満杯で読み取り専用になっている可能性があります。

解決策として、クエリシストアのデータ保持期間を調整したり、最大サイズを増やすことを検討してください。

アクセス許可

自動チューニングを管理するには、最低でもSQL Database 共同作成者 (SQL Database Contributor) ロールのメンバーシップが必要です。

関連リソース

• Azure SQL Database の自動チューニングの有効化 - Microsoft Learn
• ALTER DATABASE SET オプション (Transact-SQL)