sys.dm_db_チューニング_(TRANSACT-SQL) の推奨事項sys.dm_db_tuning_recommendations (Transact-SQL)

適用対象: ○SQL Server ○Azure SQL Database XAzure SQL Data Warehouse XParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

チューニングの推奨事項に関する詳細な情報を返します。Returns detailed information about tuning recommendations.

Azure SQL データベースAzure SQL Databaseでは、動的管理ビューでデータベースの包含に影響を与える情報を公開することや、ユーザーがアクセスできる他のデータベースに関する情報を公開することはできません。In Azure SQL データベースAzure SQL Database, dynamic management views cannot expose information that would impact database containment or expose information about other databases the user has access to. この情報を公開することを避けるため、接続されているテナントに属していないデータが含まれるすべての行はフィルターで除外します。To avoid exposing this information, every row that contains data that doesn't belong to the connected tenant is filtered out.

列名Column name データ型Data type [説明]Description
namename nvarchar (4000)nvarchar(4000) 推奨設定の一意の名前。Unique name of recommendation.
typetype nvarchar (4000)nvarchar(4000) たとえば、推奨を生成した自動チューニング オプションの名前 FORCE_LAST_GOOD_PLANThe name of the automatic tuning option that produced the recommendation, for example, FORCE_LAST_GOOD_PLAN
reasonreason nvarchar (4000)nvarchar(4000) なぜこの推奨事項が提供されている理由です。Reason why this recommendation was provided.
valid_sincevalid_since datetime2datetime2 最初にこの推奨事項が生成されました。The first time this recommendation was generated.
last_refreshlast_refresh datetime2datetime2 この推奨事項が生成された最後の時刻。The last time this recommendation was generated.
statestate nvarchar (4000)nvarchar(4000) 推奨事項の状態を記述する JSON ドキュメントです。JSON document that describes the state of the recommendation. 次のフィールドを使用できます。Following fields are available:
- currentValue -推奨事項の現在の状態。- currentValue - current state of the recommendation.
- reason -定数を推奨事項は、現在の状態の理由について説明します。- reason - constant that describes why the recommendation is in the current state.
is_executable_actionis_executable_action bitbit 1 = を使用してデータベースに対して実行できる推奨事項Transact-SQLTransact-SQLスクリプト。1 = The recommendation can be executed against the database via Transact-SQLTransact-SQL script.
0 = データベースに対して、推奨事項を実行することはできません (例: についてのみ、または元に戻された推奨事項)0 = The recommendation cannot be executed against the database (for example: information only or reverted recommendation)
_revertable_アクションis_revertable_action bitbit 1 = 推奨事項を自動的に監視し、データベース エンジンによって元に戻されます。1 = The recommendation can be automatically monitored and reverted by Database engine.
0 = 推奨事項を自動的に監視および元に戻すできることはできません。0 = The recommendation cannot be automatically monitored and reverted. ほとんど"実行可能ファイル"アクション"revertable"します。Most "executable" actions will be "revertable".
execute_action_start_timeexecute_action_start_time datetime2datetime2 推奨事項の適用の日付。Date the recommendation is applied.
execute_action_durationexecute_action_duration timetime Execute アクションは、の期間です。Duration of the execute action.
execute_action_initiated_byexecute_action_initiated_by nvarchar (4000)nvarchar(4000) User = ユーザーは、推奨設定でプランを手動で適用します。User = User manually forced plan in the recommendation.
System = システムは、推奨事項を自動的に適用されます。System = System automatically applied recommendation.
execute_action_initiated_timeexecute_action_initiated_time datetime2datetime2 推奨事項が適用された日付。Date the recommendation was applied.
元に戻す_アクション_開始_時間revert_action_start_time datetime2datetime2 日付は、推奨事項が元に戻されます。Date the recommendation was reverted.
元に戻す_アクション_期間revert_action_duration timetime 元に戻す操作の期間です。Duration of the revert action.
revert_action_initiated_byrevert_action_initiated_by nvarchar (4000)nvarchar(4000) User = 推奨プランの手動で unforced ユーザー。User = User manually unforced recommended plan.
System = システムは、推奨事項を自動的に元に戻されます。System = System automatically reverted recommendation.
元に戻す_アクション_開始_時間revert_action_initiated_time datetime2datetime2 日付は、推奨事項が元に戻されます。Date the recommendation was reverted.
scorescore intint この推奨事項では、0 ~ 100 の値または影響を推定スケール (が大きいほど良い)Estimated value/impact for this recommendation on the 0-100 scale (the larger the better)
detailsdetails nvarchar(max)nvarchar(max) 推奨事項の詳細を含む JSON ドキュメントです。JSON document that contains more details about the recommendation. 次のフィールドを使用できます。Following fields are available:

