SQL Server transaction log 파일은 physical sector-aligned boundary 로 생성되며 sector-aligned sizes 와 sector-aligned boundaries 로 데이터가 쓰여집니다.
즉, bytes per physical sector (실제 섹터당 바이트) 크기에 따라 저장되는 사이즈가 달라지며 이 값이 512 bytes 인 경우 트랜잭션 로그는 512 Bytes – 60KB 단위로 저장될 수 있습니다. (log buffer size 에 의해서 최대 60KB에 도달하면 자동 flush되므로) 만약 Physical Sector 크기가 4KB일 경우라면 4 KB – 60KB 길이로 저장될 수 있습니다.
테스트를 위해 실제 아래 쿼리를 수행했을 때, Physical sector 에 따라 차지하는 디스크 공간이 다른 것을 볼 수 있습니다.
아래 예제에서는 10000개의 섹터에 로그 레코드를 기록해야 합니다. 즉, 512 bytes * 10000 의 결과와 4096 bytes * 10000 의 결과와 크게 차이나지 않을 것입니다.
- Bytes per Physical sector (512) : 약 5MB
- Bytes per Physical sector (4096): 약 40MB
WHILE(@I < 10000)
BEGIN
BEGIN TRAN
INSERT INTO tbl1 values ('A', @I)
COMMIT TRAN
SET @I = @I + 1
END
Concurrent transaction 은 log block 공간을 공유하게 되므로 아래 쿼리는 log buffer 사이즈를 최대한 채우게 되므로 60KB 씩 쓰기를 시도하게 되며, Physical sector 512 bytes, 4K 모두 약 1MB의 로그 파일 공간을 쓰게 됩니다.
BEGIN TRAN
WHILE(@I < 10000)
BEGIN
INSERT INTO tbl1 values ('A', @I)
SET @I = @I + 1
END
COMMIT TRAN
SQL Server 내부 코드에서 검증해 보면 Log flush 시점에 file write offset 을 결정하기 위해 bytes per physical sector 값을 참조하는 것을 확인할 수 있습니다.
결과적으로 4K 미만의 트랜잭션 레코드를 빈번히 기록해야 하는 작은 트랜잭션이 단위가 많은 환경이라면 physical sector 512 bytes 보다 4KB 환경이 훨씬 더 많은 공간을 사용하게 되며 이는 트랜잭션 로그 쓰기 디자인이며 이 차이는 트랜잭션 로그 크기와 로그 백업 크기에 영향을 미치게 됩니다.
테스트 과정에서 이해를 돕기 위해 아래 툴을 사용하면 도움이 됩니다.
1. FSUTIL FSINFO NTFSINFO F:
2. Process Monitor 로 LDF 파일에 대한 Detail 정보를 통해 Offset, Lengh 확인
3. Hex editor 를 통해 로그 레코드가 기록된 offset 확인
4. SELECT [Log Record], * FROM fn_dblog(NULL,NULL) 쿼리를 통해 로그 레코드 확인
5. DBCC LOGINFO 를 통해 Startoffest 위치
작성자: Lai Go / 작성일자: 2018.12.26
