보존 정책을 사용하여 임시 테이블에서 과거 데이터 관리

아티클
03/22/2024

적용 대상: Azure SQL Database Azure SQL Managed Instance

임시 테이블은 특히 더 오랜 기간 기록 데이터를 유지하는 경우 일반 테이블보다 데이터베이스 크기를 늘릴 수 있습니다. 따라서 기록 데이터에 대한 보존 정책은 모든 임시 테이블의 수명 주기를 계획하고 관리할 때 중요합니다. Azure SQL Database 및 Azure SQL Managed Instance의 임시 테이블에서는 이 작업을 수행하는 데 도움이 되는 사용하기 쉬운 보존 메커니즘이 함께 제공됩니다.

Temporal 기록 보존은 개별 테이블 수준에서 구성할 수 있으므로 사용자가 유연한 에이징 정책을 만들 수 있습니다. 임시 보존 적용은 간단합니다. 테이블 생성 또는 스키마 변경 중에 매개 변수를 하나만 설정하면 됩니다.

보존 정책을 정의한 후에는 Azure SQL Database 및 Azure SQL Managed Instance가 자동으로 데이터 정리에 적합한 기록 행이 있는지 정기적으로 확인하기 시작합니다. 일치하는 행을 식별하고 기록 테이블에서 제거하는 작업은 시스템에서 예약하고 실행하는 백그라운드 태스크로 투명하게 수행됩니다. 기록 테이블 행의 기간 조건은 SYSTEM_TIME 기간의 종료를 나타내는 열을 기준으로 확인됩니다. 예를 들어 보존 기간이 6개월로 설정된 경우 정리할 수 있는 테이블 행은 다음 조건을 만족합니다.

ValidTo < DATEADD (MONTH, -6, SYSUTCDATETIME())

앞의 예제에서는 ValidTo 열이 SYSTEM_TIME 기간의 끝에 해당한다고 가정했습니다.

보존 정책을 구성하는 방법

임시 테이블에 대한 재방문 주기 정책을 구성하기 전에 먼저 임시 기록 재방문 주기를 데이터베이스 수준에서 사용하도록 설정할지 확인합니다.

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases

데이터베이스 플래그 is_temporal_history_retention_enabled는 기본값으로 ON으로 설정되지만 사용자는 ALTER DATABASE 문을 사용하여 변경할 수 있습니다. 또한 이 플래그는 특정 시점 복원 작업 후에 자동적으로 OFF로 설정됩니다. 데이터베이스에서 임시 기록 보존 정리를 사용하도록 설정하려면 다음 명령문을 실행합니다.

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Important

is_temporal_history_retention_enabled OFF인 경우에도 임시 테이블에 대한 보존을 구성할 수 있지만 이 경우 오래된 행에서 자동 정리는 트리거되지 않습니다.

보존 정책은 테이블을 만들 때 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
     )
 );

Azure SQL Database 및 Azure SQL Managed Instance를 통해 다른 시간 단위인 DAYS, WEEKS, MONTHS, YEARS를 사용하여 보존 기간을 지정할 수 있습니다. HISTORY_RETENTION_PERIOD를 생략하면 INFINITE 보존으로 간주됩니다. INFINITE 키워드(keyword)도 명시적으로 사용할 수 있습니다.

일부 시나리오에서는 테이블 생성 후 보존을 구성하거나 이전에 구성된 값을 변경할 수 있습니다. 이 경우 ALTER TABLE 문을 사용합니다.

ALTER TABLE dbo.WebsiteUserInfo
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));

Important

SYSTEM_VERSIONING을 OFF로 설정해도 보존 기간 값은 유지되지 않습니다. 명시적으로 HISTORY_RETENTION_PERIOD를 지정하지 않고 SYSTEM_VERSIONING을 ON으로 설정하면 INFINITE 보존 기간이 적용됩니다.

보존 정책의 현재 상태를 살펴보려면 데이터베이스 수준에서 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 T1  
OUTER APPLY (select is_temporal_history_retention_enabled from sys.databases
where name = DB_NAME()) AS DB
LEFT JOIN sys.tables T2
ON T1.history_table_id = T2.object_id WHERE T1.temporal_type = 2