planForceDetails
- queryId -クエリ_後退したクエリの id。- queryId - query_id of the regressed query.
- regressedPlanId -後退したプランの plan_id します。- regressedPlanId - plan_id of the regressed plan.
- regressedPlanExecutionCount -、回帰の前に、後退したプランとクエリの実行の数が検出されました。- regressedPlanExecutionCount - Number of executions of the query with regressed plan before the regression is detected.
- regressedPlanAbortedCount -後退したプランの実行中にエラーが検出された数。- regressedPlanAbortedCount - Number of detected errors during the execution of the regressed plan.
- regressedPlanCpuTimeAverage -平均 CPU 時間が、回帰が検出される前に、後退したクエリで使用します。- regressedPlanCpuTimeAverage - Average CPU time consumed by the regressed query before the regression is detected.
- regressedPlanCpuTimeStddev 標準偏差、回帰の前に、後退したクエリによって消費される CPU 時間が検出されました。- regressedPlanCpuTimeStddev - Standard deviation of CPU time consumed by the regressed query before the regression is detected.
- recommendedPlanId -plan_id プランを強制する必要があります。- recommendedPlanId - plan_id of the plan that should be forced.
- recommendedPlanExecutionCount-プランの回帰が検出される前に強制する必要がありますを使用して、クエリの実行回数です。- recommendedPlanExecutionCount- Number of executions of the query with the plan that should be forced before the regression is detected.
- recommendedPlanAbortedCount -適用する必要があるプランの実行中にエラーが検出された数。- recommendedPlanAbortedCount - Number of detected errors during the execution of the plan that should be forced.
- recommendedPlanCpuTimeAverage -平均 CPU 時間を強制する必要があります (計算、回帰が検出される前に) プランで実行されるクエリによって消費されます。- recommendedPlanCpuTimeAverage - Average CPU time consumed by the query executed with the plan that should be forced (calculated before the regression is detected).
- recommendedPlanCpuTimeStddev 回帰直線の前に、後退したクエリで使用された CPU 時間の標準偏差が検出されました。- recommendedPlanCpuTimeStddev Standard deviation of CPU time consumed by the regressed query before the regression is detected.

implementationDetails
- method -メソッド、回帰を修正するために使用する必要があります。- method - The method that should be used to correct the regression. 値は常にTSqlします。Value is always TSql.
- script - Transact-SQLTransact-SQL 推奨されるプランを強制的に実行されるスクリプトです。- script - Transact-SQLTransact-SQL script that should be executed to force the recommended plan.

コメントRemarks

によって返される情報sys.dm_db_tuning_recommendationsデータベース エンジンは、潜在的なクエリ パフォーマンスの低下を識別し、永続化されていないときに更新されます。Information returned by sys.dm_db_tuning_recommendations is updated when database engine identifies potential query performance regression, and is not persisted. 推奨事項がまでのみ保持SQL ServerSQL Serverを再起動します。Recommendations are kept only until SQL ServerSQL Server is restarted. データベース管理者は、サーバーの再利用後も保持する場合は、チューニング推奨設定のバックアップ コピーを定期的に作成する必要があります。Database administrators should periodically make backup copies of the tuning recommendation if they want to keep it after server recycling.

currentValue フィールドに、state列は、次の値がある可能性があります。currentValue field in the state column might have the following values:

状態Status 説明Description
Active アクティブであり、まだ適用されていません。 お勧めします。Recommendation is active and not yet applied. ユーザーは、推奨設定スクリプトを作成し、それを手動で実行できます。User can take recommendation script and execute it manually.
Verifying 推奨事項の適用によってデータベース エンジンDatabase Engineと内部の検証プロセスが機能低下したプランの強制プランのパフォーマンスを比較します。Recommendation is applied by データベース エンジンDatabase Engine and internal verification process compares performance of the forced plan with the regressed plan.
Success 推奨事項が正常に適用されます。Recommendation is successfully applied.
Reverted パフォーマンスが著しく向上することがないために、推奨事項は元に戻します。Recommendation is reverted because there are no significant performance gains.
Expired 推奨事項は、有効期限が切れたし、もはや適用することはできません。Recommendation has expired and cannot be applied anymore.

JSON ドキュメントstate列が現在の状態で、推奨事項である理由を説明する理由が含まれています。JSON document in state column contains the reason that describes why is the recommendation in the current state. [理由] フィールドの値は次のようになります。Values in the reason field might be:

