테이블은 MSSQL에서 가장 최소단위의 데이터 집합 단위입니다.
데이터의 양이 많을수록 테이블을 읽고 쓰고 수정하는 데 필요한 비용이 늘어나는데, 이를 개선하기 위한 기능으로 분할된(Partitioned) 테이블이 존재하고, 이 기능은 관리 효율성과 성능 면에서 다음과 같은 이점이 있습니다.
1. 데이터 하위 집합을 빠르고 효율적으로 전송하거나 액세스할 수 있을 뿐만 아니라 데이터 컬렉션의 무결성을 유지할 수 있습니다.
2. 하나 이상의 파티션에서 유지 관리 작업을 더 빠르게 수행할 수 있습니다.
전체 테이블 대신 이 데이터 하위 집합만 대상으로 하기 때문에 작업이 더 효율적입니다.
3. 자주 실행하는 쿼리 유형과 사용 중인 하드웨어 구성에 따라 쿼리 성능이 향상될 수 있습니다.
4. 전체 테이블이 아니라 파티션 수준에서 잠금 에스컬레이션을 설정하여 성능을 향상시킬 수 있습니다.
그리고 분할된 테이블을 사용하게 되면 통계적 측면에서도 이점이 생깁니다. 증분통계(Incremental)옵션이 그 기능입니다.
통계는 기본적으로 인덱스를 생성할 때 같은 이름의 통계가 자동 생성됩니다. (인덱스가 없는 column을 조건으로 조회했을 때 생기기도 합니다.) 이렇게 생성된 통계 정보는 Query를 수행했을 때 사용되는 비용을 예측하여, 최적화된 실행 계획을 세울 수 있게 해줍니다.
하지만 데이터를 수정·변경·삭제할 때 통계 데이터도 업데이트가 되고, 테이블의 모든 데이터를 기반으로 업데이트 하고 있기 때문에 그에 따른 비용이 많이 발생합니다. 비효율적인 예시로, 최근 대량의 데이터가 삽입, 수정, 삭제되는 상황에서는 과거 데이터에 대한 통계에 변화가 없습니다.
하지만 통계가 업데이트가 될 때에는 테이블의 전체 데이터를 기반으로 샘플링하여 업데이트를 하게 되고, 과거 데이터까지 포함하여 통계 업데이트가 되기 때문에 이 부분에서 비효율이 발생합니다. 증분 통계는 그 비효율을 감소시킬 수 있습니다.
이제 Partition의 효율을 확인하기 위해 일반테이블과 분할된 테이블의 조회에 대한 I/O 비교와 일반 통계와 증분 통계의 통계 업데이트에 쓰이는 I/O 비교를 해보겠습니다.
2. 인덱스는 ModifiedDate 컬럼에 Non Clustered로 생성하였습니다.
3. 전체 테이블의 row 수는 약 378만이고, 단독 Partition으로 구성되어 있습니다.
4. 해당 쿼리를 수행하면 Table Scan을 하게 됩니다. 모든 데이터를 스캔하여 제시된 조건에 맞는 데이터만 결과 집합으로 나타냅니다.
논리적 읽기를 44,274만큼 합니다.
분할된 테이블
1. 파티션 데이블은 데이터를 특정 컬럼(ModifiedDate)에 기준을 만들어서 분할하는 테이블입니다.
2. TestSalesOrderDetail에서 ModifiedDate 컬럼을 연도별로 기준을 지정했고, 그 기준에 따라 분할되어 저장했습니다.
3. Partition을 각각 확인해보면, 전체 테이블의 row 수는 약 368만이고,
1번 partition은 6,718, 2번은 315,247, 3번은 1,875,219, 4번은 1,482,530입니다.
4. 이전과 동일한 쿼리를 수행하면 Table Scan을 하지만, 테이블의 전체 데이터를 스캔하지 않고,
제시된 조건이 포함하는 Partition만 스캔하여 결과 집합으로 나타냅니다.
논리적 읽기를 4,519로 일반 테이블에 비해 1/10 정도 감소한 것으로 볼 수 있습니다.
5. 일반 테이블과 분할된 테이블의 StmtText를 비교해 보면, 분할된 테이블에서는
조회하고자하는 데이터가 속한 Partition을 먼저 탐색한 후에 스캔을 하기 때문에 논리적 읽기 수를 많이 줄일 수 있습니다.
위가 일반, 아래가 Partitioned 테이블의 동일한 쿼리에 대한 StmtText입니다.
일반적인 통계
1. 일반적인 테이블이나 분할된 테이블에서 인덱스를 생성하면 모두 통합된 통계로 생성됩니다.
아래에 보이는 것은 내부적으로 수행되는 Trace인데, 통계를 생성하기 위한 I/O가 쓰이는 것을 볼 수 있고,
통계 데이터를 조회해보면 테이블의 전체 데이터를 기반으로 샘플링하는 것을 확인할 수 있습니다.
2. 특정 파티션에 해당하는 데이터만 변경했을 때에도 옵티마이저는 테이블의 전체 데이터를 샘플링하여 통계를 업데이트합니다.
(샘플링은 데이터 양에 따라 비율이 조정됩니다.) 테이블이 분할되어 있어도 통계 데이터는 분할되어 있지 않기 때문입니다.
통계가 자동 업데이트 될 수 있도록 임계치 이상의 데이터를 업데이트 하였고, 관측한 통계 업데이트에 사용되는 I/O는 1,947입니다.
증분 통계
1. 이런 비효율을 해결할 수 있는 옵션은 Incremental(증분 통계) 업데이트 입니다.
2. 이 옵션은 테이블이 Partitioning 되어있을 경우 ON/OFF 할 수 있는 옵션이며, SQL Server 2014 이상 버전에서 사용이 가능합니다.
3. 옵션을 ON으로 설정하게 되면 기존의 통계를 분할된 통계로 재생성하게 됩니다.
4. 분할된 통계로 구성된 이후에 데이터를 수정하거나 변경이 있어 통계가 업데이트 되는 경우, 모든 데이터의 통계를 업데이트하지 않고 변경된 데이터가 속한 Partiton의 통계만 업데이트하게 됩니다. 통계 업데이트에 쓰이는 I/O는 185로 일반 통계에 비해 1/11 정도 감소한 것을 알 수 있습니다.
분할된 테이블 및 증분 통계 옵션을 통한 기대효과를 아래 표로 정리하며 마무리하겠습니다.
<분할된 테이블 및 증분 통계 옵션을 통한 기대효과>
기고 | 신기술본부 김국현
편집 | 사업기획팀 박예영
'엑셈 경쟁력 > 엑.기.스' 카테고리의 다른 글
| 엑기스 | XAIOps 톺아보기 2 - 엑셈의 기술력이 집대성된 솔루션 (0) | 2020.08.10 |
|---|---|
| 엑기스 | XAIOps 톺아보기 1 : 원천 데이터 수집의 근간 (0) | 2020.07.02 |
| 엑기스 | InterMax의 변화 (0) | 2020.05.15 |
| 엑기스 | 지능형 전력 빅데이터 예측, AutoML이 답! (0) | 2020.03.13 |
| 엑기스 | MaxGauge와 카카오톡 연동 (0) | 2020.02.10 |
댓글