오래된 행을 삭제하는 방법

정리 프로세스는 기록 테이블의 인덱스 레이아웃에 따라 달라집니다. 클러스터형 인덱스(B-트리 또는 columnstore)를 사용하는 기록 테이블에만 유한 보존 정책을 구성할 수 있다는 점을 기억해야 합니다. 보존 기간이 한정된 모든 temporal 테이블에 대한 오래된 데이터 정리를 수행하는 백그라운드 작업이 만들어집니다. rowstore(B-트리) 클러스터형 인덱스에 대한 정리 논리에서는 더 작은 청크(최대 10K)의 오래된 행을 삭제하여 데이터베이스 로그 및 IO 하위 시스템에 주는 부담을 최소화합니다. 정리 논리에서 필요한 B-트리 인덱스를 활용하긴 하지만 보존 기간보다 오래된 행의 삭제 순서는 확실히 보장할 수 없습니다. 따라서 애플리케이션의 정리 순서에 의존하지 마세요.

클러스터형 columnstore 인덱스에 대한 정리 작업은 전체 행 그룹을 한 번에 제거하므로(일반적으로 각 그룹에 1백만 개 행 포함) 특히 기록 데이터가 빠른 속도로 생성되는 경우에 매우 효율적입니다.

클러스터형 columnstore 보존

탁월한 데이터 압축 및 효율적인 보존 정리 덕분에 클러스터형 columnstore 인덱스는 워크로드에서 많은 양의 기록 데이터가 빠르게 생성되는 시나리오에 완벽한 선택입니다. 해당 패턴은 변경 내용 추적 및 감사, 추세 분석 또는 IoT 데이터 수집에 대해 임시 테이블을 사용하는 집약적 트랜잭션 처리 워크로드에 일반적입니다.

인덱스 고려 사항

Rowstore 클러스터형 인덱스가 있는 테이블에 대한 정리 작업은 SYSTEM_TIME 기간의 끝에 해당하는 열로 시작하는 인덱스를 필요로 합니다. 이러한 인덱스가 없는 경우 유한 보존 기간을 구성할 수 없습니다.

메시지 13765, 수준 16, 상태 1

한정된 보존 범위 설정은 'temporalstagetestdb.dbo.WebsiteUserInfoHistory' 기록 테이블에 필수 클러스터형 인덱스가 없기 때문에 'temporalstagetestdb.dbo.WebsiteUserInfo’ 시스템 버전 임시 테이블에서 실패했습니다. 기록 테이블에서 SYSTEM_TIME 기간의 끝과 일치하는 열로 시작하는 클러스터형 columnstore 또는 B-트리 인덱스 생성을 고려합니다.

Azure SQL Database 및 Azure SQL Managed Instance에서 생성한 기본 기록 테이블에 보존 정책과 호환되는 클러스터형 인덱스가 이미 있다는 점에 유의해야 합니다. 보존 기간이 유한한 테이블에서 해당 인덱스를 제거하려고 하면 다음 오류와 함께 작업이 실패합니다.

메시지 13766, 수준 16, 상태 1

'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory' 클러스터형 인덱스가 오래된 데이터의 자동 정리를 위해 사용되고 있으므로 삭제할 수 없습니다. 이 인덱스를 삭제해야 할 경우 해당 시스템 버전 임시 테이블에서 HISTORY_RETENTION_PERIOD를 INFINITE로 설정하는 것이 좋습니다.

기록 행이 오름차순(기간 열의 끝순으로 정렬됨)에 삽입되는 경우 클러스터형 columnstore 인덱스에 대한 정리가 최적으로 작동합니다. 항상 기록 테이블이 SYSTEM_VERSIONIOING 메커니즘에 의해 단독으로 채워지는 경우에 해당합니다. 기록 테이블의 행이 기간 열 끝순으로 정렬되지 않은 경우(기존 기록 데이터를 마이그레이션한 경우일 수 있음) 최적의 성능을 얻기 위해 제대로 정렬된 B-트리 rowstore 인덱스 위에 클러스터형 columnstore 인덱스를 다시 생성해야 합니다.

