적용 대상:
SQL Server 2016 (13.x) 및 이후 버전: Azure SQL 데이터베이스,Azure SQL Managed Instance,Microsoft Fabric의 SQL 데이터베이스
시스템 버전 버전 시각 테이블은 모든 행의 이전 버전을 히스토리 테이블에 보관합니다. 히스토리 테이블은 다음 조건에서 일반 테이블보다 데이터베이스 크기를 더 크게 늘릴 수 있습니다:
- 역사적 데이터를 오랜 기간 동안 보관합니다.
- 업데이트 또는 삭제가 많은 데이터 수정 패턴이 있습니다.
크고 계속 커지는 히스토리 테이블은 저장 비용과 시간 쿼리에 부과되는 성능 세금 때문에 문제가 될 수 있습니다. 히스토리 테이블에 대한 데이터 보존 정책을 개발하는 것은 모든 시간 테이블의 수명 주기를 계획하고 관리하는 데 중요한 부분입니다.
데이터 보존 정책을 계획하세요
시간 표 데이터 보존을 관리하려면 먼저 각 시간 표에 필요한 보존 기간을 결정합니다. 대부분의 경우, 보존 정책은 시간 테이블을 사용하는 애플리케이션의 비즈니스 로직의 일부가 되어야 합니다. 예를 들어, 데이터 감사 및 시간 여행 시나리오의 응용은 온라인 쿼리를 위해 과거 데이터가 얼마나 오래 제공되어야 하는지에 대한 확고한 요구사항이 있습니다.
데이터 보존 기간을 정한 후에는 과거 데이터를 관리할 계획을 세우세요. 기록 데이터를 저장하는 방법 및 위치와 보존 요구 사항보다 오래된 기록 데이터를 삭제하는 방법을 결정합니다.
이 글의 모든 접근법은 현재 표 ValidTo 에서 주기 끝에 해당하는 열, 즉 다음 예시들의 열에 작용합니다. 각 행의 기간 종료 값에 따라 행 버전이 종료되는 순간, 즉 기록 테이블에 저장되는 순간이 결정됩니다. 예를 들어, 이 상태 ValidTo < DATEADD (DAY, -30, SYSUTCDATETIME()) 는 30일 이상 된 과거 데이터와 일치합니다.
다음 중 하나를 선택하여 해당 행에 대해 행동하세요:
| Approach | 작동 방식 | 사용 시기 |
|---|---|---|
| 시간 역사 보존 정책 | 각 테이블에 보존 기간을 설정하고, 백그라운드 작업에서 오래된 행을 자동으로 삭제합니다. | 가장 간단한 방법은 오래된 기록을 완전히 삭제할 수 있다는 것입니다. |
| 테이블 분할 | 슬라이딩 윈도우는 가장 오래된 파티션을 히스토리 테이블에서 분리하여 아카이브하거나 폐기할 수 있게 합니다. | 과거 데이터를 삭제하기 전에 아카이브하고 싶거나, 시간 쿼리를 위한 파티션 제거를 원할 때 그렇습니다. |
| 사용자 지정 정리 스크립트 | 예약된 스크립트는 시스템 버전 관리를 비활성화하고, 오래된 행을 작은 단위로 삭제한 후 시스템 버전 관리를 다시 활성화합니다. | 테이블에 대해 보존 정책을 사용할 수 없고 파티셔닝을 적용할 수 없을 때. |
이 글의 분할 및 맞춤 정리 예시는 시스템 버전 시각 테이블 만들기(Create a system-versioned temporal table ) 문서의 샘플을 사용합니다.
시간 기록 보존 정책을 사용하세요
적용 대상: SQL Server 2017 (14.x) 및 이후 버전, Azure SQL Database, Azure SQL Managed Instance, Microsoft Fabric의 SQL 데이터베이스.
개별 테이블 수준에서 시간 기록 보존을 설정할 수 있어 유연한 연령 정책을 만들 수 있습니다. 시간 보존을 가능하게 하려면 테이블 생성 또는 스키마 변경 시 설정 HISTORY_RETENTION_PERIOD 하세요.
보존 정책을 정의한 후, 데이터베이스 엔진은 예약된 백그라운드 작업을 실행하여 보존 기간보다 오래된 기간 값이 있는 과거 행을 찾아 투명하게 제거합니다.
보존 정책을 구성하는 방법
temporal 테이블에 대한 보존 정책을 구성하기 전에 먼저 temporal 기록 보존이 데이터베이스 수준에서 사용하도록 설정되었는지 여부를 확인합니다.
SELECT is_temporal_history_retention_enabled,
name
FROM sys.databases;
데이터베이스 플래그는 is_temporal_history_retention_enabled 기본값으로 설정 ON되어 있지만, 문을 ALTER DATABASE 사용해 변경할 수 있습니다. 데이터베이스 엔진은 Point-in-time restore considerations에 설명된 대로 시점 복원(PITR) 작업 후에도 해당 값을 자동으로 OFF로 설정합니다. 데이터베이스의 시간 이력 보존 정리를 활성화하려면 다음 문을 실행하십시오. 변경하고자 하는 데이터베이스로 교체하세요 <myDB> :
ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION ON;
Important
시간 테이블 is_temporal_history_retention_enabledOFF에 대한 보존을 설정할 수 있지만, 이 경우 데이터베이스 엔진은 노후 행에 대해 자동 정리를 트리거하지 않습니다.
테이블 생성 시 매개변수 값을 HISTORY_RETENTION_PERIOD 지정하여 유지 정책을 구성할 수 있습니다:
CREATE TABLE dbo.WebsiteUserInfo
(
UserID INT NOT NULL PRIMARY KEY CLUSTERED,
UserName NVARCHAR (100) NOT NULL,
PagesVisited INT NOT NULL,
ValidFrom DATETIME2 (0) GENERATED ALWAYS AS ROW START,
ValidTo DATETIME2 (0) GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
SYSTEM_VERSIONING = ON (
HISTORY_TABLE = dbo.WebsiteUserInfoHistory,
HISTORY_RETENTION_PERIOD = 6 MONTHS
)
);
이 정책이 적용되면, 다음 조건을 충족하면 행 dbo.WebsiteUserInfoHistory 이 정리 대상이 됩니다:
ValidTo < DATEADD (MONTH, -6, SYSUTCDATETIME())
보유 기간 DAYS은 , WEEKS, MONTHS, 또는 YEARS에 지정할 수 있습니다.
HISTORY_RETENTION_PERIOD를 생략하면 보존 기간의 기본값은 INFINITE입니다.
INFINITE 키워드를 명시적으로 사용할 수도 있습니다.
어떤 경우에는 테이블 생성 후 보존 설정을 하거나 이전에 설정된 값을 변경하고 싶을 수도 있습니다. 이 경우 ALTER TABLE 문을 사용합니다.
ALTER TABLE dbo.WebsiteUserInfo
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));
Important
로 OFF 설정해도 SYSTEM_VERSIONING 보존 기간의 값은 유지되지 않습니다. 명시적인 SYSTEM_VERSIONING 없이 ON을(를) INFINITE(으)로 설정하면 HISTORY_RETENTION_PERIOD가 유지됩니다.
보존 정책의 현재 상태를 검토하려면 다음 샘플을 사용합니다. 이 쿼리는 데이터베이스 수준에서 temporal 보존 활성화 플래그를 개별 테이블의 보존 기간과 결합합니다.
SELECT DB.is_temporal_history_retention_enabled,
SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
T1.name AS TemporalTableName,
SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
T2.name AS HistoryTableName,
T1.history_retention_period,
T1.history_retention_period_unit_desc
FROM sys.tables AS T1
OUTER APPLY (
SELECT is_temporal_history_retention_enabled
FROM sys.databases
WHERE name = DB_NAME()
) AS DB
LEFT OUTER JOIN sys.tables AS T2
ON T1.history_table_id = T2.object_id
WHERE T1.temporal_type = 2;
데이터베이스 엔진에서 오래된 행을 삭제하는 방법
정리 프로세스는 기록 테이블의 인덱스 레이아웃에 따라 달라집니다. 유한한 보존 정책은 클러스터 행스토어(B-트리)나 클러스터 컬럼스토어 인덱스가 있는 히스토리 테이블에만 설정할 수 있습니다. 백그라운드 작업은 유한한 보존 기간을 가진 모든 시간 테이블에 대한 오래된 데이터 정리를 수행합니다.
메모
설명서는 인덱스를 지칭할 때 B-트리라는 용어를 사용합니다. rowstore 인덱스에서 데이터베이스 엔진은 B+ 트리를 구현합니다. 이는 columnstore 인덱스나 메모리 최적화 테이블 인덱스에는 적용되지 않습니다. 자세한 내용은 SQL Server 및 Azure SQL 인덱스 아키텍처 및 디자인 가이드를 참조하세요.
B-트리 로우스토어 인덱스
행스토어 클러스터 인덱스는 해당 기간의 끝 SYSTEM_TIME 에 해당하는 열에서 시작해야 합니다. 만약 그런 인덱스가 존재하지 않는다면, 유한한 보존 기간을 설정할 수 없습니다:
Msg 13765, Level 16, State 1
Setting finite retention period failed on system-versioned temporal table
'dbo.WebsiteUserInfo' because the history table 'dbo.WebsiteUserInfoHistory'
does not contain required clustered index. Consider creating a clustered
columnstore or B-tree index starting with the column that matches end of
SYSTEM_TIME period, on the history table.
기본 히스토리 테이블에는 이미 준수하는 클러스터 인덱스가 있습니다. 유한한 보존 기간을 가진 히스토리 테이블에서 그 인덱스를 드롭하려 하면 다음과 같은 오류가 발생하여 연산이 실패합니다:
Msg 13766, Level 16, State 1
Cannot drop the clustered index 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory'
because it is being used for automatic cleanup of aged data. Consider setting HISTORY_RETENTION_PERIOD to INFINITE on the corresponding system-versioned
temporal table if you need to drop this index.
rowstore 클러스터 인덱스의 정리 논리는 노후된 행을 더 작은 단위(최대 10,000개)로 삭제하여 데이터베이스 로그와 I/O 서브시스템에 가해지는 부담을 최소화합니다. 정리 논리는 필요한 B-트리 인덱스를 사용하지만, 보존 기간보다 오래된 행의 삭제 순서를 보장할 수는 없습니다. 애플리케이션의 정리 순서에 의존하지 마세요.
클러스터형 columnstore 인덱스
클러스터 컬럼스토어의 정리 작업은 전체 행 그룹 을 한 번에 제거합니다. 각 행 그룹은 일반적으로 백만 개의 행을 포함합니다. 이 방법은 특히 작업 부하가 과거 데이터를 빠르게 생성할 때 더 효율적입니다.
데이터 압축과 보존 정리 덕분에 클러스터 컬럼스토어 인덱스는 작업량이 급격히 많은 과거 데이터를 생성하는 상황에서 좋은 선택입니다. 이러한 패턴은 변화 추적 및 감사, 추세 분석, 사물인터넷(IoT) 데이터 수집을 위해 시간 테이블을 사용하는 집중 트랜잭션 처리 워크로드 에서 흔히 나타납니다.
클러스터 컬럼스토어 인덱스의 정리는 과거 행이 오름차순(주기 끝 열에 따라 정렬됨)으로 도착할 때 최적으로 작동합니다. 이 조건은 오직 SYSTEM_VERSIONING 메커니즘만 히스토리 테이블을 채우는 경우에 항상 성립합니다. 히스토리 테이블의 행이 기간 끝 열에 따라 정렬되지 않는다면(기존 과거 데이터를 마이그레이션할 때 발생할 수 있음), 최적의 성능을 위해 제대로 정렬된 B-트리 로우스토어 인덱스 위에 클러스터 컬럼스토어 인덱스를 다시 생성하세요.
유한한 보존 기간이 있는 히스토리 테이블에서 클러스터 컬럼스토어 인덱스를 재구성하는 것은 피하세요. 재구성 시 시스템 버전 관리 연산이 자연스럽게 부과하는 행-그룹 순서가 바뀔 수 있기 때문입니다. 히스토리 테이블에서 클러스터 컬럼스토어 인덱스를 다시 작성해야 한다면, 정기적인 데이터 정리에 필요한 행 그룹 순서를 유지하기 위해 준수하는 B-트리 인덱스 위에 다시 생성하세요. 보장된 데이터 순서 없이 클러스터된 컬럼스토어 인덱스를 가진 기존 히스토리 테이블과 함께 시간 테이블을 만들 때도 같은 접근법을 적용하세요:
/* Create B-tree ordered by the end-of-period column */
CREATE CLUSTERED INDEX IX_WebsiteUserInfoHistory
ON WebsiteUserInfoHistory(ValidTo) WITH (DROP_EXISTING = ON);
GO
/* Re-create the clustered columnstore index */
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
ON WebsiteUserInfoHistory WITH (DROP_EXISTING = ON);
클러스터 컬럼스토어 인덱스를 가진 히스토리 테이블에 유한한 보존 기간을 설정할 때, 그 테이블에 추가로 비클러스터 B-트리 인덱스를 생성할 수 없습니다:
CREATE NONCLUSTERED INDEX IX_WebHistNCI
ON WebsiteUserInfoHistory(UserName);
이전 진술은 다음과 같은 오류로 실패합니다:
Msg 13772, Level 16, State 1
Cannot create non-clustered index on a temporal history table 'WebsiteUserInfoHistory' since it has finite retention period and clustered columnstore index defined.
보존 정책을 사용하여 테이블 쿼리
시간 테이블의 모든 쿼리는 유한 보존 정책에 맞는 과거 행을 자동으로 필터링하여 예측 불가능하고 일관성 없는 결과를 방지합니다. 정리 작업은 언제든지 임의의 순서로 오래된 행을 삭제합니다.
다음 스크린샷은 기본 쿼리의 쿼리 계획을 보여줍니다. 이 예에서는 MONTH 테이블의 1-WebsiteUserInfo 보존 기간을 가정합니다:
SELECT *
FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;
쿼리 계획에는 히스토리 테이블의 클러스터 인덱스 스캔 연산자(아래 이미지에서 강조됨)의 기간 말 열(ValidTo)에 추가 필터가 포함되어 있습니다.
히스토리 테이블을 직접 조회하면 지정된 보존 기간보다 오래된 행이 있을 수 있지만, 반복 가능한 쿼리 결과를 보장할 수는 없습니다. 다음 스크린샷은 추가 필터 없이 히스토리 테이블의 쿼리 계획을 보여줍니다:
보존 기간 이후로 히스토리 테이블을 읽는 비즈니스 로직에 의존하지 마세요. 일관성 없거나 예상치 못한 결과가 나올 수 있습니다. temporal 테이블의 데이터를 분석하려면 FOR SYSTEM_TIME 절과 함께 temporal 쿼리를 사용하세요.
지정 시점 복원 시 고려 사항
데이터베이스를 특정 시점으로 복원하면, 새 데이터베이스는 데이터베이스 수준에서 시간 보존이 비활성화됩니다(is_temporal_history_retention_enabled로 설정).OFF 이 동작 덕분에 정리 작업이 삭제되기 전에 보존 기간보다 오래된 과거 행을 검사할 수 있습니다. 복원된 데이터베이스에서 자동 정리를 다시 시작하려면 ON을(를) TEMPORAL_HISTORY_RETENTION(으)로 다시 설정하세요.
메모
Azure SQL Database의 프리미엄 티어로 생성된 데이터베이스는 최대 35일간 백업을 유지하므로, 그 기간 내 어느 시점으로든 복원할 수 있습니다. 보존 기간이 1개월인 시간 테이블의 경우, 복원된 데이터베이스에서 직접 히스토리 테이블을 조회하여 최대 65일 전의 과거 행을 검사할 수 있습니다.
테이블 파티셔닝 사용
분할된 테이블과 인덱스를 사용하면 대규모 테이블을 더 쉽게 관리하고 확장할 수 있습니다. 테이블 파티셔닝 방식을 사용하면 시간 조건에 기반한 맞춤형 데이터 정리나 오프라인 아카이브를 구현할 수 있습니다. 또한 테이블 분할을 통해 데이터 기록 하위 집합에서 temporal 테이블을 쿼리할 때 파티션 제거를 사용하여 성능상 이점도 얻게 됩니다.
테이블 파티셔닝을 사용하여 슬라이딩 윈도우를 구현하여 히스토리 테이블에서 가장 오래된 데이터 부분을 빼내고, 보존된 부분의 크기를 나이에 따라 일정하게 유지하세요. 슬라이딩 윈도우는 필요한 보존 기간과 동일한 데이터를 히스토리 테이블에 유지합니다. 히스토리 테이블은 ON가 SYSTEM_VERSIONING인 동안에도 데이터를 전환할 수 있으므로, 유지 관리 시간을 두거나 일반적인 워크로드를 중단하지 않고도 히스토리 데이터의 일부를 정리할 수 있습니다.
메모
파티션 전환을 수행하려면, 히스토리 테이블의 클러스터 인덱스가 파티셔닝 스키마와 정렬되어야 하며(반드시 포함 ValidTo해야 합니다) 기본 히스토리 테이블은 와 ValidFrom 컬럼을 포함하는 ValidTo 클러스터 인덱스를 포함하고 있어, 분할, 새로운 히스토리 데이터 삽입, 일반적인 시간 쿼리에 최적화되어 있습니다. 자세한 내용은 Temporal 테이블을 참조하세요.
슬라이딩 윈도우는 두 가지 작업을 필요로 합니다:
- 분할 구성 작업
- 파티션 정기 유지 관리 작업
이 예시를 위해 6개월 동안 과거 데이터를 보관하고, 매달 별도의 파티션에 보관하고 싶다고 가정해 봅시다. 또한, 2023년 9월에 시스템 버전 관리(system-versioning)를 활성화했다고 가정하세요.
분할 구성 작업은 기록 테이블에 대한 초기 분할 구성을 만듭니다. 이 예시에서는 슬라이딩 윈도우 크기와 같은 수의 파티션을 몇 달 단위로 만들고, 추가로 빈 파티션 하나를 추가합니다. 이 구성은 반복 파티션 유지보수 작업을 처음 시작할 때 시스템이 새 데이터를 올바르게 저장할 수 있도록 보장합니다. 또한 데이터가 포함된 파티션을 분할하지 않도록 보장해 비용이 많이 드는 데이터 이동을 방지할 수 있습니다. 분할 함수를 RANGE RIGHT가 아니라 RANGE LEFT로 정의합니다. 자세한 내용은 이 글 후반부의 테이블 파티셔닝 성능 고려 사항을 참조하세요.
다음 사진은 6개월치 데이터를 유지하기 위한 초기 분할 구성을 보여줍니다.
첫 번째와 마지막 파티션은 각각 하부 경계와 상단 경계에 열려 있어, 분할 열의 값과 상관없이 모든 새 행이 목적지 파티션을 갖도록 보장합니다. 시간이 지나면서 히스토리 테이블의 새로운 행은 더 높은 파티션에 위치하게 됩니다. 여섯 번째 파티션이 가득 차면 목표 보존 기간에 도달합니다. 이 시점에서 처음으로 반복되는 파티션 유지 관리를 시작하세요. 이 예시에서는 한 달에 한 번씩 주기적으로 실행되도록 스케줄을 설정하세요.
다음 사진은 반복되는 파티션 유지 관리 작업을 보여줍니다.
반복되는 유지보수 작업의 각 실행 단계는 다음과 같습니다:
SWITCH OUT: 스테이징 테이블을 생성한 후, 인자가 포함된SWITCH PARTITION문장을 사용하여 ALTER TABLE 히스토리 테이블과 스테이징 테이블 간의 파티션을 전환합니다.ALTER TABLE [<history table>] SWITCH PARTITION 1 TO [<staging table>];파티션 스위치 후에는 스테이징 테이블에서 데이터를 아카이브한 뒤, 다음 유지보수 사이클을 준비하기 위해 스테이징 테이블을 삭제하거나 잘라낼 수 있습니다.
MERGE RANGE: ALTER PARTITION FUNCTION 문을MERGE RANGE와 함께 사용하여 빈 파티션1을 파티션2와 병합하십시오. 이 함수를 사용해 가장 낮은 경계를 제거하면, 빈 파티션1과 이전 파티션2을 사실상 병합하여 새로운 파티션1을 형성합니다. 다른 파티션들도 서수(순서)를 효과적으로 변경합니다.SPLIT RANGE:7와 함께SPLIT RANGE문을 사용하여 새로운 빈 분할 ALTER PARTITION FUNCTION을 생성합니다. 이 함수를 사용해 새로운 상한선을 추가하면, 다음 달을 위한 별도의 파티션을 사실상 생성하게 됩니다.
Transact-SQL을 사용하여 기록 테이블에 파티션 만들기
다음 Transact-SQL 스크립트를 사용하여 파티션 함수, 파티션 스키마를 만들고, 스키마와 파티션 정렬된 클러스터 인덱스를 다시 생성합니다. 이 예제에서는 2023년 9월부터 시작하는 월별 파티션을 사용하여 6개월의 슬라이딩 윈도우를 만듭니다.
BEGIN TRANSACTION;
/*Create partition function*/
CREATE PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo](DATETIME2 (7))
AS RANGE LEFT FOR VALUES (
N'2023-09-30T23:59:59.999',
N'2023-10-31T23:59:59.999',
N'2023-11-30T23:59:59.999',
N'2023-12-31T23:59:59.999',
N'2024-01-31T23:59:59.999',
N'2024-02-29T23:59:59.999'
);
/*Create partition scheme*/
CREATE PARTITION SCHEME [sch_Partition_DepartmentHistory_By_ValidTo]
AS PARTITION [fn_Partition_DepartmentHistory_By_ValidTo]
TO (
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY]
);
/*Re-create index to be partition-aligned with the partitioning schema*/
CREATE CLUSTERED INDEX [ix_DepartmentHistory] ON [dbo].[DepartmentHistory] (
ValidTo ASC,
ValidFrom ASC
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = ON,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
DATA_COMPRESSION = PAGE
)
ON [sch_Partition_DepartmentHistory_By_ValidTo] (ValidTo);
COMMIT TRANSACTION;
Transact-SQL을 사용하여 슬라이딩 윈도우 시나리오에서 파티션 유지 관리
다음 Transact-SQL 스크립트를 사용하여 슬라이딩 윈도우 시나리오에서 파티션 유지 관리합니다. 이 예제에서는 MERGE RANGE를 사용하여 2023년 9월 파티션을 교체하고, SPLIT RANGE를 사용하여 2024년 3월의 새 파티션을 추가합니다.
BEGIN TRANSACTION;
/* (1) Create staging table */
CREATE TABLE [dbo].[staging_DepartmentHistory_September_2023]
(
DeptID INT NOT NULL,
DeptName VARCHAR (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
ManagerID INT NULL,
ParentDeptID INT NULL,
ValidFrom DATETIME2 (7) NOT NULL,
ValidTo DATETIME2 (7) NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);
/* (2) Create index on the same filegroups as the partition to switch out */
CREATE CLUSTERED INDEX [ix_staging_DepartmentHistory_September_2023]
ON [dbo].[staging_DepartmentHistory_September_2023](
ValidTo ASC,
ValidFrom ASC
)
WITH (
PAD_INDEX = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
ON [PRIMARY];
/* (3) Create constraints matching the partition to switch out */
ALTER TABLE [dbo].[staging_DepartmentHistory_September_2023] WITH CHECK
ADD CONSTRAINT [chk_staging_DepartmentHistory_September_2023_partition_1]
CHECK (ValidTo <= N'2023-09-30T23:59:59.999');
ALTER TABLE [dbo].[staging_DepartmentHistory_September_2023]
CHECK CONSTRAINT [chk_staging_DepartmentHistory_September_2023_partition_1];
/* (4) Switch partition to staging table */
ALTER TABLE [dbo].[DepartmentHistory]
SWITCH PARTITION 1 TO [dbo].[staging_DepartmentHistory_September_2023]
WITH (
WAIT_AT_LOW_PRIORITY (
MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE
)
);
/* (5) [Commented out] Optionally archive the data and drop staging table
INSERT INTO [ArchiveDB].[dbo].[DepartmentHistory]
SELECT * FROM [dbo].[staging_DepartmentHistory_September_2023];
DROP TABLE [dbo].[staging_DepartmentHIstory_September_2023];
*/
/* (6) merge range to move lower boundary one month ahead */
ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo]()
MERGE RANGE (N'2023-09-30T23:59:59.999');
/* (7) Create new empty partition for "April and after"
by creating new boundary point and specifying NEXT USED file group*/
ALTER PARTITION SCHEME [sch_Partition_DepartmentHistory_By_ValidTo]
NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo]()
SPLIT RANGE (N'2024-03-31T23:59:59.999');
COMMIT TRANSACTION;
하지만 최적의 해결책은 매달 수정 없이 일반 Transact-SQL 스크립트를 정기적으로 실행하는 것입니다. 이전 스크립트를 제공한 매개변수(병합해야 하는 하부 경계와 파티션 분할로 생성된 새로운 경계)에 대해 일반화할 수 있습니다. 매달 스테이징 테이블을 만들지 않으려면, 미리 하나 만들어서 체크 제약 조건을 바꿔서 교체하는 파티션에 맞게 재사용하세요. 자세한 내용은 슬 라이딩 윈도우 시나리오를 완전 자동화하는 방법을 참고하세요.
테이블 분할과 관련된 성능 고려 사항
데이터 이동은 상당한 성능 오버헤드를 초래할 수 있으므로, 데이터 이동을 피하는 방식으로 MERGE RANGE 및 SPLIT RANGE 작업을 수행하세요. 자세한 내용은 파티션 함수 수정을 참조하세요.
분할 함수RANGE LEFT를 로 만들면, 지정된 값은 분할의 상한 경계입니다.
RANGE RIGHT를 사용할 때 지정된 값은 파티션의 하한입니다.
MERGE RANGE 작업을 사용하여 파티션 함수 정의에서 경계를 제거할 경우 기본적으로 경계가 포함된 파티션도 제거되도록 구현됩니다. 그 파티션이 비어 있지 않으면, MERGE RANGE 데이터를 생성된 파티션으로 옮깁니다.
다음 다이어그램에서는 RANGE LEFT 옵션 및 RANGE RIGHT 옵션을 설명합니다.
슬라이딩 윈도우 시나리오에서는 항상 하한 파티션 경계를 제거합니다.
RANGE LEFT경우: 가장 낮은 파티션 경계는 파티션1에 속하며, 파티션 교체MERGE RANGE후에는 비어 있어 데이터 이동을 일으키지 않습니다.RANGE RIGHT경우: 가장 낮은 파티션 경계는 파티션2에 속하며, 스위치 아웃할 때 비게 되는 것은 파티션1뿐이므로 이 파티션은 비어 있지 않습니다. 이 경우 는MERGE RANGE데이터 이동을 일으켜 데이터를 파티션21간 파티션 간 이동시킵니다. 이러한 데이터 이동RANGE RIGHT을 피하기 위해 슬라이딩 윈도우 시나리오에서는 파티션1를 가져야 하며, 이 파티션은 항상 비어 있어야 합니다. 이 요구사항은RANGE RIGHT를 사용하는 경우RANGE LEFT를 사용하는 경우보다 파티션을 하나 더 생성하고 유지해야 함을 의미합니다.
결론: 슬라이딩 파티션에서 파티션 RANGE LEFT 관리를 하면 더 쉬워지고, 데이터 이동을 피할 수 있습니다. 그러나 날짜 및 시간 검사 문제를 처리할 필요가 없으므로 RANGE RIGHT로 파티션 경계를 정의하는 것이 약간 더 쉽습니다.
커스텀 정리 스크립트를 사용하세요
테이블에 보존 정책이 없고 테이블 파티셔닝이 불가능할 때, 사용자 지정 정리 스크립트를 사용해 히스토리 테이블에서 데이터를 삭제할 수 있습니다. 이 프로세스는 SYSTEM_VERSIONING = OFF인 경우에만 가능합니다. 데이터 불일치를 피하려면 유지보수 창(데이터를 수정하는 워크로드가 활성화되지 않은 상태)이나 트랜잭션 내(사실상 다른 워크로드를 차단하는 경우) 중 정리를 수행하세요. 이 작업을 수행하려면 현재 및 기록 테이블에 대한 CONTROL 권한이 필요합니다.
정리 로직은 모든 시간 테이블에 동일하므로 일반적인 저장 프로시저를 통해 자동화할 수 있습니다. SQL Server 에이전트나 다른 도구를 사용해 해당 절차를 매일 실행하도록 스케줄링하고, 데이터 이력을 제한하려는 모든 시간 테이블을 반복 재생하세요.
다음 다이어그램은 실행 중인 워크로드에 미치는 영향을 줄이기 위해 단일 테이블에 대한 정리 로직을 어떻게 조직하는지 보여줍니다.
다음은 이 과정을 실행하기 위한 주요 지침입니다:
모든 시간 테이블의 과거 데이터를 여러 차례 작은 단위로 삭제하세요. 가장 오래된 줄부터 시작해서 가장 최근 줄로 이동하세요. 이전 도표에서 보인 것처럼 단일 트랜잭션에서 모든 행을 삭제하는 것은 피하세요. 모든 상황에 단일 청크 크기가 적용되는 것은 아니지만, 한 번의 거래에서 10,000행 이상을 삭제하면 상당한 페널티가 부과될 수 있습니다.
모든 반복을 일반 저장 프로시저의 호출로 구현하여 히스토리 테이블에서 일부 데이터를 제거합니다.
프로세스를 호출할 때마다 개별 temporal 테이블에 대해 삭제해야 하는 행 수를 계산합니다. 결과와 원하는 반복 횟수에 따라 각 프로시저 호출에 대한 동적 분할점을 결정하세요.
단일 테이블에 대해 반복 간 지연을 계획하여 시간 테이블에 접근하는 애플리케이션에 미치는 영향을 줄이세요.
다음 저장 프로시저는 단일 시간 테이블의 데이터를 삭제합니다. 카탈로그 뷰에서 히스토리 테이블과 기간 종료 열을 발견한 후, 트랜잭션 내에서 세 개의 문장을 실행합니다: SET SYSTEM_VERSIONING = OFF, DELETE FROM <history_table>, .SET SYSTEM_VERSIONING = ON 이 코드를 꼼꼼히 검토하고 환경에 적용하기 전에 수정하세요.
SQL Server 2016(13.x)에서 처음 두 단계는 별도의 EXECUTE 문에서 실행해야 합니다. 그렇지 않으면 SQL Server에서 다음 예제와 유사한 오류를 생성합니다.
Msg 13560, Level 16, State 1, Line XXX
Cannot delete rows from a temporal history table '<database_name>.<history_table_schema_name>.<history_table_name>'.
DROP PROCEDURE IF EXISTS usp_CleanupHistoryData;
GO
CREATE PROCEDURE usp_CleanupHistoryData (
@temporalTableSchema SYSNAME,
@temporalTableName SYSNAME,
@cleanupOlderThanDate DATETIME2
)
AS
DECLARE @disableVersioningScript AS NVARCHAR (MAX) = '';
DECLARE @deleteHistoryDataScript AS NVARCHAR (MAX) = '';
DECLARE @enableVersioningScript AS NVARCHAR (MAX) = '';
DECLARE @historyTableName AS SYSNAME;
DECLARE @historyTableSchema AS SYSNAME;
DECLARE @periodColumnName AS SYSNAME;
/* Generate script to discover history table name and
end of period column for given temporal table name */
EXECUTE sp_executesql N'
SELECT @hst_tbl_nm = t2.name,
@hst_sch_nm = s2.name,
@period_col_nm = c.name
FROM sys.tables AS t1
INNER JOIN sys.tables AS t2
ON t1.history_table_id = t2.object_id
INNER JOIN sys.schemas AS s1
ON t1.schema_id = s1.schema_id
INNER JOIN sys.schemas AS s2
ON t2.schema_id = s2.schema_id
INNER JOIN sys.periods AS p
ON p.object_id = t1.object_id
INNER JOIN sys.columns AS c
ON p.end_column_id = c.column_id
AND c.object_id = t1.object_id
WHERE t1.name = @tblName
AND s1.name = @schName',
N'@tblName sysname,
@schName sysname,
@hst_tbl_nm sysname OUTPUT,
@hst_sch_nm sysname OUTPUT,
@period_col_nm sysname OUTPUT',
@tblName = @temporalTableName,
@schName = @temporalTableSchema,
@hst_tbl_nm = @historyTableName OUTPUT,
@hst_sch_nm = @historyTableSchema OUTPUT,
@period_col_nm = @periodColumnName OUTPUT;
IF @historyTableName IS NULL
OR @historyTableSchema IS NULL
OR @periodColumnName IS NULL
THROW 50010, 'History table cannot be found. Either specified table is not system-versioned temporal or you have provided incorrect argument values.', 1;
SET @disableVersioningScript = @disableVersioningScript +
'ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + ']
SET (SYSTEM_VERSIONING = OFF)';
SET @deleteHistoryDataScript = @deleteHistoryDataScript +
' DELETE FROM [' + @historyTableSchema + '].[' + @historyTableName + ']
WHERE [' + @periodColumnName + '] < ' + '''' +
CONVERT (VARCHAR (128), @cleanupOlderThanDate, 126) + '''';
SET @enableVersioningScript = @enableVersioningScript +
' ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + ']
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + '].[' +
@historyTableName + '], DATA_CONSISTENCY_CHECK = OFF )); ';
BEGIN TRANSACTION;
EXECUTE (@disableVersioningScript);
EXECUTE (@deleteHistoryDataScript);
EXECUTE (@enableVersioningScript);
COMMIT TRANSACTION;