自動チューニング

適用対象: SQL Server 2017 (14.x) 以降 Azure SQL DatabaseAzure SQL Managed Instance

自動チューニングは、潜在的なクエリ パフォーマンスの問題に関する洞察を提供し、解決策を推奨して、特定された問題を自動的に解決するデータベース機能です。

SQL Server 2017 (14.x) で導入された自動チューニングでは、潜在的なパフォーマンス上の問題が検出されると通知されます。また、是正措置を適用したり、データベース エンジンでパフォーマンス上の問題を自動修復したりすることもできます。 SQL Server の自動チューニングを使用すると、クエリ実行プランの選択による回帰に起因するパフォーマンス上の問題を特定して修正できます。 Azure SQL Database の自動チューニングでは、必要なインデックスの作成と使われていないインデックスの削除も実行されます。 クエリ実行プランの詳細については、「実行プラン」を参照してください。

SQL Server データベース エンジンでは、データベースで実行されるクエリが監視され、ワークロードのパフォーマンスが自動的に改善されます。 データベース エンジンにはインテリジェンス メカニズムが組み込まれており、ワークロードにデータベースを動的に適合させることで、クエリのパフォーマンスを自動的に調整して向上させることができます。 次の 2 つの自動チューニング機能を使用できます。

  • プランの自動修正では、パラメーター センシティビティやパラメーター スニッフィングの問題など、問題のあるクエリ実行プランを特定し、回帰が発生する前に正常だった最後の既知のプランを強制的に適用することで、クエリ実行プラン関連のパフォーマンス上の問題を修正します。 適用対象: SQL Server (SQL Server 2017 (14.x) 以降)、Azure SQL Database、Azure SQL Managed Instance]

  • 自動インデックス管理では、データベースに追加するインデックスと削除するインデックスを識別します。 適用対象: Azure SQL Database

自動チューニングを行う理由

従来のデータベース管理における 3 つの主なタスクは、ワークロードの監視、重要な Transact-SQL クエリの識別、およびパフォーマンスの向上のために追加する必要があるインデックスや、ほとんど使用されずパフォーマンスの向上のために削除できるインデックスの特定です。 SQL Server データベース エンジンは、監視する必要があるクエリとインデックスに関する詳しい分析情報を提供します。 ただし、データベースを常に監視することは厄介で面倒なタスクであり、多数のデータベースを処理する場合は特にそうなります。 膨大な数のデータベースの管理は、効率的に実行するのは不可能な場合があります。 データベースの監視とチューニングを手動で行う代わりに、自動チューニング機能を使用して、監視とチューニングのアクションの一部をデータベース エンジンに委任することを検討できます。

自動チューニングのしくみ

自動チューニングは継続的な監視と分析のプロセスであり、ワークロードの特性を常に学習して、潜在的な問題と改善点を特定します。

Automatic tuning process.

このプロセスにより、データベースをワークロードに動的に適合させることができます。そのために、ワークロードのパフォーマンスを向上させる可能性のあるインデックスとプラン、およびワークロードに影響を与えるインデックスが検出されます。 自動チューニングでは、これらの検出結果に基づき、ワークロードのパフォーマンスを向上させるチューニング アクションを適用します。 さらに、自動チューニングでは、ワークロードのパフォーマンスが向上することを確認するために、変更を実装した後のデータベースのパフォーマンスが継続的に監視されます。 パフォーマンスが向上しなかったすべてのアクションは、自動的に元に戻されます。 この検証プロセスは、自動チューニングによる変更がワークロードの全体的なパフォーマンスを低下させないことを保証する、カギとなる機能です。

自動プラン修正

自動プラン修正は、実行プランの選択による回帰を識別し、最後の既知の正常なプランを強制的に適用して問題を自動的に修正する自動チューニング機能です。 クエリ実行プランとクエリ オプティマイザーの詳細については、「クエリ処理アーキテクチャ ガイド」を参照してください。

重要

自動プラン修正は、ワークロード追跡のためにデータベースで有効になっているクエリ ストアに依存します。

実行プランの選択による回帰とは

SQL Server データベース エンジンでは、Transact-SQL クエリを実行するために異なる実行プランを使用する場合があります。 クエリ プランは、統計、インデックス、およびその他の要因に依存します。 Transact-SQL クエリの実行に使用すべき最適なプランは、これらの要因の変化に応じて、時間の経過と共に変化することがあります。 場合によっては、新しいプランが前のプランよりも優れておらず、新しいプランによって、パラメーター センシティビティやパラメーター スニッフィングに関連する問題などのパフォーマンス低下が発生するおそれがあります。

