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

적용 대상: SQL Server Azure SQL Managed Instance

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

다른 작업 메커니즘을 사용하는 Azure SQL 데이터베이스의 경우 CDC와 Azure SQL 데이터베이스를 참조하세요.

캡처 작업

캡처 작업은 매개 변수가 없는 저장 프로시저인 sp_MScdc_capture_job을 실행하여 시작됩니다. 이 저장 프로시저는 maxtrans에서 캡처 작업에 대한 maxscans, continuous, pollinginterval 및 msdb.dbo.cdc_jobs에 대한 구성된 값을 추출하는 것으로 시작합니다. 그런 다음 이러한 구성된 값이 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이(가) 명시적으로 중지될 때까지 계속 실행할지 여부(연속 모드)도 제어합니다.

단발 모드

단발 모드에서 캡처 작업은 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 및 Azure SQL Managed Instance에서 정리 메커니즘은 첫 번째 데이터베이스 테이블을 사용하도록 설정할 때 생성되는 SQL Server 에이전트 Transact-SQL 작업으로 구성됩니다. 단일 정리 작업은 모든 데이터베이스 변경 테이블에 대한 정리를 처리하고 정의된 모든 캡처 인스턴스에 동일한 보존 값을 적용합니다.

정리 작업은 매개 변수 없는 저장 프로시저 sp_MScdc_cleanup_job을 실행하여 시작됩니다. 이 저장 프로시저는 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을 결정할 수 있습니다. 이 중 일부만 엄격하게 시간을 기반으로 합니다. 예를 들어, 클라이언트에 대한 지식은 변경 테이블에 액세스해야 하는 다운스트림 프로세스를 실행할 수 없는 경우 안전장치를 제공하는 데 사용될 수 있습니다. 또한 기본 전략은 모든 데이터베이스의 변경 테이블을 정리하는 데 동일한 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 관리 뷰에서 샘플 데이터를 수집하기 위한 데이터 수집기를 설정합니다.

데이터 수집 구성

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 성능 데이터 수집기를 마우스 오른쪽 단추로 클릭한 다음 데이터 컬렉션 세트 시작을 클릭합니다.

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

스크립트 업그레이드 모드

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

관련 콘텐츠

• Azure SQL 데이터베이스를 사용하는 CDC
• 데이터 변경 내용 추적(SQL Server)
• 변경 데이터 캡처 정보(SQL Server)
• 변경 데이터 캡처 사용 설정 및 사용 중지(SQL Server)
• 변경 데이터 작업(SQL Server)