변경 데이터 캡처 관리 및 모니터링(SQL Server)

이 항목에서는 변경 데이터 캡처를 관리하고 모니터링하는 방법을 설명합니다.

캡처 작업

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

캡처 작업 매개 변수

캡처 작업 동작을 이해하려면 sp_cdc_scan에서 구성 가능한 매개 변수가 어떻게 사용되는지 이해해야 합니다.

maxtrans 매개 변수

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

maxscans 매개 변수

maxscans 매개 변수는 로그를 반환(연속 = 0)하거나 대기(연속 = 1)를 실행하기 전에 로그를 드레이닝하려고 시도하는 최대 검사 주기 수를 지정합니다.

연속 매개 변수

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

원샷 모드

원샷 모드에서 캡처 작업은 sp_cdc_scan가 로그를 비우고 반환될 수 있도록 최대 maxtrans 횟수의 검사를 수행하도록 요청합니다. 로그에 있는 maxtrans 외에 모든 트랜잭션은 이후 검사에서 처리됩니다.

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

한 샷 모드에서 실행하는 경우 다음 계산을 사용하여 초당 트랜잭션으로 표현된 캡처 작업의 예상 처리량에 대한 상한을 계산할 수 있습니다.

(maxtrans * maxscans) / number of seconds between scans

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

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

연속 모드 및 폴링 간격

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

비고

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

캡처 작업 사용자 지정

캡처 작업에서 새 검색을 즉시 시작할지, 아니면 고정된 폴링 간격을 사용하지 않고 새 검색을 시작하기 전에 대기 시간을 사용할지를 결정하는 추가 논리를 적용할 수 있습니다. 하루 중 시간만을 기준으로 선택할 수 있으며, 특히 활동이 가장 많은 시간대에는 대기 시간을 매우 길게 설정하고, 주간 업무를 마무리하고 야간 작업을 준비해야 할 때는 폴링 간격을 0으로 설정할 수도 있습니다. 또한 캡처 프로세스 진행률을 모니터링하여 한밤중에 커밋된 모든 트랜잭션이 검사되어 변경 테이블에 입금된 시기를 확인할 수 있습니다. 이렇게 하면 캡처 작업이 종료된 후 예약된 일일 재시작 시간에 다시 시작되도록 할 수 있습니다. 전달된 작업 단계에서 sp_cdc_scan 호출을 사용자 정의 래퍼의 sp_cdc_scan 호출로 변경하면 추가 작업 없이 고도로 사용자 지정된 동작을 얻을 수 있습니다.

정리 작업

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

정리 작업의 구조

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

정리 작업은 매개 변수 없는 저장 프로시저 sp_MScdc_cleanup_job을 실행하여 시작됩니다. 이 저장 프로시저는 msdb.dbo.cdc_jobs에서 정리 작업에 대해 구성된 보존 및 임계값을 추출하는 것으로 시작합니다. 보존 값은 변경 테이블에 대한 새로운 하위 워터마크를 계산하는 데 사용됩니다. 지정한 시간(분)을 테이블의 최대 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을 결정할 수 있습니다. 이 중 일부만 엄격하게 시간을 기반으로 합니다. 예를 들어, 클라이언트에 대한 지식은 변경 테이블에 액세스해야 하는 다운스트림 프로세스를 실행할 수 없는 경우 안전장치를 제공하는 데 사용될 수 있습니다. 또한 기본 전략은 모든 데이터베이스의 변경 테이블을 정리하는 데 동일한 lsn을(를) 적용하지만, 기본 정리 절차는 캡처 인스턴스 수준에서 정리하도록 호출할 수도 있습니다.

변경 데이터 캡처 프로세스 모니터링

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

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

sys.dm_cdc_log_scan_sessions 모든 행은 로그 검사 세션을 나타냅니다(ID가 0인 행 제외). 로그 스캔 세션은 sp_cdc_scan을 한 번 실행한 것과 같습니다. 세션 중에 스캔은 변경 내용을 반환하거나 빈 결과를 반환할 수 있습니다. 결과 집합이 비어 있으면 sys.dm_cdc_log_scan_sessions empty_scan_count 열이 1로 설정됩니다. 캡처 작업이 계속 실행 중인 경우와 같이 빈 결과 집합이 연속적으로 있는 경우, 마지막 기존 행의 empty_scan_count가 증가합니다. 예를 들어 sys.dm_cdc_log_scan_sessions 변경 내용을 반환한 검사에 대해 10개의 행이 이미 포함되어 있고 행에 5개의 빈 결과가 있는 경우 보기에는 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_log_scan_sessions 보기 및 sys.dm_cdc_errors 보기의 스냅샷을 만드는 것이 유용합니다. 다음 절차에서는 sys.dm_cdc_log_scan_sessions 관리 뷰에서 샘플 데이터를 수집하기 위한 데이터 수집기를 설정합니다.

데이터 수집 구성

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

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

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

SQL Server Management Studio에서 관리를 확장한 다음 데이터 컬렉션을 확장합니다. CDC Performance Data Collector를 마우스 오른쪽 단추로 클릭한 다음 데이터 수집 집합 시작을 클릭합니다.
1단계에서 구성한 데이터 웨어하우스에서 custom_snapshots.cdc_log_scan_data 테이블을 찾습니다. 이 테이블은 로그 검색 세션의 데이터 스냅샷 기록을 제공합니다. 이 데이터를 사용하여 시간 경과에 따른 대기 시간, 처리량 및 기타 성능 측정값을 분석할 수 있습니다.

또한 참조하십시오

데이터 변경 내용 추적(SQL Server)
변경 데이터 캡처 개요(SQL Server)
변경 데이터 캡처 사용 및 사용 안 함(SQL Server)
변경 데이터 다루기(SQL Server)

Last updated on 2017-06-13