SQL

TADM55 SAP HANA Installing and administering

[진행사항]

Always-on Availability group(AG) 구성의 SQL Server에서 Primary replica가 병렬 Index Rebuild 수행 시 Secondary도 동일한 수량의 병렬 Index Rebuild를 수행하는지를 문의주신 건으로 이해됩니다.

AG의 데이터 동기화는 Primary Replica에서 발생한 Transaction log를 Secondary Replica에서 Redo하는 단계로 진행됩니다.

참고로 SQL Server 2016 이후 Parallel redo thread(Instance당 최대 100개, Database당 최대 16개)를 지원합니다.

다만, 이 Parallel redo thread의 개수는 Primary replica에서 Transaction log를 발생시킨 쿼리의 DoP와 관계 없이, 현재 Secondary replica에서 Transaction log를 Redo하는 부하 상황에 따라 달라지게 됩니다.

: SQL Server 2016/2017: Availability group secondary replica redo model and performance - Microsoft Community Hub

이 때문에, Primary replica에서 병렬 Index Rebuild 수행 시 Secondary replica에서 병렬로 Redo를 수행할 가능성은 높으나,
Index Rebuild 상황을 Redo하는 Secondary Replica의 Redo Thread 개수는, 일반적으로 Primary replica의 Index Rebuild Thread 개수보다 적게 됩니다.

예를 들어 다음과 같이 테스트환경에서 약 10GB 크기의 테이블에 대해, MAXDOP 4의 병렬 Index Rebuild를 수행하여 Secondary Replica에서 Redo thread를 모니터링 하면,
Primary Replica는 DoP 4로 Index Rebuild를 수행하고 있으나, 같은 시점의 Secondary Replica는 3개의 Redo thread로 Transaction을 Redo 하고 있음을 확인할 수 있습니다.

Case	Result
Test Scenario - 약 10GB 크기의 테이블에 Maxdop=4로 Index Rebuild를 수행	EXEC sys.sp_spaceused 'dbo.tbl_Index_ParallelBuild' GO           ALTER INDEX UCL__tbl_Index_ParallelBuild__01 ON dbo.tbl_Index_ParallelBuild REBUILD WITH (MAXDOP = 4) GO
Primary Replica - 첨부드리는 Monitor_IndexRebuild_ParallelRedo.sql 쿼리를 통해 74번 세션에서 수행중인 Index Rebuild 수행 상황을 모니터링 하면, 2개의 Consumer thread를 제외하고, 4개의 Thread로 Parallel Rebuild 수행중임을 확인할 수 있습니다.
Secondary Replica - 첨부드리는 Monitor_IndexRebuild_ParallelRedo.sql 쿼리를 통해 AG의 Redo 수행 상황을 모니터링 하면, AGDB01 데이터베이스에 대해 3개의 Parallel redo thread가 Index rebuild를 수행중임을 확인할 수 있습니다.

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 발생 여부 모니터링

③     Data File 사용률 모니터링

④     Log File 사용률 모니터링

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

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