변경 내용 추적 자동 클린업 문제 해결

아티클
01/04/2024

적용 대상:SQL Server Azure SQL DatabaseAzure SQL Managed Instance

이 문서에서는 자동 클린 변경 내용 추적에서 관찰되는 일반적인 문제를 해결하는 방법을 제공합니다.

증상

일반적으로 자동 클린up이 예상대로 작동하지 않는 경우 다음 증상 중 하나 이상을 볼 수 있습니다.

하나 이상의 변경 내용 추적 쪽 테이블 또는 syscommittab 시스템 테이블에 의한 높은 스토리지 사용.
사이드 테이블(이름 앞에 접두 change_tracking사로 change_tracking_12345시작하는 내부 테이블) 또는 syscommittab 둘 다 구성된 보존 기간을 벗어나는 많은 수의 행을 표시합니다.
dbo.MSChange_tracking_history테이블에는 특정 클린up 오류가 있는 항목이 있습니다.
CHANGETABLE 시간이 지남에 따라 성능이 저하되었습니다.
자동 클린up 또는 수동 클린up은 높은 CPU 사용량을 보고합니다.

디버깅 및 완화

변경 내용 추적 자동 클린 문제의 근본 원인을 식별하려면 다음 단계를 사용하여 문제를 디버그하고 완화합니다.

자동 클린업 상태

자동 클린 실행 중인지 확인합니다. 이를 검사 위해 동일한 데이터베이스의 클린up 기록 테이블을 쿼리합니다. 클린up이 실행 중이면 테이블에 클린 시작 및 종료 시간이 포함된 항목이 있습니다. 클린up이 실행되지 않은 경우 테이블이 비어 있거나 부실 항목이 있습니다. 기록 테이블에 열comments에 태그 cleanup errors 가 있는 항목이 있는 경우 테이블 수준 클린up 오류로 인해 클린up이 실패합니다.

SELECT TOP 1000 * FROM dbo.MSChange_tracking_history ORDER BY start_time DESC;

자동 클린은 기본 간격인 30분으로 주기적으로 실행됩니다. 기록 테이블이 없는 경우 자동 클린up이 실행되지 않을 가능성이 높습니다. 그렇지 않으면 열 값을 검사 start_timeend_time. 최신 항목이 최신 항목이 아닌 경우, 즉 몇 시간 또는 며칠이 지난 경우 자동 클린up이 실행되지 않을 수 있습니다. 이 경우 다음 단계를 사용하여 문제를 해결합니다.

1. 정리가 꺼져 있습니다.

데이터베이스에 대한 자동 정리가 설정되었는지 확인합니다. 그렇지 않은 경우에는 해당 설정을 실행하고 30분 이상 기다린 후에 기록 테이블에서 새 항목을 찾아봅니다. 이후 기록 테이블의 진행률을 모니터링합니다.

SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID('<database_name>')

0이 아닌 값 is_auto_cleanup_on 은 자동 클린up이 사용하도록 설정되어 있음을 나타냅니다. 보존 기간 값은 변경 내용 추적 메타데이터가 시스템에 보존되는 기간을 제어합니다. 변경 내용 추적 보존 기간의 기본값은 2일입니다.

변경 내용 추적을 사용하거나 사용하지 않도록 설정하려면 변경 내용 추적 사용 및 사용 안 함(SQL Server)을 참조하세요.

2. 정리가 켜져 있지만 실행되지 않음

자동 클린up이 켜진 경우 예기치 않은 오류로 인해 자동 클린up 스레드가 중지되었을 수 있습니다. 현재 자동 클린업 스레드를 다시 시작하는 것은 불가능합니다. 보조 서버에 대한 장애 조치(failover)를 시작하고(또는 보조 서버가 없는 경우 서버를 다시 시작) 데이터베이스에 대해 자동 클린up 설정이 사용하도록 설정되어 있는지 확인해야 합니다.

자동 클린 실행하지만 진행되지 않음

하나 이상의 사이드 테이블이 상당한 스토리지 사용량을 표시하거나 구성된 보존 기간을 초과하는 많은 수의 레코드를 포함하는 경우 이 섹션의 단계에 따라 단일 쪽 테이블에 대한 구제를 설명합니다. 필요한 경우 더 많은 테이블에 대해 동일한 단계를 반복할 수 있습니다.

1. 자동 클린업 백로그 평가

