가속 데이터베이스 복구 문제 해결

아티클
08/21/2023

적용 대상: SQL Server 2019 (15.x) Azure SQL Database Azure SQL Managed Instance

이 문서는 관리자가 SQL Server 2019(15.x) 이상, Azure SQL Managed Instance 및 Azure SQL Database에서 ADR(가속 데이터베이스 복구) 문제를 진단하는 데 도움이 됩니다.

PVS(영구 버전 저장소) 검사

sys.dm_tran_persistent_version_store_stats DMV를 활용하여 ADR(가속 데이터베이스 복구) PVS의 크기가 예상보다 커지는지 확인한 다음, PVS(영구 버전 저장소) 정리를 방해하는 요인을 확인합니다.

다음 샘플 스크립트에 포함된 열 sys.dm_tran_persistent_version_store_stats.pvs_off_row_page_skipped_oldest_aborted_xdesid은 SQL Server 2022(16.x)에 추가되었으며 가장 오래된 중단된 트랜잭션으로 인해 회수를 위해 건너뛴 페이지 수를 포함합니다. 버전 클리너가 느리거나 무효화되면 이 값은 중단된 트랜잭션에 대해 유지해야 하는 페이지 수를 반영합니다.

샘플 쿼리는 정리 프로세스에 대한 모든 정보를 표시하고 현재 PVS 크기, 가장 오래된 중단된 트랜잭션 및 기타 세부 정보를 보여 줍니다.

SELECT
 db_name(pvss.database_id) AS DBName,
 pvss.persistent_version_store_size_kb / 1024. / 1024 AS persistent_version_store_size_gb,
 100 * pvss.persistent_version_store_size_kb / df.total_db_size_kb AS pvs_pct_of_database_size,
 df.total_db_size_kb/1024./1024 AS total_db_size_gb,
 pvss.online_index_version_store_size_kb / 1024. / 1024 AS online_index_version_store_size_gb,
 pvss.current_aborted_transaction_count,
 pvss.aborted_version_cleaner_start_time,
 pvss.aborted_version_cleaner_end_time,
 dt.database_transaction_begin_time AS oldest_transaction_begin_time,
 asdt.session_id AS active_transaction_session_id,
 asdt.elapsed_time_seconds AS active_transaction_elapsed_time_seconds,
 pvss.pvs_off_row_page_skipped_low_water_mark,
 pvss.pvs_off_row_page_skipped_min_useful_xts,
 pvss.pvs_off_row_page_skipped_oldest_aborted_xdesid -- SQL Server 2022 only
FROM sys.dm_tran_persistent_version_store_stats AS pvss
CROSS APPLY (SELECT SUM(size*8.) AS total_db_size_kb FROM sys.database_files WHERE [state] = 0 and [type] = 0 ) AS df 
LEFT JOIN sys.dm_tran_database_transactions AS dt
ON pvss.oldest_active_transaction_id = dt.transaction_id
   AND
   pvss.database_id = dt.database_id
LEFT JOIN sys.dm_tran_active_snapshot_database_transactions AS asdt
ON pvss.min_transaction_timestamp = asdt.transaction_sequence_num
   OR
   pvss.online_index_min_transaction_timestamp = asdt.transaction_sequence_num
WHERE pvss.database_id = DB_ID();

pvs_pct_of_database_size 크기를 확인하고 다른 애플리케이션 작업 기간 동안의 기준선과 비교하여 일반적인 크기와의 차이를 파악합니다. PVS는 기준선보다 훨씬 크거나 데이터베이스 크기의 50%에 가까운 경우 큰 것으로 간주됩니다. 큰 PVS에 대한 문제 해결 수단으로 다음 단계를 사용합니다.

