변경 데이터 캡처 관리 및 모니터링

  • 아티클

적용 대상:SQL ServerAzure SQL Managed Instance

이 항목에서는 SQL Server 및 Azure SQL Managed Instance에 대한 변경 데이터 캡처를 관리하고 모니터링하는 방법을 설명합니다.

다른 작업 메커니즘을 사용하는 Azure SQL Database의 경우 Azure SQL Database를 사용한 CDC를 참조하세요.

캡처 작업

캡처 작업은 매개 변수가 없는 저장 프로시저인 sp_MScdc_capture_job을 실행하여 시작됩니다. 이 저장 프로시저는 , pollingintervalmaxscanscontinuous및 캡처 작업에 msdb.dbo.cdc_jobs대해 maxtrans구성된 값을 추출하여 시작합니다. 이러한 구성된 값은 저장 프로시저 sp_cdc_scan에 매개 변수로 전달됩니다. 로그 검사를 수행하기 위해 호출 sp_replcmds 하는 데 사용됩니다.

캡처 작업 매개 변수

캡처 작업 동작을 이해하려면 구성 가능한 매개 변수 sp_cdc_scan가 사용되는 방법을 이해해야 합니다.

maxtrans 매개 변수

이 매개 변수는 maxtrans 로그의 단일 검색 주기에서 처리할 수 있는 최대 트랜잭션 수를 지정합니다. 검사 중에 처리할 트랜잭션 수가 이 제한에 도달하면 현재 검사에 추가 트랜잭션이 포함되지 않습니다. 검색 주기가 완료된 후에는 처리된 트랜잭션의 수가 항상 maxtrans보다 작거나 같습니다.

maxscans 매개 변수

maxscans 매개 변수는 반환(continuous = 0) 또는 waitfor 실행(continuous = 1) 전에 로그를 비우기 위해 시도되는 검색 주기의 최대 수를 지정합니다.

continuous 매개 변수

매개 변수는 continuous 로그를 드레이닝하거나 최대 검사 주기(원샷 모드)를 실행한 후 제어를 포기할지 여부를 sp_cdc_scan 제어합니다. 또한 명시적으로 중지(연속 모드)될 때까지 계속 실행되는지 여부를 sp_cdc_scan 제어합니다.

원샷 모드

원샷 모드에서 캡처 작업은 로그를 드레이닝하고 반환하려고 검사까지 maxtrans 수행하도록 요청 sp_cdc_scan 합니다. 로그에 있는 트랜잭션 외 maxtrans 에 모든 트랜잭션은 이후 검사에서 처리됩니다.

원샷 모드는 제어된 테스트에서 사용되며, 처리할 트랜잭션 볼륨이 알려져 있으며 작업이 완료되면 작업이 자동으로 종료된다는 장점이 있습니다. 프로덕션 사용에는 원샷 모드를 사용하지 않는 것이 좋습니다. 검사 주기가 실행되는 빈도를 관리하기 위해 작업 일정에 의존하기 때문입니다.

단발 모드로 실행될 때는 다음과 같은 공식을 사용하여 초당 트랜잭션의 단위로 캡처 작업의 예상 처리량에 대한 상한 값을 계산할 수 있습니다.

(maxtrans * maxscans) / number of seconds between scans

로그를 검색하고 변경 테이블을 채우는 데 필요한 시간이 0과 크게 다르지 않더라도 단일 검사에 허용되는 최대 트랜잭션을 최대 허용된 트랜잭션과 로그 처리를 구분하는 시간(초)을 곱하여 작업의 평균 처리량이 얻은 값을 초과할 수 없습니다.

원샷 모드를 사용하여 로그 스캔을 조절하는 경우 로그 처리 사이의 시간(초)은 작업 일정에 따라 관리되어야 합니다. 이러한 종류의 동작이 필요한 경우 연속 모드에서 캡처 작업을 실행하는 것이 로그 검사 일정을 다시 조정하는 더 좋은 방법입니다.

연속 모드 및 폴링 간격

연속 모드에서 캡처 작업은 지속적으로 실행되도록 sp_cdc_scan 요청합니다. 이렇게 하면 저장 프로시저가 로그 처리 사이의 시간(폴링 간격)에 대한 maxtransmaxscans 값뿐만 아니라 값도 제공하여 자체 대기 루프를 관리할 수 있습니다. 연속 모드에서 캡처 작업은 활성 상태로 다시 기본 로그 검색 간에 실행됩니다WAITFOR.

