sys.dm_db_tuning_recommendations (Transact-SQL)

適用対象: SQL Server 2017 (14.x) 以降 Azure SQL データベース Azure SQL Managed Instance

自動チューニングの推奨事項に関する詳細情報を返します。 詳細については、「 自動チューニング」を参照してください。

詳細については、「 Azure SQL Database と Azure SQL Managed Instance での監視とパフォーマンスのチューニングを参照してください。

Azure SQL Database では、動的管理ビューでは、データベースの包含に影響する情報を公開したり、ユーザーがアクセスできる他のデータベースに関する情報を公開したりすることはできません。 この情報が公開されないようにするために、接続されているテナントに属していないデータを含む行はすべてフィルタリングされます。

列名	データの種類	説明
name	nvarchar (4000)	推奨事項の一意の名前。
type	nvarchar (4000)	推奨事項を生成した自動チューニング オプションの名前。次に例を示します。 FORCE_LAST_GOOD_PLAN
理由	nvarchar (4000)	この推奨事項が提供された理由。
valid_since	datetime2	この推奨事項が初めて生成されたとき。
last_refresh	datetime2	この推奨事項が最後に生成された時刻。
state	nvarchar (4000)	推奨事項の状態を説明する JSON ドキュメント。 次のフィールドを使用できます。 - currentValue - 推奨事項の現在の状態。 - reason - 推奨事項が現在の状態である理由を示す定数。
is_executable_action	bit	1 = 推奨事項は、Transact-SQL スクリプトを使用してデータベースに対して実行できます。 0 = データベースに対して推奨事項を実行することはできません (例: 情報のみまたは元に戻された推奨事項)
is_revertable_action	bit	1 = 推奨事項は、データベース エンジンによって自動的に監視および元に戻すことができます。 0 = 推奨事項を自動的に監視および元に戻すことはできません。 ほとんどの 実行可能 アクションは に対応できます。
execute_action_start_time	datetime2	推奨事項が適用された日付。
execute_action_duration	time	実行アクションの期間。
execute_action_initiated_by	nvarchar (4000)	User = 推奨事項でユーザーが手動で強制的に計画します。 System = システムによって自動的に適用される推奨事項。
execute_action_initiated_time	datetime2	推奨事項が適用された日付。
revert_action_start_time	datetime2	推奨事項が元に戻された日付。
revert_action_duration	time	元に戻す操作の期間。
revert_action_initiated_by	nvarchar (4000)	User = ユーザーが推奨プランを手動で強制解除します。 System = システムが自動的に推奨を元に戻しました。
revert_action_initiated_time	datetime2	推奨事項が元に戻された日付。
スコア	int	0 から 100 のスケールに対するこの推奨事項の推定値/効果 (大きいほど良い)
詳細	nvarchar(max)	推奨事項の詳細を含む JSON ドキュメント。 次のフィールドを使用できます。 planForceDetails - queryId - 後退したクエリのquery_id。 - regressedPlanId - 後退したプランのplan_id。 - regressedPlanExecutionCount - 回帰が検出される前の、後退したプランを含むクエリの実行回数。 - regressedPlanAbortedCount - 後退したプランの実行中に検出されたエラーの数。 - regressedPlanCpuTimeAverage - 回帰が検出されるまでの、後退したクエリによって消費された平均 CPU 時間 (マイクロ秒)。 - regressedPlanCpuTimeStddev - 回帰が検出される前に、後退したクエリによって消費される CPU 時間の標準偏差。 - recommendedPlanId - 強制する計画のplan_id。 - recommendedPlanExecutionCount- 回帰が検出される前に強制的に実行する必要があるプランでのクエリの実行回数。 - recommendedPlanAbortedCount - 強制的に実行する必要があるプランの実行中に検出されたエラーの数。 - recommendedPlanCpuTimeAverage - プランで実行されたクエリによって消費される平均 CPU 時間 (マイクロ秒) です。この時間は、強制する必要があります (回帰が検出される前に計算されます)。 - recommendedPlanCpuTimeStddev 回帰が検出される前に、後退したクエリによって消費される CPU 時間の標準偏差。 implementationDetails - method - 回帰を修正するために使用するメソッド。 値は常に TSql。 - script - 推奨されるプランを強制するために実行する必要がある Transact-SQL スクリプト。

解説

sys.dm_db_tuning_recommendationsによって返される情報は、データベース エンジンが潜在的なクエリ パフォーマンスの回帰を識別し、永続化されていない場合に更新されます。 推奨事項は、データベース エンジンが再起動されるまでのみ保持されます。 データベース エンジンが最後に起動された時刻を調べるには、sys.dm_os_sys_info の sqlserver_start_time を使用します。 データベース管理者は、サーバーのリサイクル後に保持する場合は、チューニングの推奨事項のバックアップ コピーを定期的に作成する必要があります。

state列の currentValue フィールドには、次の値が含まれる場合があります。

状態	説明
Active	推奨事項はアクティブであり、まだ適用されていません。 ユーザーはレコメンデーション スクリプトを実行し、手動で実行できます。
Verifying	推奨事項はデータベース エンジンによって適用され、内部検証プロセスでは、強制プランのパフォーマンスと後退したプランが比較されます。
Success	推奨事項が正常に適用されました。
Reverted	パフォーマンスが大幅に向上しないため、推奨事項は元に戻されます。
Expired	推奨事項の有効期限が切れ、適用できなくなります。

state列の JSON ドキュメントには、推奨事項が現在の状態である理由を説明する理由が含まれています。 理由フィールドの値は次のようになります。

