sys.dm_db_tuning_recommendations (Transact-SQL)

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

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

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

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

列名	データの種類	説明
name	nvarchar (4000)	推奨事項の一意の名前。
type	nvarchar (4000)	推奨事項を生成した自動チューニング オプションの名前 (例: ) FORCE_LAST_GOOD_PLAN
reason	nvarchar (4000)	この推奨事項が提供された理由。
valid_since	datetime2	この推奨事項が初めて生成されたとき。
last_refresh	datetime2	この推奨事項が最後に生成された時刻。
状態	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 を使用します。 データベース管理者は、サーバーのリサイクル後に保持する場合は、チューニングの推奨事項のバックアップ コピーを定期的に作成する必要があります。

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

Status	説明
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 時間など) は表示されません。 レコメンデーションの詳細は、回帰検出時に取得され、データベース エンジンがパフォーマンスの回帰を特定した理由を説明します。 カタログ ビュークエリ ストアクエリを実行して、正確なランタイム プランの統計情報を検索するには、 と recommendedPlanId を使用regressedPlanIdします。

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

例 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です。

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

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)