참고 항목

폴링 간격의 값이 0보다 크면 되풀이 원샷 작업의 처리량에 대한 동일한 상한이 연속 모드의 작업 작업에도 적용됩니다. 즉, (maxtrans * maxscans) 0이 아닌 폴링 간격으로 나뉘면 캡처 작업에서 처리할 수 있는 평균 트랜잭션 수에 상한이 적용됩니다.

캡처 작업 사용자 지정

캡처 작업에서 새 검색을 즉시 시작할지, 아니면 고정된 폴링 간격을 사용하지 않고 새 검색을 시작하기 전에 대기 시간을 사용할지를 결정하는 추가 논리를 적용할 수 있습니다. 이러한 옵션을 사용하면 단순히 하루 일과를 기준으로 업무량이 많을 때는 대기 시간을 매우 길게 적용하고, 주간 업무를 완료하고 야간 업무를 준비해야 하는 마감 시간에는 폴링 간격을 0으로 설정할 수도 있습니다. 캡처 프로세스 진행률을 모니터링하여 자정까지 커밋된 모든 트랜잭션을 검사하고 변경 테이블에 입금한 시점을 확인할 수도 있습니다. 이렇게 하면 캡처 작업이 종료된 후 예약된 일일 재시작 시간에 다시 시작되도록 할 수 있습니다. 동작을 사용자 지정하려면 호출 sp_cdc_scan 하는 작업 단계를 사용자 쓰기 래퍼에 대한 sp_cdc_scan호출로 바꿀 수 있습니다.

정리 작업

이 섹션에서는 변경 데이터 캡처 클린업 작업의 작동 방식에 대한 정보를 제공합니다.

클린업 작업의 구조

변경 데이터 캡처는 보존 기반 클린 업 전략을 사용하여 변경 테이블 크기를 관리합니다. SQL Server 및 Azure SQL Managed Instance에서 클린up 메커니즘은 첫 번째 데이터베이스 테이블을 사용할 때 생성되는 SQL Server 에이전트 Transact-SQL 작업으로 구성됩니다. 단일 클린업 작업은 모든 데이터베이스 변경 테이블에 대한 클린up을 처리하고 정의된 모든 캡처 인스턴스에 동일한 보존 값을 적용합니다.

클린up 작업은 매개 변수가 없는 저장 프로시저sp_MScdc_cleanup_job를 실행하여 시작됩니다. 이 저장 프로시저는 클린up 작업에 msdb.dbo.cdc_jobs대해 구성된 보존 및 임계값을 추출하여 시작합니다. 보존 값은 변경 테이블에 대한 새 하위 워터마크를 계산하는 데 사용됩니다. 저장 프로시저는 cdc.lsn_time_mapping 테이블의 최대 tran_end_time 값에서 지정된 시간 값(분)을 빼 datetime 값으로 표현된 새로운 하위 워터마크를 얻습니다. 그런 다음 CDC.lsn_time_mapping 테이블을 사용하여 이 datetime 값을 해당 lsn 값으로 변환합니다. 테이블 lsn 의 여러 항목에서 동일한 커밋 시간을 공유하는 경우 가장 lsn 작은 항목에 해당하는 커밋 시간이 새 하위 워터마크로 선택됩니다. 이 lsn 값은 데이터베이스 변경 테이블에서 변경 테이블 항목을 제거하기 위해 전달 sp_cdc_cleanup_change_tables 됩니다.

참고 항목

최근 트랜잭션의 커밋 시간을 새 하위 워터마크를 계산하기 위한 기반으로 사용할 경우 지정된 시간 동안 변경 테이블에서 변경 내용을 다시 기본 수 있다는 장점이 있습니다. 캡처 프로세스가 뒤에서 실행되는 경우에도 발생합니다. 현재 하위 워터마크와 커밋 시간이 동일한 모든 항목은 실제 하위 워터마크에 대한 공유 커밋 시간이 있는 가장 lsn 작은 항목을 선택하여 변경 테이블 내에서 계속 표시됩니다.