理由	説明
SchemaChanged	参照先テーブルのスキーマが変更されたため、推奨事項の有効期限が切れています。 新しいスキーマで新しいクエリ プランの回帰が検出された場合は、新しい推奨事項が作成されます。
StatisticsChanged	参照先テーブルの統計情報の変更により、推奨事項の有効期限が切れています。 新しい統計に基づいて新しいクエリ プランの回帰が検出されると、新しい推奨事項が作成されます。
ForcingFailed	推奨プランをクエリに対して強制することはできません。 sys.query_store_plan ビューでlast_force_failure_reasonを見つけて、エラーの原因を見つけます。
AutomaticTuningOptionDisabled	FORCE_LAST_GOOD_PLAN オプションは、検証プロセス中にユーザーによって無効になります。 ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) ステートメントを使用してFORCE_LAST_GOOD_PLANオプションを有効にするか、details列のスクリプトを使用してプランを手動で強制します。
UnsupportedStatementType	クエリに対してプランを強制することはできません。 サポートされていないクエリの例としては、カーソルと INSERT BULK ステートメントがあります。
LastGoodPlanForced	推奨事項が正常に適用されました。
AutomaticTuningOptionNotEnabled	潜在的なパフォーマンス低下データベース エンジン特定されましたが、FORCE_LAST_GOOD_PLAN オプションが有効になっていません。ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL)を参照してください。 推奨事項を手動で適用するか、 FORCE_LAST_GOOD_PLAN オプションを有効にします。
VerificationAborted	再起動またはクエリ ストアのクリーンアップにより、検証プロセスが中止されます。
VerificationForcedQueryRecompile	パフォーマンスが大幅に向上しないため、クエリが再コンパイルされます。
PlanForcedByUser	ユーザーは、 sp_query_store_force_plan (Transact-SQL) プロシージャを使用してプランを手動で強制しました。 ユーザーが何らかのプランを強制することを明示的に決定した場合、データベース エンジンは推奨事項を適用しません。
PlanUnforcedByUser	ユーザーは、 sp_query_store_unforce_plan (Transact-SQL) プロシージャを使用して、プランを手動で強制解除しました。 ユーザーが推奨プランを明示的に元に戻したので、データベース エンジンは現在のプランを引き続き使用し、将来何らかのプランの回帰が発生した場合に新しい推奨事項を生成します。
UserForcedDifferentPlan	sp_query_store_force_plan (Transact-SQL) プロシージャを使用して、ユーザーが別のプランを手動で強制しました。 ユーザーが何らかのプランを強制することを明示的に決定した場合、データベース エンジンは推奨事項を適用しません。
TempTableChanged	プランで使用された一時テーブルが変更されます。

details列の統計には、ランタイム プランの統計情報 (現在の CPU 時間など) は表示されません。 推奨事項の詳細は、回帰検出時に取得され、パフォーマンスの低下データベース エンジン特定された理由を説明します。 regressedPlanIdとrecommendedPlanIdを使用して、クエリ ストアカタログ ビューに対してクエリを実行し正確なランタイム プランの統計情報を検索します。

チューニングに関する推奨事項情報の使用例

例 1

次のサンプル コードでは、特定のクエリに対して適切なプランを強制する、生成された Transact-SQL スクリプトを取得します。

SELECT name,
    reason,
    score,
    JSON_VALUE(details, '$.implementationDetails.script') AS script,
    details.*
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON(details, '$.planForceDetails') WITH (
        [query_id] INT '$.queryId',
        regressed_plan_id INT '$.regressedPlanId',
        last_good_plan_id INT '$.recommendedPlanId'
        ) AS details
WHERE JSON_VALUE(STATE, '$.currentValue') = 'Active';

例 2

次に示す Transact-SQL スクリプトは、特定のクエリに対して適切なプランを強制的に作成し、推定ゲインに関する追加情報を取得します。

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;

例 3

次の例では、特定のクエリに対して適切なプランを強制する生成された Transact-SQL スクリプトと、クエリ テキストとクエリ ストアに格納されているクエリ プランを含む追加情報を取得します。

WITH cte_db_tuning_recommendations
AS (
    SELECT reason,
        score,
        query_id,
        regressedPlanId,
        recommendedPlanId,
        current_state = JSON_VALUE(STATE, '$.currentValue'),
        current_state_reason = JSON_VALUE(STATE, '$.reason'),
        script = JSON_VALUE(details, '$.implementationDetails.script'),
        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
            )
    )
SELECT qsq.query_id,
    qsqt.query_sql_text,
    dtr.*,
    CAST(rp.query_plan AS XML) AS RegressedPlan,
    CAST(sp.query_plan AS XML) AS SuggestedPlan
FROM cte_db_tuning_recommendations AS dtr
INNER JOIN sys.query_store_plan AS rp
    ON rp.query_id = dtr.query_id
        AND rp.plan_id = dtr.regressedPlanId
INNER JOIN sys.query_store_plan AS sp
    ON sp.query_id = dtr.query_id
        AND sp.plan_id = dtr.recommendedPlanId
INNER JOIN sys.query_store_query AS qsq
    ON qsq.query_id = rp.query_id
INNER JOIN sys.query_store_query_text AS qsqt
    ON qsqt.query_text_id = qsq.query_text_id;

推奨事項ビューの値のクエリに使用できる JSON 関数の詳細については、データベース エンジンのJSON サポートを参照してください。

アクセス許可

SQL Server VIEW SERVER STATE アクセス許可が必要です。

Azure SQL Database のデータベースに対する VIEW DATABASE STATE アクセス許可が必要です。

SQL Server 2022 以降でのアクセス許可

サーバーに対する VIEW SERVER PERFORMANCE STATE 権限が必要です。

次のステップ

• 自動チューニング
• sys.database_automatic_tuning_options (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• JSON のサポート
• sys.dm_os_sys_info (Transact-SQL)