다음을 통해 공유

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

  • 아티클

적용 대상:Microsoft Fabric의 SQL Server 2019(15.x) 이상 버전 Azure SQL Database Azure SQL Managed InstanceSQL 데이터베이스

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

PVS 크기 검사

sys.dm_tran_persistent_version_store_stats DMV를 사용하여 PVS(영구 버전 저장소) 크기가 예상보다 큰지 확인합니다.

다음 샘플 쿼리는 현재 PVS 크기, 정리 프로세스 및 기타 세부 정보에 대한 정보를 보여 줍니다.

SELECT  DB_NAME(pvss.database_id) AS database_name,
        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
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의 크기를 확인합니다. 일반적인 PVS 크기와 애플리케이션 작업의 다른 기간 동안 표시된 기준선을 비교하여 어떤 차이가 있는지 확인하십시오. PVS는 기준선보다 훨씬 크거나 데이터베이스 크기의 50%에 가까운 경우 큰 것으로 간주됩니다. 다음 문제 해결 단계를 사용하여 PVS 크기가 큰 이유를 찾습니다.

PVS 크기가 예상보다 큰 경우 다음을 확인합니다.

장시간 실행 중인 활성 트랜잭션을 확인하기

활성 트랜잭션이 오래 지속되면 ADR이 설정된 데이터베이스에서 PVS 정리가 방해받을 수 있습니다. oldest_transaction_begin_time 열을 사용하여 가장 오래된 활성 트랜잭션의 시작 시간을 확인합니다. 장기 실행 트랜잭션에 대한 자세한 내용은 다음 샘플 쿼리를 사용합니다. 트랜잭션 기간 및 생성된 트랜잭션 로그 양에 대한 임계값을 설정할 수 있습니다.

DECLARE @LongTxThreshold int = 1800;  /* The number of seconds to use as a duration threshold for long-running transactions */
DECLARE @LongTransactionLogBytes bigint = 1073741824; /* The 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 GETDATE() >= DATEADD(second, @LongTxThreshold, tr.transaction_begin_time)
        OR
        dbtr.database_transaction_log_bytes_used >= @LongTransactionLogBytes;

세션이 식별되면 가능하다면 세션을 종료하십시오. 애플리케이션을 검토하여 문제가 있는 활성 트랜잭션의 특성을 확인하여 향후 문제를 방지합니다.

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

장기 실행 활성 스냅샷 검사 확인

장기간 실행되는 활성 스냅샷 검사는 ADR이 활성화된 데이터베이스에서 PVS 정리를 방해할 수 있습니다. RCSI(READ COMMITTED 스냅샷 격리) 또는 SNAPSHOT격리 수준을 사용하는 문은 인스턴스 수준 타임스탬프를 받을 있습니다. 스냅샷 스캔은 타임스탬프를 사용하여 RCSI 또는 SNAPSHOT 트랜잭션의 버전 행 가시성을 결정합니다. RCSI를 사용하는 모든 문에는 자체 타임스탬프가 있는 반면, SNAPSHOT 격리에는 트랜잭션 수준의 타임스탬프가 있습니다.

이러한 인스턴스 수준의 트랜잭션 타임스탬프는 단일 데이터베이스 트랜잭션에서도 사용됩니다. 이는 어느 트랜잭션이든 데이터베이스 간 트랜잭션을 발생시킬 가능성이 있기 때문입니다. 따라서 스냅샷 검사는 동일한 데이터베이스 엔진 인스턴스의 모든 데이터베이스에서 PVS 정리를 방지할 수 있습니다. 마찬가지로, ADR이 활성화되어 있지 않으면 스냅샷 검사가 tempdb버전 저장소의 정리를 방해할 수 있습니다. 따라서 SNAPSHOT 또는 RCSI를 사용하는 장기 실행 트랜잭션이 있는 경우 PVS의 크기가 커질 수 있습니다.

이 문서의 시작 부분에 있는 문제 해결 쿼리에서, pvs_off_row_page_skipped_min_useful_xts 열은 스냅샷 검색이 오래 걸려서 복구 단계를 건너뛴 페이지 수를 표시합니다. 이 열에 평소보다 큰 값이 표시되면 긴 스냅샷 검사로 인해 PVS 정리가 방지됩니다.

다음 샘플 쿼리를 사용하여 장기 실행 SNAPSHOT 또는 RCSI 트랜잭션이 포함된 세션을 찾습니다.

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 정리 지연을 방지하려면 다음을 수행합니다.

  • 가능하면 PVS 정리를 지연하는 긴 활성 트랜잭션 세션을 종료하는 것이 좋습니다.
  • 장기 실행 쿼리를 조정하여 쿼리 기간을 줄입니다.
  • 또한 애플리케이션을 검토하여 문제가 있는 활성 스냅샷 검사의 성격을 파악합니다. 장기 실행 쿼리가 PVS 정리를 지연하는 경우 SNAPSHOT 또는 RCSI 대신 READ COMMITTED와 같은 다른 격리 수준을 고려해 보세요. 이 문제는 SNAPSHOT 격리 수준에서 더 자주 발생합니다.
  • Azure SQL Database 탄력적 풀에서 SNAPSHOT 격리 또는 RCSI를 사용하는 장기 실행 트랜잭션이 있는 데이터베이스를 탄력적 풀 밖으로 이동하는 것을 고려하십시오.

보조 복제본에서 장기 실행 쿼리를 확인하다

데이터베이스에 보조 복제본이 있는 경우 보조 하위 워터마크가 진행 중인지 확인합니다.

주 복제본에서 다음 DMV를 실행하여, 보조 복제본의 PVS 정리를 방해할 수 있는 장기 실행 쿼리를 식별합니다.

sys.dm_tran_persistent_version_store_stats DMV에서는 pvs_off_row_page_skipped_low_water_mark 열이 보조 복제본에서 장기 실행 쿼리로 인해 발생하는 정리 지연을 나타낼 수도 있습니다.

보조 복제본에 연결하고 긴 쿼리를 실행 중인 세션을 찾은 후, 허용되는 경우 세션을 종료하는 것을 고려하십시오. 보조 복제본에서 장기 실행 쿼리는 PVS 정리를 지연시킬 뿐만 아니라 고스트 정리를 방지할 수 있습니다.

많은 수의 중단된 트랜잭션 확인

이전 시나리오가 워크로드에 적용되지 않는 경우, 많은 수의 중단된 트랜잭션으로 인해 정리가 지연될 수 있습니다. aborted_version_cleaner_last_start_time 및 aborted_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 문제를 보고하는 메시지를 확인합니다.
  • 정리가 완료된 후에도 PVS 크기가 예상대로 줄어들지 않으면 pvs_off_row_page_skipped_oldest_aborted_xdesid 열을 확인합니다. 큰 값은 중단된 트랜잭션의 행 버전에서 여전히 공간을 사용하고 있음을 나타냅니다.

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

DML 문(INSERT, UPDATE, DELETE, MERGE)이 다수 포함된 워크로드(예: 대용량 OLTP)가 있을 경우, PVS 정리 프로세스가 공간을 회수하기 위해 휴지/복구 기간이 필요할 수 있습니다.

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

예를 들어:

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

정리 실패 캡처

SQL Server 2022(16.x)부터 PVS 정리 동작이 오류 로그에 기록됩니다. 일반적으로 이로 인해 10분마다 새 로그 이벤트가 기록됩니다. 정리 통계는 tx_mtvc2_sweep_stats확장 이벤트가 보고됩니다.

관련 콘텐츠