클린 수행되면 모든 캡처 인스턴스에 대한 하위 워터마크가 처음에 단일 트랜잭션에서 업데이트됩니다. 그런 다음 변경 테이블과 cdc.lsn_time_mapping 테이블에서 오래된 항목을 제거하려고 시도합니다. 구성 가능한 임계값은 단일 문에서 삭제되는 항목 수를 제한합니다. 개별 테이블에서 삭제를 수행하지 못하면 다시 기본 테이블에서 작업이 시도되지 않습니다.

정리 작업 사용자 지정

클린업 작업의 경우 사용자 지정 가능성은 해제할 변경 테이블 항목을 결정하는 데 사용되는 전략에 있습니다카드. 배달된 정리 작업에서 지원되는 유일한 전략은 시간 기반 전략입니다. 이 경우 마지막으로 처리된 트랜잭션의 커밋 시간에서 허용되는 보존 기간을 빼서 새 하위 워터마크가 계산됩니다. 기본 클린업 프로시저는 시간이 아니라 기반으로 lsn 하기 때문에 변경 테이블에 유지할 가장 lsn 작은 전략을 결정하는 데 사용할 수 있습니다. 이들 중 일부만 엄격하게 시간 기반입니다. 예를 들어 클라이언트에 대한 지식은 변경 테이블에 대한 액세스가 필요한 다운스트림 프로세스를 실행할 수 없는 경우 장애 조치(failsafe)를 제공하는 데 사용할 수 있습니다. 또한 기본 전략은 모든 데이터베이스의 변경 테이블을 클린 동일하게 적용되지 lsn 만 기본 클린업 프로시저를 호출하여 캡처 인스턴스 수준에서 클린 수도 있습니다.

프로세스 모니터링

변경 데이터 캡처 프로세스 모니터링을 통해 변경 내용이 올바르게 기록되고 있고 변경 테이블에 대한 대기 시간이 적절한지 확인할 수 있습니다. 모니터링은 발생할 수 있는 오류를 식별하는 데 도움이 될 수도 있습니다. SQL Server에는 변경 데이터 캡처를 모니터링하는 데 도움이 되는 두 가지 동적 관리 뷰인 sys.dm_cdc_log_scan_sessions 및 sys.dm_cdc_errors 포함되어 있습니다.

빈 결과 집합을 사용하여 세션 식별

모든 행은 sys.dm_cdc_log_scan_sessions 로그 검사 세션을 나타냅니다(ID가 0인 행 제외). 로그 검사 세션은 한 번의 sp_cdc_scan 실행과 동일합니다. 세션 중에 검사는 변경 내용을 반환하거나 빈 결과를 반환할 수 있습니다. 결과 집합이 비어 있으면 empty_scan_count 열 sys.dm_cdc_log_scan_sessions 이 1로 설정됩니다. 캡처 작업이 지속적으로 실행되는 경우와 같이 빈 결과 집합이 연속적으로 있는 경우 마지막 기존 행의 empty_scan_count 증가합니다. 예를 들어 변경 내용을 반환한 검사에 대한 행이 이미 10개이고 행에 5개의 빈 결과가 있는 경우 sys.dm_cdc_log_scan_sessions 보기에는 11개의 행이 포함됩니다. 마지막 행의 값은 empty_scan_count 열에 5입니다. 빈 검색이 있는 세션을 확인하려면 다음 쿼리를 실행합니다.

SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0

대기 시간 확인

관리 뷰에는 sys.dm_cdc_log_scan_sessions 각 캡처 세션의 대기 시간을 기록하는 열이 포함됩니다. 대기 시간은 원본 테이블에서 커밋되는 트랜잭션과 변경 테이블에서 커밋되는 마지막 캡처된 트랜잭션 사이의 경과된 시간으로 정의됩니다. 대기 시간 열은 활성 세션에 대해서만 채워집니다. empty_scan_count 열에서 값이 0보다 큰 세션의 경우 대기 시간 열은 0으로 설정됩니다. 다음 쿼리는 가장 최근의 세션에 대한 평균 대기 시간을 반환합니다.

SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0

대기 시간 데이터를 사용하여 캡처 프로세스가 트랜잭션을 처리하는 속도 또는 속도를 확인할 수 있습니다. 이 데이터는 캡처 프로세스가 계속해서 실행되는 경우에 가장 유용합니다. 캡처 프로세스가 일정에 따라 실행되는 경우 원본 테이블에서 커밋되는 트랜잭션과 예약된 시간에 실행되는 캡처 프로세스 간의 지연으로 인해 대기 시간이 높아질 수 있습니다.