유한 보존 기간을 사용하여 기록 테이블에서 클러스터형 columnstore 인덱스가 다시 작성되지 않도록 합니다. 시스템 버전 관리 작업에 의해 자연스럽게 적용되는 행 그룹의 순서가 변경될 수 있기 때문입니다. 기록 테이블의 클러스터형 columnstore 인덱스를 다시 작성해야 할 경우 호환성 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 clustered columnstore index*/
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory ON WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

클러스터형 columnstore 인덱스가 있는 기록 테이블에 대해 유한 보존 기간이 구성된 경우 해당 테이블에서 비클러스터형 B-트리 인덱스를 추가로 생성할 수 없습니다.

CREATE NONCLUSTERED INDEX IX_WebHistNCI ON WebsiteUserInfoHistory ([UserName])

위의 문을 실행하면 다음 오류와 함께 실패합니다.

메시지 13772, 수준 16, 상태 1

임시 기록 테이블인 'WebsiteUserInfoHistory'에는 한정된 보존 범위 및 클러스터형 columnstore 인덱스가 정의되어 있기 때문에 비클러스터형 인덱스를 생성할 수 없습니다.

보존 정책을 사용하여 테이블 쿼리

임시 테이블의 모든 쿼리는 예측 불가능하고 일관되지 않은 결과를 방지하기 위해 유한 보존 정책과 일치하는 기록 행을 자동으로 필터링합니다. 정리 작업에서 언제든지 임의의 순서로 오래된 행을 삭제할 수 있기 때문입니다.

다음 그림에서는 간단한 쿼리에 대한 쿼리 계획을 보여줍니다.

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

쿼리 계획에는 기록 테이블(강조 표시됨)에 있는 클러스터형 인덱스 스캔 연산자의 기간 열 끝(ValidTo)에 적용되는 추가 필터가 포함됩니다. 이 예제에서는 WebsiteUserInfo 테이블에서 1개월의 보존 기간이 설정되었다고 가정합니다.

재방문 주기 쿼리 필터

그러나 기록 테이블을 직접 쿼리하는 경우 지정된 보존 기간보다 오래되었지만 반복 가능한 쿼리 결과를 보장하지 않는 행이 표시될 수 있습니다. 다음 그림은 추가 필터를 적용하지 않고 기록 테이블에 대한 쿼리 계획 실행을 보여줍니다.

재방문 주기 필터를 사용하지 않은 기록 쿼리

일관되지 않거나 예기치 않은 결과가 발생할 수 있으므로 보존 기간 이후의 기록 테이블을 읽는 데 비즈니스 논리를 사용하지 마세요. 임시 테이블의 데이터를 분석하기 위해 FOR SYSTEM_TIME 절과 함께 임시 쿼리를 사용하는 것이 좋습니다.

특정 시점 복원 고려 사항

기존 데이터베이스를 특정 시점으로 복원하여 새 데이터베이스를 생성하면 데이터베이스 수준에서 임시 보존이 사용되지 않습니다. (is_temporal_history_retention_enabled 플래그 OFF로 설정). 이 기능을 사용하면 쿼리하기 전에 오래된 행이 제거될 염려 없이 복원 시 모든 기록 행을 검사할 수 있습니다. 이 데이터를 사용하여 구성된 보존 기간 이후의 기록 데이터를 검사할 수 있습니다.

임시 테이블의 재방문 주기가 1 MONTH로 지정되었다고 가정해 봅시다. 프리미엄 서비스 계층에서 데이터베이스를 생성한 경우 최대 35일 전 상태의 데이터베이스로 데이터베이스 복사본을 생성할 수 있습니다. 이를 효과적으로 사용하면 기록 테이블을 직접 쿼리하여 최대 65일 전의 기록 행을 분석할 수 있습니다.

임시 보존 정리를 활성화하려면 특정 시점 복원 후에 다음 Transact-SQL 문을 실행합니다.

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

다음 단계

애플리케이션에서 임시 테이블을 사용하는 방법을 알아보려면 임시 테이블 시작을 참조하세요.

임시 테이블에 대한 자세한 내용은 임시 테이블을 검토하세요.

다음을 통해 공유