ADR이 사용하도록 설정된 모든 데이터베이스의 활성 장기 실행 트랜잭션은 PVS 정리를 방지할 수 있습니다. 트랜잭션 ID를 기반으로 쿼리하여 이 트랜잭션이 오랫동안 활성 상태인지 여부를 검색 oldest_active_transaction_id 하고 sys.dm_tran_database_transactions 확인합니다. 기간 또는 로그 분량에 대한 임계값을 설정하는 변수를 선언하는 아래 샘플과 같은 쿼리를 사용하여 장기 실행 활성 트랜잭션이 있는지 확인합니다.

DECLARE @longTxThreshold int = 1800; --number of seconds to use as a duration threshold for long-running transactions
DECLARE @longTransactionLogBytes bigint = 2147483648; --number of bytes to use as a log amount threshold for long-running transactions

SELECT
    dbtr.database_id, 
    transess.session_id,  
    transess.transaction_id, 
    atr.name, 
    sess.login_time,  
    dbtr.database_transaction_log_bytes_used, 
    CASE
       WHEN getdate() >= dateadd(second, @longTxThreshold, tr.transaction_begin_time) then 'DurationThresholdExceeded' 
       WHEN dbtr.database_transaction_log_bytes_used >= @longTransactionLogBytes then 'LogThresholdExceeded' 
       ELSE 'unknown' END AS Reason 
  FROM
    sys.dm_tran_active_transactions AS tr  
    INNER JOIN sys.dm_tran_session_transactions AS transess on tr.transaction_id = transess.transaction_id  
    INNER JOIN sys.dm_exec_sessions AS sess on transess.session_id = sess.session_id 
    INNER JOIN sys.dm_tran_database_transactions AS dbtr on tr.transaction_id = dbtr.transaction_id 
    INNER JOIN sys.dm_tran_active_transactions AS atr on atr.transaction_id = transess.transaction_id 
WHERE transess.session_id <> @@spid AND 
    ( getdate() >= dateadd(second, @longTxThreshold, tr.transaction_begin_time) OR
      dbtr.database_transaction_log_bytes_used >= @longTransactionLogBytes );

식별된 세션은 허용되는 경우 종료하는 것이 좋습니다. 또한 애플리케이션을 검토하여 문제가 있는 활성 트랜잭션의 성격을 파악합니다.

장기 실행 쿼리 문제 해결에 대한 자세한 내용은 다음을 참조하세요.

장기 활성 스냅샷 검사로 인해 영구 버전 정리가 보류될 수 있습니다. RCSI(읽기 커밋된 스냅샷 격리) 또는 SNAPSHOT 격리 수준을 사용하는 문은 인스턴스 수준 타임스탬프를 받습니다. 스냅샷 검사는 타임스탬프를 사용하여 가속화된 데이터베이스 복구가 활성화된 PVS에서 RCSI 또는 SNAPSHOT 트랜잭션에 대한 행 표시 여부를 결정합니다. RCSI를 사용하는 모든 문에는 자체 타임스탬프가 있는 반면 SNAPSHOT 격리에는 트랜잭션 수준 타임스탬프가 있습니다. 이러한 인스턴스 수준 트랜잭션 타임스탬프는 단일 데이터베이스 트랜잭션에서도 사용됩니다. 트랜잭션이 데이터베이스 간 트랜잭션으로 승격될 수 있기 때문입니다. 따라서 스냅샷 검사는 ADR PVS에서 또는 ADR이 없는 경우 버전 저장소에서 레코드 정리를 tempdb 방지할 수 있습니다. 따라서 이 버전 추적으로 인해 SNAPSHOT 또는 RCSI를 사용하는 장기 실행 트랜잭션으로 인해 ADR PVS가 인스턴스의 데이터베이스 정리를 지연하여 ADR PVS의 크기가 증가할 수 있습니다.

이 문서의pvs_off_row_page_skipped_min_useful_xts 맨 위에 있는 원래 문제 해결 쿼리에서 값은 긴 스냅샷 검색으로 인해 회수를 위해 건너뛴 페이지 수를 표시합니다. pvs_off_row_page_skipped_min_useful_xts가 정상보다 큰 값을 표시하는 경우 PVS 정리를 방지하는 장기 스냅샷 검사가 있음을 의미합니다.