캡처 프로세스 효율성의 또 다른 중요한 측정값은 처리량입니다. 이는 각 세션 동안 처리되는 초당 평균 명령 수입니다. 세션의 처리량을 확인하려면 command_count 열에 있는 값을 duration 열에 있는 값으로 나눕니다. 다음 쿼리는 가장 최근 세션의 평균 처리량을 반환합니다.

SELECT command_count/duration AS [Throughput] FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0

데이터 수집기를 사용하여 샘플링 데이터 수집

SQL Server 데이터 수집기를 사용하면 모든 테이블 또는 동적 관리 뷰에서 스냅샷 데이터를 수집하고 성능 데이터 웨어하우스를 빌드할 수 있습니다. 데이터베이스에서 변경 데이터 캡처를 사용하는 경우 나중에 분석하기 위해 보기와 sys.dm_cdc_errors sys.dm_cdc_log_scan_sessions 보기를 정기적으로 스냅샷 사용하는 것이 유용합니다. 다음 절차에서는 관리 뷰에서 샘플 데이터를 수집하기 위한 데이터 수집기를 sys.dm_cdc_log_scan_sessions 설정합니다.

데이터 수집 구성

  1. 데이터 수집기를 사용하도록 설정하고 관리 데이터 웨어하우스를 구성합니다. 자세한 내용은 데이터 수집 관리를 참조 하세요.

  2. 다음 코드를 실행하여 변경 데이터 캡처를 위한 사용자 지정 수집기를 만듭니다.

    USE msdb;  

DECLARE @schedule_uid uniqueidentifier;  

-- Collect and upload data every 5 minutes  
SELECT @schedule_uid = (  
SELECT schedule_uid from sysschedules_localserver_view
WHERE name = N'CollectorSchedule_Every_5min')  

DECLARE @collection_set_id int;  

EXEC dbo.sp_syscollector_create_collection_set  
@name = N' CDC Performance Data Collector',  
@schedule_uid = @schedule_uid,
@collection_mode = 0,
@days_until_expiration = 30,
@description = N'This collection set collects CDC metadata',  
@collection_set_id = @collection_set_id output;  

-- Create a collection item using statistics from
-- the change data capture dynamic management view.  
DECLARE @parameters xml;  
DECLARE @collection_item_id int;  

SELECT @parameters = CONVERT(xml,
    N'<TSQLQueryCollector>  
        <Query>  
          <Value>SELECT * FROM sys.dm_cdc_log_scan_sessions</Value>  
          <OutputTable>cdc_log_scan_data</OutputTable>  
        </Query>  
      </TSQLQueryCollector>');  

EXEC dbo.sp_syscollector_create_collection_item  
@collection_set_id = @collection_set_id,  
@collector_type_uid = N'302E93D1-3424-4BE7-AA8E-84813ECF2419',  
@name = ' CDC Performance Data Collector',  
@frequency = 5,
@parameters = @parameters,  
@collection_item_id = @collection_item_id output;

GO

  3. SQL Server Management Studio에서 관리를 확장한 다음 데이터 수집을 확장합니다. CDC Performance Data Collector를 마우스 오른쪽 단추로 클릭한 다음 데이터 수집 집합 시작을 클릭합니다.

  4. 1단계에서 구성한 데이터 웨어하우스에서 custom_스냅샷s.cdc_log_scan_data 테이블을 찾습니다. 이 테이블은 로그 검색 세션의 데이터 스냅샷 기록을 제공합니다. 이 데이터를 사용하여 시간에 따른 대기 시간, 처리량 및 기타 성능 측정값을 분석할 수 있습니다.

스크립트 업그레이드 모드

인스턴스에 누적 업데이트 또는 서비스 팩을 적용하면 다시 시작할 때 인스턴스가 스크립트 업그레이드 모드로 전환할 수 있습니다. 이 모드에서 SQL Server는 내부 CDC 테이블을 분석하고 업그레이드하는 단계를 실행하여 캡처 테이블의 인덱스와 같은 개체를 다시 만들 수 있습니다. 관련된 데이터의 양에 따라 이 단계는 시간이 다소 소요되거나 사용 가능한 CDC 데이터베이스에 대한 높은 트랜색션 로그 사용을 유발할 수 있습니다.

관련 콘텐츠