만료된 레코드의 백로그가 큰 사이드 테이블을 식별합니다. 이 테이블에 대해 완화를 수행해야 합니다. 다음 쿼리를 실행하여 만료된 레코드 수가 큰 사이드 테이블을 식별합니다. 예제 스크립트의 값을 표시된 대로 바꿔야 합니다.

잘못된 클린업 버전을 가져옵니다.
```
SELECT * FROM sys.change_tracking_tables;
```
반환된 행의 값은 cleanup_version 잘못된 클린up 버전을 나타냅니다.

다음 동적 T-SQL(Transact-SQL) 쿼리를 실행하여 쿼리를 생성하여 사이드 테이블의 만료된 행 수를 가져옵니다. 쿼리의 <invalid_version> 값을 이전 단계에서 얻은 값으로 바꿉다.

SELECT 'SELECT ''' + QUOTENAME(name) + ''', count(*) FROM [sys].' + QUOTENAME(name)
    + ' WHERE sys_change_xdes_id IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts <= <invalid_version>) UNION'
FROM sys.internal_tables
WHERE internal_type = 209;

이전 쿼리에서 결과 집합을 복사하고 마지막 행에서 키워드(keyword) 제거 UNION 합니다. DAC(전용 관리자 연결)를 통해 생성된 T-SQL 쿼리를 실행하는 경우 쿼리는 모든 사이드 테이블의 만료된 행 수를 제공합니다. 테이블의 sys.syscommittab 크기와 사이드 테이블 수에 따라 이 쿼리를 완료하는 데 시간이 오래 걸릴 수 있습니다.

Important

이 단계는 완화 단계를 진행하기 위해 필요합니다. 이전 쿼리가 실행되지 않는 경우 다음에 지정된 쿼리를 사용하여 개별 사이드 테이블의 만료된 행 수를 식별합니다.

만료된 행 수가 자동 클린 따라 잡기 위해 관리 가능한 상태로 내려올 때까지 만료된 행 개수의 감소 순서를 갖는 사이드 테이블에 대해 다음 완화 단계를 수행합니다.

만료된 레코드 수가 큰 사이드 테이블을 식별하면 사이드 테이블 삭제 문의 대기 시간 및 지난 몇 시간 동안의 초당 삭제 속도에 대한 정보를 수집합니다. 다음으로 부실 행 수와 삭제 대기 시간을 모두 고려하여 사이드 테이블을 클린 데 필요한 시간을 예측합니다.

매개 변수 템플릿을 적절한 값으로 대체하여 다음 T-SQL 코드 조각을 사용합니다.

초당 클린 증가율을 쿼리합니다.

SELECT
    table_name,
    rows_cleaned_up / ISNULL(NULLIF(DATEDIFF(second, start_time, end_time), 0), 1),
    cleanup_version
FROM dbo.MSChange_tracking_history
WHERE table_name = '<table_name>'
ORDER BY end_time DESC;

함수에 분 또는 시간 단위를 DATEDIFF 사용할 수도 있습니다.

추가 테이블에서 부실 행 수를 찾습니다. 해당 쿼리는 정리 보류 중인 행 수 찾기에 도움을 줍니다.

<cleanup_version> 사용자 테이블은 <internal_table_name> 이전 섹션에서 반환된 출력에 있습니다. 이 정보를 사용하여 전용 DAC(관리자 연결)를 통해 다음 T-SQL 코드를 실행합니다.
```
SELECT '<internal_table_name>',
    COUNT(*)
FROM sys.<internal_table_name>
WHERE sys_change_xdes_id IN (
        SELECT xdes_id
        FROM sys.syscommittab ssct
        WHERE ssct.commit_ts <= <cleanup_version>
);
```
이 쿼리를 완료하는 데 다소 시간이 걸릴 수 있습니다. 쿼리 시간이 초과되는 경우 총 행과 활성 행, 즉 클린 행 간의 차이를 찾아 부실 행을 계산합니다.

다음 쿼리를 실행하여 사이드 테이블의 총 행 수를 찾습니다.

SELECT sum(row_count) FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('sys.<internal_table_name>')
GROUP BY partition_id;

다음 쿼리를 실행하여 사이드 테이블의 활성 행 수를 찾습니다.
```
SELECT '<internal_table_name>', COUNT(*) FROM sys.<internal_table_name> WHERE sys_change_xdes_id
IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts > <cleanup_version>);
```
클린 및 부실 행 수를 사용하여 테이블을 클린 예상 시간을 계산할 수 있습니다. 다음 수식을 고려합니다.

분 단위로 클린 시간 = (부실 행 수) / (클린 분 단위의 비율)

테이블 클린up을 완료하는 시간이 허용되는 경우 진행률을 모니터링하고 자동 클린업이 작업을 계속하도록 합니다. 그렇지 않은 경우 다음 단계를 진행하여 자세히 드릴다운합니다.

2. 테이블 잠금 충돌 확인

테이블 잠금 에스컬레이션 충돌로 인해 클린업이 진행되지 않는지 확인합니다. 이 충돌은 행을 삭제하기 위해 측면 테이블의 잠금을 획득하는 클린 지속적으로 증가합니다.

잠금 충돌을 확인하려면 다음 T-SQL 코드를 실행합니다. 이 쿼리는 문제가 있는 테이블에 대한 레코드를 가져와 잠금 충돌을 나타내는 여러 항목이 있는지 확인합니다. 기간 동안 분산된 몇 가지 간헐적인 충돌은 진행 완화 단계를 수행할 자격이 없어야 합니다. 충돌은 되풀이되어야 합니다.

SELECT TOP 1000 *
FROM dbo.MSChange_tracking_history
WHERE table_name = '<user_table_name>'
ORDER BY start_time DESC;

기록 테이블에 값Cleanup error: Lock request time out period exceeded이 있는 열에 comments 여러 항목이 있는 경우 잠금 충돌 또는 잠금 시간 제한으로 인해 여러 클린업 시도가 실패했음을 명확하게 알 수 있습니다. 다음 해결 방법을 고려하세요.

문제가 있는 테이블에서 변경 내용 추적을 사용하지 않음 설정하고 사용 설정합니다. 이렇게 하면 테이블이 제거될 기본 추적 메타데이터가 전부 제거됩니다. 테이블의 데이터는 손상되지 않고 유지됩니다. 이것이 가장 빠른 해결책입니다.
이전 옵션을 사용할 수 없는 경우 다음과 같이 추적 플래그 8284를 사용하도록 설정하여 테이블에서 수동 클린 실행을 계속합니다.
```
DBCC TRACEON (8284, -1);
GO
EXEC [sys].[sp_flush_CT_internal_table_on_demand] @TableToClean = '<table_name>';
```

3. 다른 원인 확인

클린 지연의 또 다른 가능한 원인은 delete 문의 느림입니다. 확인하려면 값을 hardened_cleanup_version검사. 이 값은 고려 중인 데이터베이스에 대한 전용 DAC(관리자 연결)를 통해 검색할 수 있습니다.

다음 쿼리를 실행하여 강화된 클린업 버전을 찾습니다.

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1004;

다음 쿼리를 실행하여 클린up 버전을 찾습니다.

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1003;

값이 cleanup_version 같으면 hardened_cleanup_version 이 섹션을 건너뛰고 다음 섹션으로 진행합니다.

두 값이 서로 다르면 하나 이상의 사이드 테이블에 오류가 발생했음을 의미합니다. 가장 빠른 완화 방법은 문제가 있는 테이블에서 변경 내용 추적을 사용하지 않도록 설정하고 사용하도록 설정하는 것입니다. 이렇게 하면 테이블이 제거될 기본 추적 메타데이터가 전부 제거됩니다. 테이블의 데이터는 그대로 다시 기본.

이전 옵션을 사용할 수 없는 경우 테이블에서 수동 클린 실행합니다.

syscommittab 문제 해결

이 섹션에서는 시스템 테이블이 syscommittab 많은 스토리지 공간을 사용하거나 부실 행의 큰 백로그가 있는 경우 시스템 테이블의 문제를 디버그하고 완화하는 단계를 설명합니다.

시스템 테이블 클린up은 syscommittab 사이드 테이블 클린up에 따라 달라집니다. 모든 사이드 테이블이 클린 syscommittab 후에만 제거할 수 있습니다. 자동 클린업의 모든 단계가 실행되지만 진행률 섹션이 수행되지 않는지 확인합니다.

클린up을 syscommittab 명시적으로 호출하려면 sys.sp_flush_commit_table_on_demand 저장 프로시저를 사용합니다.

참고 항목

저장 프로시저는 sys.sp_flush_commit_table_on_demand 행의 큰 백로그를 삭제하는 경우 시간이 걸릴 수 있습니다.

sys.sp_flush_commit_table_on_demand 문서의 예제 섹션에 표시된 것처럼 이 저장 프로시저는 삭제된 행의 safe_cleanup_version()값과 행 수를 반환합니다. 반환된 값이 표시되고 0스냅샷 격리가 설정되어 있으면 클린up에서 syscommittab아무것도 삭제하지 않을 수 있습니다.

보존 기간이 1일보다 크면 추적 플래그 8239를 전역적으로 사용하도록 설정한 후 저장 프로시저를 다시 실행 sys.sp_flush_commit_table_on_demand 해도 안전합니다. 스냅샷 격리가 해제된 경우 이 추적 플래그를 사용하는 것은 항상 안전하지만 경우에 따라 필요하지 않을 수 있습니다.

클린 동안 높은 CPU 사용률

이 섹션에 설명된 문제는 이전 버전의 SQL Server에서 확인할 수 있습니다. 데이터베이스에 변경 추적 테이블이 많은 경우 자동 클린up 또는 수동 클린 업데이트로 인해 CPU 사용률이 높습니다. 이전 섹션에서 간략하게 멘션 기록 테이블로 인해 이 문제가 발생할 수도 있습니다.

다음 T-SQL 코드를 사용하여 기록 테이블의 행 수를 검사.

SELECT COUNT(*) from dbo.MSChange_tracking_history;

행 수가 충분히 큰 경우 없는 경우 다음 인덱스를 추가해 보세요. 다음 T-SQL 코드를 사용하여 인덱스 추가:

IF NOT EXISTS (
    SELECT *
    FROM sys.indexes
    WHERE name = 'IX_MSchange_tracking_history_start_time'
        AND object_id = OBJECT_ID('dbo.MSchange_tracking_history')
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_MSchange_tracking_history_start_time
    ON dbo.MSchange_tracking_history (start_time)
END

30분보다 더 자주 클린 실행

특정 테이블의 변경 속도가 높을 수 있으며 자동 클린 작업에서 30분 간격 내에 측면 테이블을 syscommittab 클린 수 없습니다. 이 경우 빈도가 증가된 수동 클린up 작업을 실행하여 프로세스를 용이하게 할 수 있습니다.

SQL Server 및 Azure SQL Managed Instance 의 경우 기본 30분보다 짧은 내부 작업으로 백그라운드 작업을sp_flush_CT_internal_table_on_demand 만듭니다. Azure SQL Database의 경우 Azure Logic Apps 를 사용하여 이러한 작업을 예약할 수 있습니다.

다음 T-SQL 코드를 사용하여 변경 내용 추적을 위해 사이드 테이블을 클린 작업을 만들 수 있습니다.

-- Loop to invoke manual cleanup procedure for cleaning up change tracking tables in a database
-- Fetch the tables enabled for change tracking
SELECT IDENTITY(INT, 1, 1) AS TableID,
    (SCHEMA_NAME(tbl.Schema_ID) + '.' + OBJECT_NAME(ctt.object_id)) AS TableName
INTO #CT_Tables
FROM sys.change_tracking_tables ctt
INNER JOIN sys.tables tbl
    ON tbl.object_id = ctt.object_id;

-- Set up the variables
DECLARE @start INT = 1,
    @end INT = (
        SELECT COUNT(*)
        FROM #CT_Tables
        ),
    @tablename VARCHAR(255);

WHILE (@start <= @end)
BEGIN
    -- Fetch the table to be cleaned up
    SELECT @tablename = TableName
    FROM #CT_Tables
    WHERE TableID = @start

    -- Execute the manual cleanup stored procedure
    EXEC sp_flush_CT_internal_table_on_demand @tablename

    -- Increment the counter
    SET @start = @start + 1;
END

DROP TABLE #CT_Tables;

Share via

변경 내용 추적 자동 클린업 문제 해결

증상

디버깅 및 완화

자동 클린업 상태

1. 정리가 꺼져 있습니다.

2. 정리가 켜져 있지만 실행되지 않음

자동 클린 실행하지만 진행되지 않음

1. 자동 클린업 백로그 평가

2. 테이블 잠금 충돌 확인

3. 다른 원인 확인

syscommittab 문제 해결

클린 동안 높은 CPU 사용률

30분보다 더 자주 클린 실행

피드백

피드백

추가 리소스

Share via

변경 내용 추적 자동 클린업 문제 해결

증상

디버깅 및 완화

자동 클린업 상태

1. 정리가 꺼져 있습니다.

2. 정리가 켜져 있지만 실행되지 않음

자동 클린 실행하지만 진행되지 않음

1. 자동 클린업 백로그 평가

2. 테이블 잠금 충돌 확인

3. 다른 원인 확인

syscommittab 문제 해결

클린 동안 높은 CPU 사용률

30분보다 더 자주 클린 실행

관련 콘텐츠

피드백

피드백

추가 리소스