다음 샘플 쿼리를 사용하여 문제가 있는 세션을 결정할 수 있습니다.
```
SELECT 
    snap.transaction_id, snap.transaction_sequence_num, session.session_id, session.login_time, 
    GETUTCDATE() as [now], session.host_name, session.program_name, session.login_name, session.last_request_start_time
FROM sys.dm_tran_active_snapshot_database_transactions AS snap
INNER JOIN sys.dm_exec_sessions AS session ON snap.session_id = session.session_id  
ORDER BY snap.transaction_sequence_num asc;
```
PVS 정리 지연을 방지하려면 다음을 수행합니다.
1. 가능하면 PVS 정리를 지연하는 긴 활성 트랜잭션 세션을 종료하는 것이 좋습니다. ADR이 사용되는 모든 데이터베이스에서 장기 실행 트랜잭션은 ADR PVS 정리를 지연할 수 있습니다.
2. 장기 실행 쿼리를 조정하여 쿼리 기간 및 필요한 잠금을 줄입니다. 자세한 내용 및 지침 은 SQL Server 의 차단 이해 및 해결 또는 Azure SQL Database 차단 문제 이해 및 해결을 참조하세요.
3. 애플리케이션을 검토하여 문제가 있는 활성 스냅샷 검사의 특성을 확인합니다. ADR PVS 정리를 지연하는 장기 실행 쿼리의 경우 SNAPSHOT 또는 READ COMMITTED SNAPSHOT 대신 READ COMMITTED와 같은 다른 격리 수준을 고려합니다. 이 문제는 SNAPSHOT 격리 수준에서 더 자주 발생합니다.
4. 이 문제는 SQL Server, Azure SQL Managed Instance 및 Azure SQL Database의 탄력적 풀에서 발생할 수 있지만 단일 Azure SQL 데이터베이스에서는 발생할 수 없습니다. Azure SQL Database 탄력적 풀에서 READ COMMIT SNAPSHOT 또는 SNAPSHOT 격리 수준을 사용하여 장기 실행 쿼리가 있는 탄력적 풀에서 데이터베이스를 이동하는 것이 좋습니다.
주 또는 보조 복제본에서 장기 실행 트랜잭션으로 인해 PVS 크기가 증가하는 경우 장기 실행 쿼리를 조사하고 병목 상태를 해결합니다. sys.dm_tran_aborted_transactions DMV는 모든 중단된 트랜잭션을 표시합니다. 자세한 내용은 sys.dm_tran_aborted_transactions(Transact-SQL)를 참조하세요. nest_aborted 열은 트랜잭션이 커밋되었지만 PVS 정리 프로세스를 차단할 수 있는 중단된 부분(저장점 또는 중첩 트랜잭션)이 있음을 나타냅니다.
데이터베이스가 가용성 그룹의 일부인 경우 secondary_low_water_mark를 확인합니다. 이는 보고한 low_water_mark_for_ghostssys.dm_hadr_database_replica_states것과 동일합니다. 또한 PVS 정리를 방지하므로 복제본 중 하나가 이 값을 뒤에 두고 있는지 여부를 확인하는 쿼리 sys.dm_hadr_database_replica_states 입니다. 버전 정리는 읽기 가능한 보조 복제본에 대한 읽기 쿼리로 인해 보류됩니다. SQL Server 온-프레미스 및 Azure SQL DB는 모두 읽기 가능한 보조를 지원합니다. sys.dm_tran_persistent_version_store_stats DMV에서 pvs_off_row_page_skipped_low_water_mark는 보조 복제본 지연의 표시도 제공할 수 있습니다. 자세한 내용은 sys.dm_tran_persistent_version_store_stats를 참조하세요.

