sys.dm_db_tuning_recommendations (Transact-SQL)
適用対象: SQL Server 2017 (14.x) 以降
Azure SQL Database
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 オプションは、検証プロセス中にユーザーによって無効になります。 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
権限が必要です。