Query execution plan choice regression.

プランの選択による回帰の発生に気付いたときは常に、以前の適切なプランを見つけて、現在のプランの代わりにそれが強制的に使用されるようにする必要があります。 これは、sp_query_store_force_plan プロシージャを使用して実行できます。 SQL Server 2017 (14.x) のデータベース エンジンでは、回帰したプランと推奨される是正措置に関する情報が提供されます。 さらに、データベース エンジンを使用すると、このプロセスを完全に自動化し、プランの変更に関連して検出されたあらゆる問題をデータベース エンジンに修正させることができます。

重要

ワークロードのアップグレード リスクを自動的に軽減するために、ベースラインをキャプチャした後に、データベース互換レベル アップグレードのスコープでプランの自動修正を使用する必要があります。 このユース ケースの詳細については、「新しい SQL Server へのアップグレード中にパフォーマンスの安定性を維持する」を参照してください。

自動プラン選択の修正

データベース エンジンでは、プランの選択による回帰が検出されると常に、最後の既知の正常なプランに自動的に切り替えることができます。

Query execution plan choice correction.

データベース エンジンでは、正しくないプランの代わりに使う必要があるプランなど、プランの選択による回帰の可能性を自動的に検出します。 自動プラン修正によって強制的に適用される、生成される実行プランは、最後の既知の正常なプランと同じか、似たものになります。 生成されるプランは、最後の既知の正常なプランと同じではない可能性があるため、適用されたプランのパフォーマンスは変化することがあります。 まれに、パフォーマンスの違いが大きく、悪影響を与える場合があります。この場合、自動プラン修正は自動的に代替プランの強制的な試行を停止します。

データベース エンジンによって回帰が発生する前の最後の既知の正常なプランが適用される場合、適用されたプランのパフォーマンスが自動的に監視されます。 適用されたプランが回帰したプランよりも優れていない場合、新しいプランは適用解除され、データベース エンジンによって新しいプランがコンパイルされます。 適用されたプランが回帰したプランよりも優れていることがデータベース エンジンによって確認された場合、適用されたプランは保持されます。 それは、再コンパイルが行われるまで (たとえば、次の統計の更新やスキーマの変更など) 保持されます。 プランの適用と適用できるプランの種類について詳しくは、「プランの適用の制限事項」を参照してください。

注意

プランの適用アクションが検証される前に 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 プロシージャを使用してプランを適用解除し、データベース エンジンが最適なプランを見つけられるようにする必要があります。

ヒント

または、[強制されたプランを持つクエリ] クエリ ストア ビューを使って、プランを見つけて適用解除します。

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 ステートメントなどの詳細が表示されます。

type 説明 DATETIME スコア 詳細 ...
FORCE_LAST_GOOD_PLAN CPU 時間が 4 ms から 14 ms に変化しました 2017/3/17 83 queryId recommendedPlanId regressedPlanId T-SQL
FORCE_LAST_GOOD_PLAN CPU 時間が 37 ms から 84 ms に変化しました 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 スコア script query_id 現在の plan_id 推奨される plan_id estimated_gain error_prone
CPU 時間が 3 ms から 46 ms に変化しました 36 EXEC sp_query_store_force_plan 12, 17; 12 28 17 11.59 0

estimated_gain は、クエリ実行に現在のプランではなく推奨プランが使用された場合に短縮される推定秒数を表します。 ゲインが 10 秒を超える場合は、現在のプランではなく推奨プランを適用すべきです。 現在のプランに推奨プランよりも多くのエラー (たとえば、タイムアウトや中止された実行など) があった場合、列 error_prone は値 YES に設定されます。 エラーが発生しやすいプランは、現在のプランではなく推奨プランを適用すべきであるもう 1 つの理由となります。

データベース エンジンによってプランの選択による回帰を特定するために必要なすべての情報が提供されますが、継続的な監視とパフォーマンス上の問題の修正は、面倒なプロセスになることがあります。 自動チューニングによって、このプロセスがはるかに簡単になります。

注意

sys.dm_db_tuning_recommendations DMV 内のデータは、データベース エンジンの再起動後に保持されません。 データベース エンジンが最後に起動された時刻を調べるには、sys.dm_os_sys_infosqlserver_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 を使用した SQL Database Advisor」参照してください。

次のステップ