행 버전 관리 리소스 사용
행 버전 관리 프레임워크는 SQL Server의 다음 기능을 지원합니다.
트리거
MARS(Multiple Active Results Sets)
온라인 인덱싱
또한 행 버전 관리 프레임워크는 다음 행 버전 관리 기반 트랜잭션 격리 수준을 지원합니다. 이러한 격리 수준은 기본적으로 설정되지 않습니다.
READ_COMMITTED_SNAPSHOT 데이터베이스 옵션이 ON이면 READ_COMMITTED 트랜잭션이 행 버전 관리를 사용하여 문 수준의 읽기 일관성을 제공합니다.
ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션이 ON이면 SNAPSHOT 트랜잭션이 행 버전 관리를 사용하여 트랜잭션 수준의 읽기 일관성을 제공합니다.
행 버전 관리 기반 격리 수준을 이용하면 읽기 작업에 대해 공유 잠금을 사용하지 않아도 되므로 트랜잭션에 의해 확보되는 잠금 수가 줄어듭니다. 결과적으로 잠금 관리에 사용되는 리소스가 줄어 시스템 성능이 향상됩니다. 또한 다른 트랜잭션에 의해 확보된 잠금으로 인해 트랜잭션이 차단되는 횟수도 줄어 성능이 한층 더 향상됩니다.
행 버전 관리 기반 격리 수준을 이용하면 데이터 수정에 필요한 리소스가 늘어납니다. 이 옵션을 설정하면 데이터베이스의 모든 데이터 수정에 대해 버전이 지정됩니다. 행 버전 관리 기반 격리를 사용하는 활성 트랜잭션이 없는 경우에도 수정 전에 데이터의 복사본이 tempdb에 저장됩니다. 수정 후의 데이터에는 tempdb에 저장된 버전 지정 데이터에 대한 포인터가 포함됩니다. 큰 개체의 경우 개체의 변경된 부분만 tempdb에 복사됩니다.
tempdb의 사용된 공간
각 데이터베이스 엔진 인스턴스의 tempdb에는 해당 인스턴스의 모든 데이터베이스에 대해 생성된 행 버전을 보유할 수 있는 공간이 있어야 합니다. 데이터베이스 관리자는 tempdb에 버전 저장소를 포함할 수 있는 충분한 공간이 있는지를 확인해야 합니다. tempdb에는 다음의 두 버전 저장소가 있습니다.
온라인 인덱스 작성 버전 저장소는 모든 데이터베이스의 온라인 인덱스 작성에 사용됩니다.
공용 버전 저장소는 모든 데이터베이스의 다른 모든 데이터 수정 작업에 사용됩니다.
활성 트랜잭션에서 행 버전에 액세스해야 하는 동안에는 행 버전이 저장되어야 합니다. 1분마다 백그라운드 스레드가 필요 없게 된 행 버전을 제거하여 tempdb에서 공간을 비웁니다. 다음 중 하나에 해당될 경우 장기 실행 트랜잭션은 버전 저장소의 공간이 해제되지 않도록 합니다.
행 버전 관리 기반 격리를 사용합니다.
트리거, MARS 또는 온라인 인덱스 작성 작업을 사용합니다.
행 버전을 생성합니다.
[!참고]
트랜잭션 내부에서 트리거를 호출하면 해당 트리거에 의해 생성된 행 버전은 트리거가 완료된 후 행 버전이 더 이상 필요하지 않아도 트랜잭션이 끝날 때까지 유지됩니다. 이는 행 버전 관리를 사용하는 커밋된 읽기 트랜잭션에도 적용됩니다. 이 유형의 트랜잭션에서는 트랜잭션의 각 문에 대해서만 데이터베이스의 트랜잭션 뷰가 일치해야 하므로 문이 완료된 후에는 트랜잭션의 문에 대해 생성된 행 버전이 필요하지 않습니다. 그러나 트랜잭션의 각 문에 의해 생성된 행 버전은 트랜잭션이 완료될 때까지 유지됩니다.
tempdb의 공간이 부족하면 데이터베이스 엔진에서 버전 저장소를 강제로 축소합니다. 축소하는 동안, 아직 행 버전을 생성하지 않은 트랜잭션 중 장기 실행 트랜잭션은 교착 상태가 발생한 것으로 표시됩니다. 오류 로그에는 교착 상태가 발생한 각 트랜잭션에 대해 메시지 3967이 생성됩니다. 교착 상태가 발생한 것으로 표시된 트랜잭션은 버전 저장소의 행 버전을 더 이상 읽을 수 없습니다. 행 버전을 읽으려고 하면 메시지 3966이 생성되고 트랜잭션이 롤백됩니다. 축소하는 데 성공하면 tempdb에서 공간을 사용할 수 있게 됩니다. 그렇지 않으면 tempdb의 공간이 부족해지고 다음과 같은 상황이 발생합니다.
읽기 작업이 계속 실행되지만 버전을 생성하지 않습니다. 오류 로그에 정보 메시지(3959)가 나타납니다. 데이터를 기록한 트랜잭션은 영향을 받지 않습니다.
tempdb의 완전 롤백으로 인해 생성되지 않은 행 버전에 액세스하려고 하는 트랜잭션은 오류 3958과 함께 종료됩니다.
데이터 행의 공간 사용량
각 데이터베이스 행의 행 끝에서 최대 14바이트를 사용하여 행 버전 관리 정보를 저장할 수 있습니다. 행 버전 관리 정보에는 버전을 커밋한 트랜잭션의 트랜잭션 시퀀스 번호와 버전이 지정된 행에 대한 포인터가 포함됩니다. 이 14바이트는 다음과 같은 조건에서 행이 처음 수정될 때 또는 새 행이 삽입될 때 추가됩니다.
READ_COMMITTED_SNAPSHOT 또는 ALLOW_SNAPSHOT_ISOLATION 옵션이 ON입니다.
테이블에 트리거가 있습니다.
MARS(Multiple Active Results Sets)를 사용하고 있습니다.
테이블에서 현재 온라인 인덱스 작성 작업이 실행되고 있습니다.
이 14바이트는 다음과 같은 조건에서 행이 처음 수정될 때 데이터베이스 행에서 제거됩니다.
READ_COMMITTED_SNAPSHOT 및 ALLOW_SNAPSHOT_ISOLATION 옵션이 OFF입니다.
테이블에 트리거가 없습니다.
MARS를 사용하고 있지 않습니다.
현재 온라인 인덱스 작성 작업이 실행되고 있지 않습니다.
행 버전 관리 기능을 사용하는 경우에는 데이터베이스 행당 14바이트를 사용할 수 있도록 데이터베이스에 더 많은 디스크 공간을 할당해야 할 수 있습니다. 행 버전 관리 정보를 추가하면 인덱스 페이지가 분할되거나 현재 페이지에 사용 가능한 공간이 충분하지 않은 경우에는 새 데이터 페이지가 할당될 수 있습니다. 예를 들어 평균 행 길이가 100바이트인 경우 14바이트를 추가하면 기존 테이블이 최대 14% 증가할 수 있습니다.
채우기 비율을 낮추면 인덱스 페이지의 조각화를 방지하거나 줄일 수 있습니다. 테이블 또는 뷰의 데이터와 인덱스에 대한 조각화 정보를 보려면 DBCC SHOWCONTIG를 사용합니다.
큰 개체의 공간 사용량
SQL Server 데이터베이스 엔진에서는 최대 2GB의 큰 문자열을 저장할 수 있는 6개의 데이터 형식으로 nvarchar(max), varchar(max), varbinary(max), ntext, text 및 image를 지원합니다. 이러한 데이터 형식을 사용하여 저장된 큰 문자열은 데이터 행에 연결된 일련의 데이터 조각에 저장됩니다. 행 버전 관리 정보는 큰 문자열이 저장된 각 조각에 저장됩니다. 데이터 조각은 테이블의 큰 개체에만 사용되는 페이지의 모음입니다.
데이터베이스에 새로 큰 값이 추가될 때 조각당 최대 8040바이트의 데이터가 할당됩니다. 이전 버전의 데이터베이스 엔진에서는 조각당 최대 8080바이트의 ntext, text 또는 image 데이터를 저장했습니다.
이전 버전의 SQL Server에서 SQL Server로 데이터베이스를 업그레이드한 경우에는 행 버전 관리 정보를 저장할 공간을 만들기 위해 기존 ntext, text 및 image LOB(Large Object) 데이터가 업데이트되지 않습니다. 그러나 LOB 데이터가 처음 수정될 때는 버전 관리 정보를 저장할 수 있도록 데이터가 동적으로 업그레이드됩니다. 이는 행 버전이 생성되지 않은 경우에도 마찬가지입니다. LOB 데이터가 업그레이드된 후에는 조각당 저장되는 최대 바이트 수가 8080바이트에서 8040바이트로 줄어듭니다. 업그레이드 프로세스에서는 LOB 값을 삭제하고 동일한 값을 다시 삽입합니다. 1바이트만 수정되더라도 LOB 데이터가 업그레이드됩니다. 이 작업은 각 ntext, text 또는 image 열에 대해 한 번 수행되지만 LOB 데이터의 크기에 따라 각 작업 수행 시 대량의 페이지 할당 및 I/O 작업이 발생할 수 있습니다. 수정 내용 전체가 로깅되는 경우에는 대량의 로깅 작업이 발생할 수도 있습니다. 데이터베이스 복구 모드를 FULL로 설정하지 않으면 WRITETEXT와 UPDATETEXT 작업에서 최소한의 정보만 로깅합니다.
nvarchar(max), varchar(max) 및 varbinary(max) 데이터 형식은 이전 버전의 SQL Server에서는 사용할 수 없으므로 업그레이드 문제가 발생하지 않습니다.
이 요구 사항을 충족하는 충분한 디스크 공간을 할당해야 합니다.
행 버전 관리 및 버전 저장소 모니터링
행 버전 관리, 버전 저장소 및 스냅숏 격리 프로세스의 성능과 문제를 모니터링하기 위해 SQL Server에서는 DMV(동적 관리 뷰) 및 Windows 시스템 모니터의 성능 카운터 형태로 도구를 제공합니다.
DMV
다음 DMV는 tempdb와 버전 저장소 및 행 버전 관리를 사용하는 트랜잭션의 현재 상태에 대한 정보를 제공합니다.
sys.dm_db_file_space_usage. 데이터베이스의 각 파일에 대한 공간 사용량 정보를 반환합니다. 자세한 내용은 sys.dm_db_file_space_usage(Transact-SQL)를 참조하십시오.
sys.dm_db_session_space_usage. 데이터베이스에서 발생하는 세션별 페이지 할당 및 할당 취소 작업을 반환합니다. 자세한 내용은 sys.dm_db_session_space_usage(Transact-SQL)를 참조하십시오.
sys.dm_db_task_space_usage. 데이터베이스에서 발생하는 태스크별 페이지 할당 및 할당 취소 작업을 반환합니다. 자세한 내용은 sys.dm_db_task_space_usage(Transact-SQL)를 참조하십시오.
sys.dm_tran_top_version_generators. 버전 저장소의 버전 대부분을 생성하는 개체에 대한 가상 테이블을 반환합니다. 256개의 집계 레코드 길이를 database_id 및 rowset_id에 따라 그룹화합니다. 이 함수를 사용하면 버전 저장소를 가장 많이 사용하는 소비자를 찾을 수 있습니다. 자세한 내용은 sys.dm_tran_top_version_generators(Transact-SQL)를 참조하십시오.
sys.dm_tran_version_store. 공용 버전 저장소의 모든 버전 레코드를 표시하는 가상 테이블을 반환합니다. 자세한 내용은 sys.dm_tran_version_store(Transact-SQL)를 참조하십시오.
[!참고]
sys.dm_tran_top_version_generators와 sys.dm_tran_version_store 함수는 버전 저장소 전체를 쿼리하므로 버전 저장소의 크기가 클 경우에는 실행하는 데 비용이 많이 들 수 있습니다.
sys.dm_tran_active_snapshot_database_transactions. 행 버전 관리를 사용하는 SQL Server 인스턴스 내의 모든 데이터베이스에 있는 전체 활성 트랜잭션에 대한 가상 테이블을 반환합니다. 시스템 트랜잭션은 이 DMV에 나타나지 않습니다. 자세한 내용은 sys.dm_tran_active_snapshot_database_transactions(Transact-SQL)를 참조하십시오.
sys.dm_tran_transactions_snapshot. 각 트랜잭션에서 사용한 스냅숏을 표시하는 가상 테이블을 반환합니다. 스냅숏에는 행 버전 관리를 사용하는 활성 트랜잭션의 시퀀스 번호가 포함됩니다. 자세한 내용은 sys.dm_tran_transactions_snapshot(Transact-SQL)을 참조하십시오.
sys.dm_tran_current_transaction. 현재 세션에 있는 트랜잭션의 행 버전 관리 관련 상태 정보를 표시하는 단일 행을 반환합니다. 자세한 내용은 sys.dm_tran_current_transaction(Transact-SQL)을 참조하십시오.
sys.dm_tran_current_snapshot. 현재 스냅숏 격리 트랜잭션이 시작될 때의 모든 활성 트랜잭션을 표시하는 가상 테이블을 반환합니다. 현재 트랜잭션에서 스냅숏 격리를 사용하고 있으면 이 함수가 행을 반환하지 않습니다. sys.dm_tran_current_snapshot은 현재 스냅숏에 대한 활성 트랜잭션만 반환한다는 점을 제외하고는 sys.dm_tran_transactions_snapshot과 비슷합니다. 자세한 내용은 sys.dm_tran_current_snapshot(Transact-SQL)을 참조하십시오.
성능 카운터
SQL Server성능 카운터는 SQL Server 프로세스의 영향을 받는 시스템 성능에 대한 정보를 제공합니다. 다음 성능 카운터는 tempdb와 버전 저장소 및 행 버전 관리를 사용하는 트랜잭션을 모니터링합니다. 이러한 성능 카운터는 SQLServer:Transactions 성능 개체에 포함되어 있습니다.
Free Space in tempdb (KB). tempdb 데이터베이스의 사용 가능한 공간(KB)을 모니터링합니다. tempdb에는 스냅숏 격리를 지원하는 버전 저장소를 처리하기에 충분한 공간이 있어야 합니다.
다음 공식을 사용하여 버전 저장소의 예상 크기를 대략적으로 계산할 수 있습니다. 장기 실행 트랜잭션의 경우 생성 및 정리 속도를 모니터링하여 버전 저장소의 예상 최대 크기를 결정하면 유용합니다.
[공용 버전 저장소의 크기] = 2 * [분당 생성되는 버전 저장소 데이터] * [트랜잭션의 최장 실행 시간(분)]
트랜잭션의 최장 실행 시간에는 온라인 인덱스 작성이 포함되지 않습니다. 큰 테이블의 경우 온라인 인덱스 작성 작업에 많은 시간이 소요되므로 이 작업은 별도의 버전 저장소를 사용합니다. 온라인 인덱스 작성 버전 저장소의 크기는 온라인 인덱스 작성을 수행하는 동안 모든 인덱스를 포함하여 테이블에서 수정된 전체 데이터 양과 거의 같습니다.
Version Store Size (KB). 모든 버전 저장소의 크기(KB)를 모니터링합니다. 이 정보를 통해 tempdb 데이터베이스의 버전 저장소에 필요한 공간을 결정할 수 있습니다. 이 카운터를 지속적으로 모니터링하면 tempdb에 필요한 추가 공간을 예측할 수 있습니다.
Version Generation rate (KB/s). 모든 버전 저장소의 버전 생성 속도(KB/초)를 모니터링합니다.
Version Cleanup rate (KB/s). 모든 버전 저장소의 버전 정리 속도(KB/초)를 모니터링합니다.
[!참고]
Version Generation rate (KB/s) 및 Version Cleanup rate (KB/s)에서 제공하는 정보를 통해 tempdb에 필요한 공간을 예측할 수 있습니다.
Version Store unit count. 버전 저장소 단위 수를 모니터링합니다.
Version Store unit creation. 인스턴스가 시작된 이후 행 버전을 저장하기 위해 생성된 버전 저장소 단위의 총 수를 모니터링합니다.
Version Store unit truncation. 인스턴스가 시작된 이후 잘린 버전 저장소 단위의 총 수를 모니터링합니다. SQL Server에서 버전 저장소 단위에 저장된 버전 행이 활성 트랜잭션을 실행하는 데 불필요하다고 결정하면 버전 저장소 단위가 잘립니다.
Update conflict ratio. 총 업데이트 스냅숏 트랜잭션 중 업데이트 충돌이 있는 업데이트 스냅숏 트랜잭션의 비율을 모니터링합니다.
Longest Transaction Running Time. 행 버전 관리를 사용하는 트랜잭션의 가장 긴 실행 시간(초)을 모니터링합니다. 이 정보를 사용하면 특별한 이유 없이 오래 실행되는 트랜잭션이 있는지를 확인할 수 있습니다.
Transactions. 활성 트랜잭션의 총 수를 모니터링합니다. 시스템 트랜잭션은 포함되지 않습니다.
Snapshot Transactions. 활성 스냅숏 트랜잭션의 총 수를 모니터링합니다.
Update Snapshot Transactions. 업데이트 작업을 수행하는 활성 스냅숏 트랜잭션의 총 수를 모니터링합니다.
NonSnapshot Version Transactions. 버전 레코드를 생성하는 활성 비-스냅숏 트랜잭션의 총 수를 모니터링합니다.
[!참고]
Update Snapshot Transactions와 NonSnapshot Version Transactions의 합은 버전 생성에 참여하는 트랜잭션의 총 수를 나타냅니다. Snapshot Transactions와 Update Snapshot Transactions 값의 차이를 보고 읽기 전용 스냅숏 트랜잭션의 수를 알 수 있습니다.