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에서 동적 관리 뷰는 데이터베이스 포함에 영향을 주는 정보를 노출하거나 사용자가 액세스할 수 있는 다른 데이터베이스에 대한 정보를 노출할 수 없습니다. 이 정보를 노출하지 않으려면 연결된 테넌트에 속하지 않는 데이터가 포함된 모든 행이 필터링됩니다.

열 이름	데이터 형식	설명
이름	nvarchar(4000)	권장 사항의 고유 이름입니다.
type	nvarchar(4000)	예를 들어 권장 사항을 생성한 자동 튜닝 옵션의 이름입니다. FORCE_LAST_GOOD_PLAN
reason	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 배율에 대한 이 권장 사항의 예상 값/효과(클수록 좋습니다.)
details	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 정보는 데이터베이스 엔진이 잠재적인 쿼리 성능 회귀를 식별하고 유지되지 않을 때 업데이트됩니다. 권장 사항은 데이터베이스 엔진이 다시 시작될 때까지만 유지됩니다. sqlserver_start_time sys.dm_os_sys_info 열을 사용하여 마지막 데이터베이스 엔진 시작 시간을 찾습니다. 데이터베이스 관리자는 서버 재활용 후 유지하려는 경우 튜닝 권장 사항의 백업 복사본을 주기적으로 만들어야 합니다.

열의 state 필드에는 currentValue 다음 값이 있을 수 있습니다.

Status	설명
Active	권장 사항이 활성 상태이며 아직 적용되지 않았습니다. 사용자는 권장 사항 스크립트를 사용하여 수동으로 실행할 수 있습니다.
Verifying	권장 사항은 데이터베이스 엔진 적용되며 내부 확인 프로세스는 강제 계획의 성능을 회귀된 계획과 비교합니다.
Success	권장 사항이 적용되었습니다.
Reverted	성능이 크게 향상되지 않으므로 권장 사항이 되돌려집니다.
Expired	권장 사항이 만료되어 더 이상 적용할 수 없습니다.

열의 state JSON 문서에는 현재 상태의 권장 사항 이유를 설명하는 이유가 포함되어 있습니다. 이유 필드의 값은 다음과 같습니다.

이유	설명
SchemaChanged	참조된 테이블의 스키마가 변경되어 권장 사항이 만료되었습니다. 새 스키마에서 새 쿼리 계획 회귀가 검색되면 새 권장 사항이 생성됩니다.
StatisticsChanged	참조된 테이블의 통계 변경으로 인해 권장 사항이 만료되었습니다. 새 통계를 기반으로 새 쿼리 계획 회귀가 검색되면 새 권장 사항이 생성됩니다.
ForcingFailed	권장 계획은 쿼리에서 강제 적용할 수 없습니다. last_force_failure_reason sys.query_store_plan 보기에서 실패 원인을 찾습니다.
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)