파티션 테이블 압축

1.     파티션 테이블 압축시 서비스 영향도

-> 온라인으로 가능한지, IO 발생 및 압축진행중인 파티션 사용시 lock 발생 등의 위험요소가 궁금합니다.

-     파티션별 압축의 경우에도 전체 인덱싱과 마찬가지로 온라인 인덱싱을 지원합니다.

Online Indexing의 경우, 인덱싱 작업중 대상 테이블/파티션에 대한 CRUD가 가능하나, 아래와 같이 단계에 따라 작업 및 잠금을 획득하며, 이 과정에서 여러가지 형태의 성능 경합과 잠금이 발생합니다.

다만 해당 환경의 성능 여유가 충분한 상황이라면, 일반적으로 이와 같은 성능 경합이 심각한 성능 영향을 주지는 않습니다.

 

이와 관련하여서는 다음의 문서를 통해 조금 더 세부적인 내용을 확인하실 수 있습니다.

: How Online Index Operations Work - SQL Server | Microsoft Learn

Phase	Source activity	Source locks	Architecture
Preparation : Short phase	-  System metadata preparation to create the new empty index structure.   -  A snapshot of the table is defined. That is, row versioning is used to provide transaction-level read consistency.   -  Concurrent user write operations on the source are blocked for a short period. No concurrent DDL operations are allowed except creating multiple nonclustered indexes.	-  S (Shared) on the table*   -  IS (Intent Shared)   -  INDEX_BUILD_INTERNAL_RESOURCE**
Build : Main phase	-  The data is scanned, sorted, merged, and inserted into the target in bulk load operations.   -  Concurrent user select, insert, update, and delete operations are applied to both the pre-existing indexes and any new indexes being built.	-  IS   -  INDEX_BUILD_INTERNAL_RESOURCE**
Final : Short phase	-  All uncommitted update transactions must complete before this phase starts.   -  Depending on the acquired lock, all new user read or write transactions are blocked for a short period until this phase is completed.   -  System metadata is updated to replace the source with the target.   -  The source is dropped if it is required. For example, after rebuilding or dropping a clustered index.	-  INDEX_BUILD_INTERNAL_RESOURCE**   -  S on the table if creating a nonclustered index.*   -  SCH-M (Schema Modification) if any source structure (index or table) is dropped.*

 

-     다음은 온라인 인덱싱 작업에 대한 일반적인 가이드라인입니다. Online Indexing 작업 중 발생할 수 있는 여러가지 이슈사항과 사전 고려사항을 정리하고 있으며,
Update 부하가 높은 환경에서 Online Indexing 중, 종종 발생하는 Deadlock(자동으로 해소)과 같은 증상도 해당 문서를 통해 확인할 수 있습니다.

: Guidelines for online index operations - SQL Server | Microsoft Learn

 

-> 온라인으로 절대 해서는 안되는 작업인지

-     온라인 수행에 문제는 없습니다. 다만 고객환경의 비즈니스로직에서의 성능 영향도를 파악하기 위해 고객사 내부적으로 충분한 테스트를 거쳐 검증하신 후 작업을 수행하실 것을 권장 드립니다.

 

-> 압축 진행중인 해당 파티션에만 영향을 주는 것인지

-     특정 파티션에 대해 압축을 수행하는 경우, 결과적으로는 해당 파티션에만 영향을 주는 것이 맞습니다.

하지만 압축을 진행하는 과정의 마지막 단계에서 Metadata를 업데이트 하며 Sch-M 잠금을 획득하므로, 동 시간대 대상 테이블에 대해 Sch-S/Sch-M을 요청하는 작업들과의 일시적인 성능 경합이 있을 수 있습니다.

 

2.     테이블/인덱스 압축시 내부 프로세스 및 모니터링 필수 요소

-> 압축대상 파티션을 바로 압축을 시도 하는지 혹은 별도의 데이터파일 여유 공간에 생성후 전환 (rebuild 처럼) 등등

-     인덱스의 파티션별 압축 작업 또한 마찬가지로 ALTER INDEX REBUILD 작업을 수행하며, 데이터파일 신규 공간을 할당 받아 Sort 및 인덱스를 생성한 후 기존의 개체를 대체하게 되므로 추가 공간을 사용하게 됩니다.

예를 들어 30GB 크기의 A인덱스의 1번 파티션을 압축하여 10GB로 압축이 수행된 경우, 작업 시작 시점에 Datafile 내 여유공간이 없었다면 데이터파일의 크기가 10GB 증가하게 됩니다.

 

