가속화된 데이터베이스 복구 모니터링 및 문제 해결

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

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

PVS 크기 검사

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

다음 예제 진단 쿼리는 현재 PVS 크기, 정리 프로세스 및 PVS 크기가 0보다 큰 모든 데이터베이스의 기타 세부 정보에 대한 정보를 보여 줍니다.

SELECT pvss.database_id,
       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_percent_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,
       pvss.oldest_aborted_transaction_id,
       pvss.oldest_active_transaction_id,
       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.master_files AS mf
            WHERE mf.database_id = pvss.database_id
                  AND
                  mf.state = 0
                  AND
                  mf.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.persistent_version_store_size_kb > 0
ORDER BY persistent_version_store_size_kb DESC;

SQL 데이터베이스 및 패브릭

SELECT pvss.database_id,
       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 / IIF(pvss.database_id = 2, tdf.total_db_size_kb, df.total_db_size_kb) AS pvs_percent_of_database_size,
       IIF(pvss.database_id = 2, tdf.total_db_size_kb, 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,
       pvss.oldest_aborted_transaction_id,
       pvss.oldest_active_transaction_id,
       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
CROSS APPLY (
            SELECT SUM(size * 8.) AS total_db_size_kb
            FROM tempdb.sys.database_files
            WHERE state = 0
                  AND
                  type = 0
            ) AS tdf
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 IN (DB_ID(), 2)
ORDER BY persistent_version_store_size_kb DESC;

pvs_percent_of_database_size 열을 확인하여 총 데이터베이스 크기를 기준으로 PVS의 크기를 확인합니다. 일반적인 PVS 크기와 애플리케이션 작업의 일반적인 기간 동안 표시되는 기준선 간의 차이를 확인합니다. PVS는 기준선보다 훨씬 크거나 데이터베이스 크기의 50%에 가까운 경우 큰 것으로 간주됩니다.

PVS 크기가 감소하지 않는 경우 다음 문제 해결 단계를 사용하여 큰 PVS 크기의 이유를 찾아 해결합니다.

팁 (조언)

다음 문제 해결 단계에서 언급된 열은 이 섹션의 진단 쿼리 결과 집합에 있는 열을 참조합니다.

큰 PVS 크기는 다음과 같은 이유로 인해 발생할 수 있습니다.

• 장기 실행 활성 트랜잭션
• 오래 실행되는 활성 스냅샷 검사
• 장기 실행 쿼리 보조 복제본
• 중단된 트랜잭션

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

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

DECLARE @LongTxThreshold int = 900;  /* 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 the generated log threshold for long-running transactions */

SELECT  dbtr.database_id,
        DB_NAME(dbtr.database_id) AS database_name,
        st.session_id,
        st.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 st
ON tr.transaction_id = st.transaction_id
INNER JOIN sys.dm_exec_sessions AS sess
ON st.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 = st.transaction_id
WHERE GETDATE() >= DATEADD(second, @LongTxThreshold, tr.transaction_begin_time)
      OR
      dbtr.database_transaction_log_bytes_used >= @LongTransactionLogBytes;

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

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

• SQL Server에서 느리게 실행되는 쿼리 문제 해결
• Azure SQL Database 검색 가능한 유형의 쿼리 성능 병목 상태
• SQL Server 및 Azure SQL Managed Instance에서 검색 가능한 쿼리 성능 병목 현상의 유형

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

장기간 실행되는 활성 스냅샷 검사는 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 sdt.transaction_id,
       sdt.transaction_sequence_num,
       s.database_id,
       s.session_id,
       s.login_time,
       GETDATE() AS query_time,
       s.host_name,
       s.program_name,
       s.login_name,
       s.last_request_start_time
FROM sys.dm_tran_active_snapshot_database_transactions AS sdt
INNER JOIN sys.dm_exec_sessions AS s
ON sdt.session_id = s.session_id;

PVS 정리 지연을 방지하려면 다음을 수행합니다.

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

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

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

pvs_off_row_page_skipped_low_water_mark 열의 큰 값은 보조 복제본에서 실행되는 장기 쿼리 때문에 정리 지연일 가능성이 있습니다. PVS 정리를 유지하는 것 외에도 보조 복제본에서 장기 실행 쿼리는 고스트 정리유지할 수 있습니다.

주 복제본에서 다음 예제 쿼리를 사용하여 보조 복제본에서 장기 실행 쿼리가 PVS 정리를 방해할 수 있는지 확인할 수 있습니다. 쓰기 워크로드가 주 복제본에서 실행되고 있지만 low_water_mark_for_ghosts 열의 값이 예제 쿼리의 한 실행에서 다음 쿼리로 증가하지 않는 경우 PVS 및 고스트 정리는 보조 복제본에서 장기 실행 쿼리에 의해 유지될 수 있습니다.

SELECT database_id,
       DB_NAME(database_id) AS database_name,
       low_water_mark_for_ghosts,
       synchronization_state_desc,
       synchronization_health_desc,
       is_suspended,
       suspend_reason_desc,
       secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states
WHERE is_local = 1
      AND
      is_primary_replica = 1;

자세한 내용은 sys.dm_hadr_database_replica_stateslow_water_mark_for_ghosts 열에 대한 설명을 참조하세요.

SQL 데이터베이스 및 패브릭

SELECT database_id,
       DB_NAME(database_id) AS database_name,
       low_water_mark_for_ghosts,
       synchronization_state_desc,
       synchronization_health_desc,
       is_suspended,
       suspend_reason_desc,
       secondary_lag_seconds
FROM sys.dm_database_replica_states
WHERE is_local = 1
      AND
      is_primary_replica = 1;

자세한 내용은 sys.dm_database_replica_stateslow_water_mark_for_ghosts 열에 대한 설명을 참조하세요.

각 읽기 가능 보조 복제본에 연결하여 오래 실행되는 쿼리를 포함한 세션을 찾아, 허용될 경우 해당 세션을 종료하는 것을 고려하십시오. 자세한 내용은 느린 쿼리 찾기참조하세요.

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

aborted_version_cleaner_start_time 및 aborted_version_cleaner_end_time 열을 확인하여 마지막으로 중단된 트랜잭션 정리가 완료되었는지 확인합니다. oldest_aborted_transaction_id 중단된 트랜잭션 정리가 완료된 후 더 높게 이동해야 합니다. oldest_aborted_transaction_id oldest_active_transaction_id보다 훨씬 낮고 current_abort_transaction_count 값이 큰 경우 PVS 정리를 방지하는 오래된 중단된 트랜잭션이 있을 수 있습니다.

많은 수의 중단된 트랜잭션으로 인한 PVS 정리 지연을 해결하려면 다음을 고려합니다.

• SQL Server 2022(16.x)를 사용하는 경우 ADR Cleaner Thread Count 서버 구성의 값을 늘입니다. 자세한 내용은 서버 구성: ADR 클리너 스레드 수을 참조하세요.
• 가능하면 워크로드를 중지하여 버전 클리너가 진행되도록 합니다.
• 애플리케이션을 검토하여 높은 트랜잭션 중단 속도 문제를 식별하고 해결합니다. 중단은 높은 교착 상태, 중복 키, 제약 조건 위반 또는 쿼리 시간 제한에서 비롯될 수 있습니다.
• PVS 클리너에 필요한 개체 수준 또는 파티션 수준 IX 잠금과 호환되지 않는 잠금을 줄이기 위해 워크로드를 최적화합니다. 자세한 내용은 잠금 호환성참조하세요.
• SQL Server를 사용하는 경우 PVS 크기를 제어하는 비상 전용 단계로 ADR을 사용하지 않도록 설정합니다. 을(를) 참조하여 ADR을(를) 사용 중지하십시오.
• SQL Server를 사용하고 중단된 트랜잭션 정리가 최근에 성공적으로 완료되지 않은 경우 오류 로그에서 VersionCleaner 문제를 보고하는 메시지를 확인합니다.
• 정리가 완료된 후에도 PVS 크기가 예상대로 줄어들지 않으면 pvs_off_row_page_skipped_oldest_aborted_xdesid 열을 확인합니다. 큰 값은 중단된 트랜잭션의 행 버전에서 여전히 공간을 사용하고 있음을 나타냅니다.

PVS 크기 제어

대량의 DML 문(INSERT, UPDATE, DELETE, MERGE)이 많은 워크로드(예: 대용량 OLTP)가 있고 PVS 크기가 큰지 관찰하는 경우 PVS 크기를 제어하기 위해 ADR Cleaner Thread Count 서버 구성의 값을 늘려야 할 수 있습니다. 자세한 내용은 Server 구성을 참조하세요. SQL Server 2022(16.x)부터 사용할 수 있는 ADR 클리너 스레드 수.

SQL Server 2019(15.x)에서 또는 ADR Cleaner Thread Count 구성의 값을 늘리면 PVS 크기를 충분히 줄이는 데 도움이 되지 않는 경우 워크로드는 공간을 회수하기 위해 PVS 정리 프로세스에 대한 휴식/복구 기간이 필요할 수 있습니다.

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

예를 들어:

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

정리 실패 캡처

SQL Server 2022(16.x)부터 주목할 만한 PVS 정리 메시지가 오류 로그에 기록됩니다. 정리 통계는 tx_mtvc2_sweep_stats확장 이벤트가 보고됩니다.

관련 콘텐츠

• 가속화된 데이터베이스 복구
• 가속 데이터베이스 복구 관리
• sys.dm_tran_persistent_version_store_stats
• sys.sp_persistent_version_cleanup
• sys.dm_tran_aborted_transactions