適用対象:
SQL Server 2017 (14.x) 以降のバージョン Azure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric の SQL データベース
自動チューニングは、潜在的なクエリ パフォーマンスの問題に関する分析情報を提供し、解決策を推奨し、特定された問題を自動的に修正するデータベース機能です。
SQL Server 2017 (14.x) で導入された自動チューニングでは、潜在的なパフォーマンスの問題が検出されるたびに通知され、修正アクションを適用したり、データベース エンジンでパフォーマンスの問題を自動的に修正したりできます。 SQL Server の自動チューニングでは、 クエリ実行プランの選択の回帰によって発生するパフォーマンスの問題が特定され、修正されます。 Azure SQL Database と Microsoft Fabric の SQL データベースの自動チューニングでは、必要なインデックスも作成され、未使用のインデックスが削除されます。 クエリ実行プランの詳細については、「 実行プラン」を参照してください。
SQL Server データベース エンジンは、データベースで実行されるクエリを監視し、ワークロードのパフォーマンスを自動的に向上させます。 データベース エンジンには、データベースをワークロードに動的に適応させることで、クエリのパフォーマンスを自動的に調整および改善できるインテリジェンス メカニズムが組み込まれています。 使用可能な自動チューニング機能は 2 つあります。
自動プラン修正 では、 パラメーターの感度やパラメーター スニッフィング の問題など、問題のあるクエリ実行プランを識別し、回帰が発生する前に最後に既知の正常なプランを強制することで、クエリ実行プラン関連のパフォーマンスの問題を修正します。 適用対象: SQL Server (SQL Server 2017 (14.x 以降))、Microsoft Fabric の Azure SQL Database、SQL データベース、および Azure SQL Managed Instance
インデックスの自動管理 では、データベースに追加する必要があるインデックスと、削除する必要があるインデックスを識別します。 適用対象: Microsoft Fabric の Azure SQL Database と SQL データベース
Note
この記事では、Azure SQL Database の機能と動作は、Microsoft Fabric の SQL データベースにも適用されます。
自動チューニングの理由
クラシック データベース管理の主なタスクの 3 つに、ワークロードの監視、重要な Transact-SQL クエリの識別、パフォーマンスを向上させるために追加する必要があるインデックス、またはほとんど使用されておらず、パフォーマンスを向上させるために削除できるインデックスの識別があります。 SQL Server データベース エンジンは、監視する必要があるクエリとインデックスに関する詳細な分析情報を提供します。 ただし、データベースを常に監視することは、特に多くのデータベースを扱う場合は、困難で面倒な作業です。 膨大な数のデータベースを効率的に管理することは不可能な場合があります。 データベースを手動で監視およびチューニングする代わりに、自動チューニング機能を使用して、一部の監視およびチューニング アクションをデータベース エンジンに委任することを検討してください。
自動チューニングのしくみ
自動チューニングは、ワークロードの特性について常に学習し、潜在的な問題と改善点を特定する継続的な監視および分析プロセスです。
このプロセスにより、ワークロードのパフォーマンスを向上させる可能性があるインデックスとプラン、およびワークロードに影響するインデックスを見つけることで、データベースをワークロードに動的に適応させることができます。 自動チューニングでは、これらの検出結果に基づき、ワークロードのパフォーマンスを向上させるチューニング アクションを適用します。 さらに、自動チューニングでは、ワークロードのパフォーマンスが向上することを確認するために、変更を実装した後のデータベースのパフォーマンスが継続的に監視されます。 パフォーマンスが向上しなかったすべてのアクションは、自動的に元に戻されます。 この検証プロセスは、自動チューニングによって行われた変更によってワークロードの全体的なパフォーマンスが低下しないことを保証する重要な機能です。
自動プラン修正
自動プラン修正は、 実行プランの選択回帰 を識別し、最後に既知の適切なプランを強制することで問題を自動的に修正する自動チューニング機能です。 クエリ実行プランとクエリ オプティマイザーの詳細については、「 クエリ処理アーキテクチャ ガイド」を参照してください。
Important
プランの自動修正は、ワークロード追跡のためにデータベースでクエリ ストアが有効になっているかどうかによって異なります。
実行プランの選択回帰とは
SQL Server データベース エンジンでは、異なる実行プランを使用して Transact-SQL クエリを実行できます。 クエリ プランは、統計、インデックス、およびその他の要因によって異なります。 Transact-SQL クエリの実行に使用する最適なプランは、これらの要因の変化に応じて時間の経過と同時に変化する可能性があります。 場合によっては、新しいプランが前のプランよりも適していない場合があり、新しいプランによって、 パラメーターの感度やパラメーター スニッフィング 関連の問題など、パフォーマンスの低下が発生する可能性があります。
プランの選択の回帰が発生したことに気付くたびに、以前の適切なプランを見つけて、現在のプランではなく強制的に使用する必要があります。 これを行うには、 sp_query_store_force_plan プロシージャを使用します。 SQL Server 2017 (14.x) のデータベース エンジンは、後退したプランと推奨される是正措置に関する情報を提供します。 さらに、データベース エンジンを使用すると、このプロセスを完全に自動化し、プランの変更に関連する問題をデータベース エンジンで修正できます。
Important
ワークロードのアップグレード リスクを自動的に軽減するために、ベースラインをキャプチャした後、データベース互換性レベルのアップグレードのスコープで自動プラン修正を使用する必要があります。 このユース ケースの詳細については、「 新しい SQL Server へのアップグレード中のパフォーマンスの安定性を維持する」を参照してください。
自動プラン選択修正
データベース エンジンは、プランの選択の回帰が検出されるたびに、最新の正常なプランに自動的に切り替えることができます。
データベース エンジンは、間違ったプランの代わりに使用する必要があるプランを含め、プランの選択に関する潜在的な回帰を自動的に検出します。 自動プランの修正によって強制される実行プランは、前回の既知の適切なプランと同じか類似します。 結果として得られるプランは、前回の良好な計画と同じでない可能性があるため、強制プランのパフォーマンスは異なる場合があります。 まれに、パフォーマンスの違いは大きく、負の場合もあります。この場合、自動プラン修正は自動的に置換プランの強制試行を停止します。
データベース エンジンは、回帰が発生する前に最後の既知の適切なプランを適用すると、強制プランのパフォーマンスを自動的に監視します。 強制プランが後退したプランよりも優れていない場合、新しいプランは強制解除され、データベース エンジンは新しいプランをコンパイルします。 データベース エンジンが、強制プランが後退したプランよりも優れていることを確認した場合、強制プランは保持されます。 再コンパイルが行われるまで保持されます (たとえば、次の統計の更新やスキーマの変更など)。 プラン強制の詳細と、強制可能なプランの種類については、「プランの 強制制限」を参照してください。
Note
プラン強制アクションが検証される前に SQL Server インスタンスが再起動されると、そのプランは自動的に強制解除されます。 それ以外の場合、プランの強制は SQL Server の再起動時に保持されます。
プランの自動選択修正を有効にする
データベースごとに自動チューニングを有効にし、プランの変更の回帰が検出されるたびに、最後の適切なプランを強制することを指定できます。 自動チューニングは、次のコマンドを使用して有効になります。
ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );
このオプションを有効にすると、データベース エンジンは、推定 CPU ゲインが 10 秒を超える場合、または新しいプランのエラー数が推奨プランのエラー数より多い場合に、推奨事項を自動的に強制し、強制プランが現在のプランよりも優れていることを確認します。
Azure SQL Database と Azure SQL Managed Instance で自動チューニングを有効にするには、「Azure portal を使用して Azure SQL Database で自動チューニングを有効にする」を参照してください。
代替 - 手動プランの選択の修正
自動チューニングを行わない場合、ユーザーは定期的にシステムを監視し、後退したクエリを探す必要があります。 いずれかのプランが後退した場合、ユーザーは以前の適切なプランを見つけ、 sp_query_store_force_plan プロシージャを使用して現在のプランではなく強制的に適用する必要があります。 統計またはインデックスの変更により古いプランが無効になる可能性があるため、最後に既知の適切なプランを強制することをお勧めします。 最後の既知の適切なプランを強制するユーザーは、強制プランを使用して実行されるクエリのパフォーマンスを監視し、強制プランが期待どおりに動作することを確認する必要があります。 監視と分析の結果によっては、プランを強制するか、ユーザーがクエリを最適化する別の方法 (書き換えなど) を見つける必要があります。 データベース エンジンは最適なプランを適用できるため、手動で強制されたプランを永続的に強制しないでください。 ユーザーまたは DBA は、最終的に sp_query_store_unforce_plan 手順を使用してプランを強制解除し、データベース エンジンが最適なプランを見つけられるようにする必要があります。
Tip
または、[ 強制プランを含むクエリ クエリ ストア] ビューを使用して、プランの検索と適用解除を行います。
SQL Server には、パフォーマンスを監視し、クエリ ストアの問題を解決するために必要なすべてのビューと手順が用意されています。
SQL Server 2016 (13.x) では、クエリ ストア システム ビューを使用してプランの選択回帰を見つけることができます。 SQL Server 2017 (14.x) 以降、データベース エンジンは、プランの選択肢の潜在的な回帰と、 sys.dm_db_tuning_recommendations (Transact-SQL) DMV に適用する必要がある推奨アクションを検出して表示します。 DMV には、問題に関する情報、問題の重要性、特定されたクエリ、後退したプランの ID、比較のベースラインとして使用されたプランの ID、問題を解決するために実行できる Transact-SQL ステートメントなどの詳細が表示されます。
| 型 | description | datetime | スコア | details | ... |
|---|---|---|---|---|---|
FORCE_LAST_GOOD_PLAN |
CPU 時間が 4 ミリ秒から 14 ミリ秒に変更されました | 3/17/2017 | 83 |
queryId
recommendedPlanId
regressedPlanId
T-SQL
|
|
FORCE_LAST_GOOD_PLAN |
CPU 時間が 37 ミリ秒から 84 ミリ秒に変更されました | 3/16/2017 | 26 |
queryId
recommendedPlanId
regressedPlanId
T-SQL
|
このビューの一部の列については、次の一覧で説明します。
- 推奨されるアクション
FORCE_LAST_GOOD_PLANの種類。 - このプランの変更がパフォーマンス低下の可能性があるとデータベース エンジンが考える理由を示す説明。
- 潜在的な回帰が検出された日時。
- この推奨事項のスコア。
- 検出されたプランの ID、後退したプランの ID、問題の修正を強制する必要があるプランの ID、問題を解決するために適用される可能性があるスクリプト Transact-SQL などの問題の詳細。詳細は JSON 形式で格納されます。
次のクエリを使用して、問題を修正するスクリプトと、推定ゲインに関する追加情報を取得します。
SELECT reason, score,
script = JSON_VALUE(details, '$.implementationDetails.script'),
planForceDetails.*,
estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount)
* (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
WITH ( [query_id] int '$.queryId',
regressedPlanId int '$.regressedPlanId',
recommendedPlanId int '$.recommendedPlanId',
regressedPlanErrorCount int,
recommendedPlanErrorCount int,
regressedPlanExecutionCount int,
regressedPlanCpuTimeAverage float,
recommendedPlanExecutionCount int,
recommendedPlanCpuTimeAverage float
) AS planForceDetails;
結果セットは次のとおりです。
| reason | スコア | スクリプト | query_id | 現在のプランID | 推奨されるplan_id | 推定利益 | エラーが発生しやすい |
|---|---|---|---|---|---|---|---|
| CPU 時間が 3 ミリ秒から 46 ミリ秒に変更されました | 36 | EXEC sp_query_store_force_plan 12, 17; | 12 | 28 | 17 | 11.59 | 0 |
estimated_gain列は、推奨プランが現在のプランではなくクエリ実行に使用される場合に保存される推定秒数を表します。 ゲインが 10 秒を超える場合は、現在のプランの代わりに推奨プランを強制する必要があります。 現在のプランで、推奨されるプランよりも多くのエラー (タイムアウトや実行の中止など) がある場合、列 error_prone は YES値に設定されます。 現在のプランではなく、推奨されるプランを強制する必要があるもう 1 つの理由は、エラーが発生しやすいプランです。
データベース エンジンは、プランの選択の回帰を識別するために必要なすべての情報を提供しますが、継続的な監視とパフォーマンスの問題の修正は面倒なプロセスになる可能性があります。 自動チューニングにより、このプロセスがはるかに簡単になります。
Note
sys.dm_db_tuning_recommendations DMV 内のデータは、データベース エンジンの再起動後に保持されません。 データベース エンジンが最後に起動された時刻を調べるには、sqlserver_start_time の を使用します。
インデックスの自動管理
Azure SQL Database では、Azure SQL Database がワークロードについて学習し、データのインデックスが常に最適に作成されるようにするため、インデックス管理は簡単です。 ワークロードの最適なパフォーマンスを実現するには適切なインデックス設計が不可欠であり、インデックスの自動管理はインデックスの最適化に役立ちます。 インデックスの自動管理では、インデックスが正しくないデータベースのパフォーマンスの問題を修正するか、既存のデータベース スキーマのインデックスを維持および改善できます。 Azure SQL Database の自動チューニングでは、次のアクションが実行されます。
- テーブルからデータを読み取る Transact-SQL クエリのパフォーマンスを向上させる可能性があるインデックスを識別します。
- 使用されなくなった期間が長いインデックスや冗長なインデックスを特定し、削除できる可能性を見出します。 不要なインデックスを削除すると、テーブル内のデータを更新するクエリのパフォーマンスが向上します。
インデックス管理が必要な理由
インデックスを使用すると、テーブルからデータを読み取るクエリの一部が高速化されますが、データを更新するクエリの速度が低下する可能性があります。 インデックスを作成するタイミングと、インデックスに含める必要がある列を慎重に分析する必要があります。 一部のインデックスは、しばらくすると必要ない場合があります。 そのため、これらのインデックスを定期的に特定して削除する必要があります。これらのインデックスには利点はありません。 未使用のインデックスを無視すると、データを更新するクエリのパフォーマンスが低下し、データを読み取るクエリにメリットはありません。 未使用のインデックスは、追加の更新で不要なログ記録が必要になるため、システムの全体的なパフォーマンスにも影響します。
テーブルからデータを読み取り、更新への影響を最小限に抑えるクエリのパフォーマンスを向上させる最適なインデックス セットを見つけるには、継続的かつ複雑な分析が必要になる場合があります。
Azure SQL Database では、組み込みのインテリジェンスと高度なルールを使用してクエリを分析し、現在のワークロードに最適なインデックスを特定し、削除する必要がある可能性のあるインデックスを特定します。 Azure SQL Database では、データを読み取るクエリを最適化し、他のクエリへの影響を最小限に抑えながら、必要最小限のインデックスセットを確保できます。
インデックスの自動管理
検出に加えて、Azure SQL Database では、識別された推奨事項を自動的に適用できます。 組み込みの規則によってデータベースのパフォーマンスが向上する場合は、Azure SQL Database でインデックスを自動的に管理できます。
Azure SQL Database は、CREATE INDEX または DROP INDEX の推奨事項を適用すると、インデックスの影響を受けるクエリのパフォーマンスを自動的に監視します。 新しいインデックスは、影響を受けるクエリのパフォーマンスが向上した場合にのみ保持されます。 インデックスがないために実行速度が低下するクエリがある場合、削除されたインデックスは自動的に再作成されます。
インデックスの自動管理に関する考慮事項
Azure SQL Database で必要なインデックスを作成するために必要なアクションは、リソースを消費し、ワークロードのパフォーマンスに一時的に影響を与える可能性があります。 インデックスの作成がワークロードのパフォーマンスに与える影響を最小限に抑えるために、Azure SQL Database では、インデックス管理操作に適した時間枠が見つかります。 ワークロードを実行するためにデータベースにリソースが必要な場合、チューニング アクションは延期され、メンテナンス タスクに使用できる十分な未使用のリソースがデータベースにある場合は再起動されます。 インデックスの自動管理における重要な機能の 1 つは、アクションの検証です。 Azure SQL Database がインデックスを作成または削除すると、監視プロセスによってワークロードのパフォーマンスが分析され、アクションによって全体的なパフォーマンスが向上したことを確認します。 大幅な改善がもたらされなかった場合、アクションはすぐに元に戻されます。 これにより、Azure SQL Database は、自動チューニング アクションがワークロードのパフォーマンスに悪影響を与えないことを保証します。 自動チューニングによって作成されたインデックスは、基になるスキーマのメンテナンス操作に対して透過的です。 列の削除や名前変更などのスキーマの変更は、自動的に作成されたインデックスが存在することによってブロックされません。 Azure SQL Database によって自動的に作成されるインデックスは、関連するテーブルまたは列が削除されると直ちに削除されます。
代替 - 手動インデックス管理
自動インデックス管理を使用しない場合、ユーザーまたは DBA は、 Sys.dm_db_missing_index_details (Transact-SQL) ビューに対して手動でクエリを実行するか、Management Studio のパフォーマンス ダッシュボード レポートを使用して、パフォーマンスを向上させる可能性のあるインデックスを検索し、このビューで提供されている詳細を使用してインデックスを作成し、クエリのパフォーマンスを手動で監視する必要があります。 削除する必要があるインデックスを見つけるには、ユーザーはインデックスの運用上の使用状況の統計を監視して、使用頻度の低いインデックスを見つける必要があります。
Azure SQL Database では、このプロセスが簡略化されます。 Azure SQL Database はワークロードを分析し、新しいインデックスを使用して高速に実行できるクエリを識別し、未使用または重複するインデックスを識別します。 変更する必要があるインデックスの識別の詳細については、 Azure portal のインデックスの検索に関する推奨事項を参照してください。
次のステップ
- Azure SQL Database と Azure SQL Managed Instance での自動チューニング
- データベースを変更し、自動チューニングを設定する (Transact-SQL)
- sys.database_自動チューニングオプション (Transact-SQL)
- sys.dm_db_tuning_recommendations (Transact-SQL)
- sys.dm_db_missing_index_details (Transact-SQL)
- sp_query_store_force_plan(Transact-SQL)
- sys.query_store_plan_forcing_locations (クエリ ストア プラン強制ロケーション) (Transact-SQL)
- sp_query_store_unforce_plan (Transact-SQL)
- sys.database_query_store_options (Transact-SQL)
- sys.dm_os_sys_info (Transact-SQL)
- JSON 関数
- 実行プラン
- パフォーマンスの監視と調整
- パフォーマンス監視およびチューニング ツール
- クエリのストアを使用した、パフォーマンスの監視
- クエリ チューニング アシスタント