해결책은 스냅샷 검사 보류와 동일합니다. 보조 복제본으로 이동하여 긴 쿼리를 실행하는 세션을 찾고 허용되는 경우 세션을 종료하는 것이 좋습니다. 보조 복제본 보류는 ADR 버전 정리에 영향을 미칠 뿐만 아니라 고스트 레코드 정리를 방지할 수도 있습니다.
min_transaction_timestamp PVS 정리를 유지하는 이전 스냅샷 트랜잭션이 있는 세션을 찾기 위해 열을 transaction_sequence_num 확인sys.dm_tran_active_snapshot_database_transactions(또는 online_index_min_transaction_timestamp 온라인 PVS가 유지 중인 경우)합니다.
위의 항목이 적용되지 않으면 정리가 중단된 트랜잭션에 의해 유지됨을 의미합니다. 마지막으로 aborted_version_cleaner_last_start_timeaborted_version_cleaner_last_end_time 중단된 트랜잭션 정리가 완료되었는지 확인합니다. oldest_aborted_transaction_id 중단된 트랜잭션 정리가 완료된 후 더 높게 이동해야 합니다. 값이 oldest_aborted_transaction_id 훨씬 작 oldest_active_transaction_id고 current_abort_transaction_count 값이 더 큰 경우 PVS 정리를 방지하는 오래된 중단된 트랜잭션이 있습니다. 이 문제를 해결하려면
- 가능하면 워크로드를 중지하여 버전 클리너가 진행되도록 합니다.
- 워크로드를 최적화하여 개체 수준 잠금 사용량을 줄입니다.
- 애플리케이션을 검토하여 높은 트랜잭션 중단 비율 문제가 있는지 확인합니다. 교착 상태, 중복 키 및 기타 제약 조건 위반으로 인해 중단 비율이 높아질 수 있습니다.
- SQL Server 경우 PVS 크기 및 트랜잭션 중단 수를 모두 제어하는 비상 단계로서 ADR을 사용하지 않도록 설정합니다. ADR 기능 사용 안 함을 참조하세요.
중단된 트랜잭션이 최근에 성공적으로 완료되지 않은 경우 VersionCleaner 문제를 보고하는 메시지에 대한 오류 로그를 확인합니다.
SQL Server 오류 로그에서 'PreallocatePVS' 항목을 모니터링합니다. 'PreallocatePVS' 항목이 있는 경우 백그라운드 작업을 위한 페이지를 미리 할당하는 ADR 기능을 늘려야 할 수 있습니다. ADR 백그라운드 스레드가 충분한 페이지를 미리 할당하고 포그라운드 PVS 할당 비율이 0에 가까울 때 성능이 향상될 수 있기 때문입니다. 이 용량을 늘리려면 sp_configure 'ADR Preallocation Factor' 값을 사용할 수 있습니다. 자세한 내용은 ADR 미리 할당 요소 서버 구성 옵션을 참조하세요.

수동으로 PVS 정리 프로세스 시작

공간을 회수하기 위해 PVS 정리 프로세스에 대한 미사용/복구 기간 없이 업데이트/삭제 트랜잭션 수가 많은 데이터베이스 환경(예: 대용량 OLTP)에는 ADR을 사용하지 않는 것이 좋습니다.

워크로드 간에 또는 유지 관리 기간 동안 PVS 정리 프로세스를 수동으로 활성화하려면 시스템 저장 프로시저 sys.sp_persistent_version_cleanup을 사용합니다.

EXEC sys.sp_persistent_version_cleanup [database_name];

예를 들면 다음과 같습니다.

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

정리 실패 캡처

SQL Server 2022(16.x)부터 SQL Server는 SQL Server 오류 로그에 ADR PVS 정리 동작을 기록합니다. 일반적으로 10분마다 새 로그 이벤트가 기록됩니다.

가속 데이터베이스 복구 문제 해결

PVS(영구 버전 저장소) 검사

수동으로 PVS 정리 프로세스 시작

정리 실패 캡처

참고 항목

다음 단계

피드백

피드백

추가 리소스