ReasonReason 説明Description
SchemaChanged 推奨事項には、参照先のテーブルのスキーマが変更されたため、有効期限が切れました。Recommendation expired because the schema of a referenced table is changed.
StatisticsChanged 推奨事項は、参照先のテーブルで統計を変更したため有効期限が切れました。Recommendation expired due to the statistic change on a referenced table.
ForcingFailed 推奨されるプランは、クエリに強制することはできません。Recommended plan cannot be forced on a query. 検索、last_force_failure_reasonで、 sys.query_store_plan失敗の理由を確認するビュー。Find the last_force_failure_reason in the sys.query_store_plan view to find the reason of the failure.
AutomaticTuningOptionDisabled FORCE_LAST_GOOD_PLAN オプションは、検証プロセス中に、ユーザーが無効です。FORCE_LAST_GOOD_PLAN option is disabled by the user during verification process. 有効にするFORCE_LAST_GOOD_PLANオプションを使用してAUTOMATIC_TUNING 設定データベースの ALTER (TRANSACT-SQL) ステートメントまたは強制的に、プラン内のスクリプトを使用して手動で[details]列。Enable FORCE_LAST_GOOD_PLAN option using ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) statement or force the plan manually using the script in [details] column.
UnsupportedStatementType クエリにプランを強制することはできません。Plan cannot be forced on the query. サポートされていないクエリの例は、カーソルとINSERT BULKステートメント。Examples of unsupported queries are cursors and INSERT BULK statement.
LastGoodPlanForced 推奨事項が正常に適用されます。Recommendation is successfully applied.
AutomaticTuningOptionNotEnabled データベース エンジンDatabase Engine 潜在的なパフォーマンスの低下、識別されたが、FORCE_LAST_GOOD_PLANオプションが有効になっていないを参照してください - AUTOMATIC_TUNING 設定データベースの ALTER (TRANSACT-SQL)します。identified potential performance regression, but the FORCE_LAST_GOOD_PLAN option is not enabled - see ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL). 推奨事項を手動で適用または有効にするFORCE_LAST_GOOD_PLANオプション。Apply recommendation manually or enable FORCE_LAST_GOOD_PLAN option.
VerificationAborted 検証プロセスが再起動またはクエリ ストアのクリーンアップにより中止されました。Verification process is aborted due to the restart or Query Store cleanup.
VerificationForcedQueryRecompile クエリは、大幅なパフォーマンス向上がないために再コンパイルされます。Query is recompiled because there is no significant performance improvement.
PlanForcedByUser プランを使用して、ユーザーが手動で強制sp_query_store_force_plan (TRANSACT-SQL) プロシージャ。User manually forced the plan using sp_query_store_force_plan (Transact-SQL) procedure.
PlanUnforcedByUser プランを使用して、ユーザーが手動で動作sp_query_store_unforce_plan (TRANSACT-SQL) プロシージャ。User manually unforced the plan using sp_query_store_unforce_plan (Transact-SQL) procedure.

[詳細] 列の統計では、プランのランタイム統計情報 (たとえば、現在の CPU 時間) は表示されません。Statistic in the details column do not show runtime plan statistics (for example, current CPU time). 推奨事項の詳細の回帰の検出時に行われ、理由について説明しますデータベース エンジンDatabase Engineパフォーマンスの低下を識別します。The recommendation details are taken at the time of regression detection and describe why データベース エンジンDatabase Engine identified performance regression. 使用regressedPlanIdrecommendedPlanIdクエリにクエリ ストアのカタログ ビュープランの正確なランタイム統計情報を検索します。Use regressedPlanId and recommendedPlanId to query Query Store catalog views to find exact runtime plan statistics.

チューニング推奨設定の情報を使用する例Examples of using tuning recommendations information

例 1Example 1

次に、生成された取得Transact-SQLTransact-SQLクエリの適切なプランを強制するスクリプト。The following gets the generated Transact-SQLTransact-SQL script that forces a good plan for any given query:

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';

例 2Example 2

次に、生成された取得Transact-SQLTransact-SQLスクリプトは、指定されたクエリと推定利益に関する追加情報の適切なプランを強制します。The following gets the generated Transact-SQLTransact-SQL script that forces a good plan for any given query and additional information about the estimated gain:

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;

例 3Example 3

次に、生成された取得Transact-SQLTransact-SQLスクリプトは、指定されたクエリとクエリ テキストを含む追加の情報の適切なプランとクエリのストアに格納されているクエリ プランを強制します。The following gets the generated Transact-SQLTransact-SQL script that forces a good plan for any given query and additional information that includes the query text and the query plans stored in Query Store:

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 サポートデータベース エンジンDatabase Engineします。For more information about JSON functions that can be used to query values in the recommendation view, see JSON Support in データベース エンジンDatabase Engine.

アクセス許可Permissions

必要がありますVIEW SERVER STATE権限SQL ServerSQL Serverします。Requires VIEW SERVER STATE permission in SQL ServerSQL Server.
必要があります、VIEW DATABASE STATEで、データベースに対する権限Azure SQL データベースAzure SQL Databaseします。Requires the VIEW DATABASE STATE permission for the database in Azure SQL データベースAzure SQL Database.

参照See Also

自動チューニング Automatic Tuning
sys.database_automatic_tuning_options (TRANSACT-SQL) sys.database_automatic_tuning_options (Transact-SQL)
sys.database_query_store_options (TRANSACT-SQL) sys.database_query_store_options (Transact-SQL)
JSON のサポートJSON Support