SQL Server가 자동으로 Version Store의 size를 자동으로 관리하고 저장된 행을 필요 이상으로 오래 유지하지 않도록 하는 Thread를 작동한다.
하지만 여기에도 문제가 있는데 SQL Server의 정리 Thread가 더 Version Store가 더 많은 용량이 필요하다고 판단하여 tempdb의 size를 키우려고 할 때 디스크가 꽉 차서 더 이상 늘릴 수 없을 경우 RCSI(또는 SI)이 포함된 쿼리는 모두 실패할 것이다.
따라서 지속적으로 운영 및 모니터링을 해줄 필요성이 있다.
Windows Perfmon 에서 다음과 같은 항목을 모니터링 한다.
l Transactions: Free space in tempdb
- tempdb의 여유 공간의양을 모니터링 함으로써 Row Versioning을 유지하는데 문제가 발생할지 않도록 사용자가 tempdb를 설정한다.
l Transactions: Version store Size
- 해당 항목은 Version Store Size를 KB 단위로 모니터링 한다.
- 해당 항목을 통해 tempdb의 여유 공간을 설정하는 자료로 이용될 수 있다.
l Transactions: Version generation rate
- 초 당 Version Store 를 KB 단위로 얼마나 생성하는지에 대한 항목
l Transactions: Version cleanup rate
- 초 당 Version Store 를 KB 단위로 얼마나 제거하는지에 대한 항목
l Transactions: Update conflict ratio
- 업데이트 충돌이 발생된 Snapshot Transaction의 비율을 나타내는 항목
- 업데이트 충돌이 발생된 Snapshot Transaction / 전체 발생된 Snapshot Transaction
l Transactions: Longest transaction running time
- RCSI(또는 SI)가 설정된 Transaction 중 가장 실행 시간이 길었던 Transation 시간
- 해당 시간을 통해 tempdb의 최대 크기를 설정하는데 자료로 사용
l Transactions: Snapshot transactions
- 총 진행된 Snapshot Transaction의 수를 나타내는 항목
먼저 AdventureWorks2019.Sales.SalesOrderDetail 를 기반으로 테스트 데이터 생성 후
RCSI 사용 옵션을 설정한다.
SELECT *
INTO AdventureWorks2019.dbo.vs_test_tbl
FROM AdventureWorks2019.Sales.SalesOrderDetail
ALTER DATABASE AdventureWorks2019 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE AdventureWorks2019 SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
ALTER DATABASE AdventureWorks2019 SET MULTI_USER
이 후 Profiler와 PerfMon을 설정하고 테스트를 진행했다.
Profiler는 Batch의 Start와 End만 수집하고 PerfMon은 위의 사항을 모두 수집했다.
다음과 같은 구문으로 Update 작업을 발생하여 Snapshot이 발생되도록 한다.
UPDATE AdventureWorks2019.dbo.vs_test_tbl
SET UnitPriceDiscount = 99.99
다음과 같은 결과를 확인해 볼 수 있다.
UPDATE 구문이 발생되면서 Version Store에 데이터가 생성되면서 Version generation rate 가 상승되었다가 생성이 완료되면 줄어들고 Version store Size 가 늘어나면서 Free space in tempdb가 감소하는 것을 볼 수 있다.
위와 같이 수집기로 모니터링이 가능하고 관련 DMV 또한 제공하고 있다.
l sys.dm_tran_version_store
- Version Store에 저장된 모든 버전 레코드를 표시
- transaction_sequence_num : XSN
- version_sequence_num : Transaction 내에 고유한 번호로 transaction_sequence_num가 변경될 때마다 1부터 시작
- database_id : 데이터베이스 고유 번호
- rowset_id : Index나 heap의 행 집합의 고유 ID
- status : Version이 지정된 행이 2개 레코드로 분한되었는지 여부 0이면 1개 페이지에 저장되어 있고, 1이면 지정된 행이 2개의 페이지에 걸쳐 저장되어 있다.
- min_length_in_bytes : 행의 최소 길이(Byte)
- record_length_first_part_in_bytes : Version Store 행에서 첫 번째 부분의 길이(Byte)
- record_image_first_part : 행이 이진 이미지 형태 일 때 Version Store 행에서 첫 번째 부분의 이미지
- record_length_second_part_in_bytes : Version Store 행에서 두 번째 부분의 길이(Byte)
- record_image_second_part : 행이 이진 이미지 형태 일 때 Version Store 행에서 두 번째 부분의 이미지
l sys.dm_tran_top_version_generators
- Version Store에서 가장 많은 부분을 차지하는 개체 정보를 리턴
- database_id : 데이터베이스 고유 번호
- rowset_id : Index나 heap의 행 집합의 고유 ID
- aggregated_record_length_in_bytes : 한 행의 길이의 합계 (Byte)
l sys.dm_tran_active_snapshot_database_transactions
- Row Versioning 되어 있는 모든 Active Transaction 정보
- transaction_id : Transaction 고유 번호
- transaction_sequence_num : XSN
- commit_sequence_num : Commit 완료 또는 중지된 시점을 나타내는 번호
- is_snapshot : 스냅샷 격리 여부
- session_id : Transaction을 시작한 Session의 ID
- first_snapshot_sequence_num : Snapshot을 생성할 때의 가장 낮은 XSN
- max_version_chain_traversed : Version Chain의 최대 길이
- average_version_chain_traversed : Version Chain의 Row Version 수
- elapsed_time_seconds : Transaction이 XSN을 발급 받고서 경과된 시간
다음과 같이 2개의 행을 무한 루프에서 업데이트하도록 하면
WHILE 1=1
BEGIN
UPDATE AdventureWorks2019.dbo.vs_test_tbl
SET UnitPriceDiscount = 99.99
WHERE SalesOrderID IN (43659,43661)
WAITFOR DELAY '00:00:01'
END
다음과 같이 해당 Session에서 진행된 Snapshot Transaction의 정보를 조회 할 수 있고
SELECT transaction_sequence_num
, commit_sequence_num
, is_snapshot
, t.session_id
, first_snapshot_sequence_num
, max_version_chain_traversed
, elapsed_time_seconds
, CASE transaction_isolation_level
WHEN 0 THEN 'None'
WHEN 1 THEN 'ReadCommitted'
WHEN 2 THEN 'ReadUncommitted'
WHEN 3 THEN 'RepeatableRead'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS transaction_isolation_level
FROM sys.dm_tran_active_snapshot_database_transactions t
INNER JOIN sys.dm_exec_sessions s ON t.session_id = s.session_id
아래와 같이 현재 Version Store에 저장된 데이터의 정보를 확인할 수 있으며
SELECT A.transaction_sequence_num
, A.version_sequence_num
, A.rowset_id
, B.name
FROM sys.dm_tran_version_store AS A
INNER JOIN sys.databases AS B
ON A.database_id = B.database_id
아래와 같이 해당 행의 Version 정보 중 가장 용량을 사용할 때의 정보를 조회할 수 있다.
(위의 루프문이 2개의 행을 update 하기 때문에 2개의 행이 조회된다.)
SELECT A.database_id
, A.aggregated_record_length_in_bytes
, B.name
FROM sys.dm_tran_top_version_generators AS A
INNER JOIN sys.databases AS B
ON A.database_id = B.database_id
참조
'Database > SQL Server' 카테고리의 다른 글
| 가속 데이터베이스 복구 [ADR] (3) 가속 데이터베이스 복구 (0) | 2021.11.24 |
|---|---|
| 가속 데이터베이스 복구 [ADR] (1) Row Versioning (0) | 2021.11.18 |
| SQL Server Connection Pool 대하여 (0) | 2021.11.11 |
| 가용성 그룹(Always On) 구축 [On Azure] (7) 수신기 구성 (0) | 2021.10.01 |
| 가용성 그룹(Always On) 구축 [On Azure] (6) Alaways On 구성 (0) | 2021.09.29 |
최근댓글