-     Sort_in_tempdb 옵션을 사용하는 경우에도 새로운 공간에 신규 인덱스를 배치한 후 기존의 인덱스 개체를 제거하므로 추가 Disk 공간의 사용은 발생합니다.

 

-> 롤백이 발생하게 되나요?

-     Offline Indexing의 경우 작업을 중단하는 경우 해당 작업에 대한 Rollback이 발생하며, Online Indexing의 경우에도 작업 중단에 의해 메타데이터 업데이트를 위한 짧은 Rollback이 필요합니다.

-     작업 수행 중 다음의 내용을 모니터링 하실 수 있을 것으로 의견 드립니다.

①     Data / Log Disk 사용률 모니터링

②     Blocked Process 발생 여부 모니터링

-- sys.dm_exec_requests (Transact-SQL) - SQL Server | Microsoft Learn SELECT session_id, status, blocking_session_id, wait_type, wait_time, wait_resource, transaction_id FROM sys.dm_exec_requests WHERE status = N'suspended'; GO

 

③     Data File 사용률 모니터링

SELECT  Database_Name   = db_name(database_id)     ,   file_id     ,   Filesize_MB     = total_page_count/128     ,   Allocated_MB    = allocated_extent_page_count/128     ,   UnAllocated_MB  = unallocated_extent_page_count/128     FROM  sys.dm_db_file_space_usage GO

 

④     Log File 사용률 모니터링

SELECT  DB_Name            = db_name(database_id)     ,   LogfileSize_MB     = total_log_size_in_bytes/1024/1024     ,   LogfileUsed_MB     = used_log_space_in_bytes/1024/1024     ,   LogfileUsed_PCT    = used_log_space_in_percent   FROM  sys.dm_db_log_space_usage GO

 

-> 파티션 압축 작업을 진행하게 되면 로그 사용량의 변화는 어떻게 되는지 궁금합니다.

-     전체 인덱스를 한 번에 압축하는 경우와 한 개 파티션 씩 압축하는 경우 모두 거의 유사한 크기의 Transaction Log가 발생하는 것으로 확인됩니다.

인덱스 전체를 한 번에 압축한 경우: 트랜잭션 로그 2,514 MB 증가

한 개 파티션 씩 12회 압축한 경우: 트랜잭션 로그 2,514 MB 증가

BACKUP LOG [OnlineCompressionWithPartitions] TO DISK = 'NUL'; GO   -- Log Used: 63 MB SELECT  DB_Name            = db_name(database_id)     ,   LogfileSize_MB     = total_log_size_in_bytes/1024/1024     ,   LogfileUsed_MB     = used_log_space_in_bytes/1024/1024     ,   LogfileUsed_PCT    = used_log_space_in_percent   FROM  sys.dm_db_log_space_usage GO   -- Duration: 78 Sec ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = ALL WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4) GO   -- Log Used: 2,577 MB (2,514 MB increased) SELECT  DB_Name            = db_name(database_id)     ,   LogfileSize_MB     = total_log_size_in_bytes/1024/1024     ,   LogfileUsed_MB     = used_log_space_in_bytes/1024/1024     ,   LogfileUsed_PCT    = used_log_space_in_percent   FROM  sys.dm_db_log_space_usage GO

BACKUP LOG [OnlineCompressionWithPartitions] TO DISK = 'NUL'; GO   -- Log Used: 15 MB SELECT  DB_Name            = db_name(database_id)     ,   LogfileSize_MB     = total_log_size_in_bytes/1024/1024     ,   LogfileUsed_MB     = used_log_space_in_bytes/1024/1024     ,   LogfileUsed_PCT    = used_log_space_in_percent   FROM  sys.dm_db_log_space_usage GO   -- Duration: 77 Sec ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 1  WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4) ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 2  WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4) ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 3  WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4) ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 4  WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4) ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 5  WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4) ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 6  WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4) ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 7  WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4) ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 8  WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4) ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 9  WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4) ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 10 WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4) ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 11 WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4) ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 12 WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4)   -- Log Used: 2,529 MB (2,514 MB increased) SELECT  DB_Name            = db_name(database_id)     ,   LogfileSize_MB     = total_log_size_in_bytes/1024/1024     ,   LogfileUsed_MB     = used_log_space_in_bytes/1024/1024     ,   LogfileUsed_PCT    = used_log_space_in_percent   FROM  sys.dm_db_log_space_usage GO