시스템 버전 관리된 temporal 테이블에서 기록 데이터의 보존 관리

아티클
03/22/2024

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL Database Azure SQL Managed Instance

기록 테이블에서는 시스템 버전 관리된 temporal 테이블로 특히 다음과 같은 조건에서 일반 테이블보다 데이터베이스 크기를 늘릴 수 있습니다.

기록 데이터를 오랫동안 보관
대량의 데이터를 업데이트 및 삭제하는 수정 패턴이 있는 경우

대규모의 계속 증가하는 기록 테이블은 순수한 스토리지 비용과 temporal 쿼리에 대한 성능 세금 부과로 인해 문제가 될 수 있습니다. 따라서 기록 테이블에서 데이터를 관리하기 위한 데이터 보존 정책을 개발하는 것은 모든 temporal 테이블의 수명 주기를 계획하고 관리하는 중요한 측면입니다.

기록 테이블에 대한 데이터 보존 관리

temporal 테이블 데이터 보존 관리는 각 temporal 테이블에 대한 필수 보존 기간을 결정하는 것부터 시작됩니다. 대부분의 경우 보존 정책은 temporal 테이블을 사용하는 애플리케이션의 비즈니스 논리의 일부로 간주되어야 합니다. 예를 들어 데이터 감사 및 시간 이동 시나리오의 애플리케이션에는 온라인 쿼리에 기록 데이터를 사용할 수 있어야 하는 기간에 대한 확고한 요구 사항이 있습니다.

데이터 보존 기간을 결정하면 다음 단계는 기록 데이터를 관리하기 위한 계획을 세우는 것입니다. 기록 데이터를 저장하는 방법 및 위치와 보존 요구 사항보다 오래된 기록 데이터를 삭제하는 방법을 결정해야 합니다. 다음 방법으로 temporal 기록 테이블에서 기록 데이터를 관리할 수 있습니다.

테이블 분할
사용자 지정 정리 스크립트
보존 정책

이러한 각 방법을 사용하면 기록 데이터를 마이그레이션하거나 정리하기 위한 논리가 현재 테이블의 기간 종료에 해당하는 열을 기반으로 합니다. 각 행의 기간 종료 값에 따라 행 버전이 종료되는 순간, 즉 기록 테이블에 저장되는 순간이 결정됩니다. 예를 들어 ValidTo < DATEADD (DAYS, -30, SYSUTCDATETIME ()) 조건은 1개월보다 오래된 기록 데이터를 제거하거나 기록 테이블에서 이동해야 함을 지정합니다.

참고 항목

이 문서의 예제에서는 이 시스템 버전 관리된 temporal 테이블 만들기를 사용합니다.

테이블 분할 방법 사용

분할된 테이블과 인덱스를 사용하면 대규모 테이블을 더 쉽게 관리하고 확장할 수 있습니다. 테이블 분할 방법을 통해 기록 테이블 파티션을 사용하여 시간 조건에 따라 사용자 지정 데이터 정리 또는 오프라인 보관을 구현할 수 있습니다. 또한 테이블 분할을 통해 데이터 기록 하위 집합에서 temporal 테이블을 쿼리할 때 파티션 제거를 사용하여 성능상 이점도 얻게 됩니다.

테이블 분할을 사용하면 기록 테이블에서 기록 데이터의 가장 오래된 부분을 이동하고 보존된 부분의 크기를 보존 기간과 동일하게 유지하도록 슬라이딩 윈도우를 구현할 수 있습니다. 즉, 기록 테이블의 데이터를 필요한 보존 기간과 동일하게 유지할 수 있습니다. 기록 테이블에서 데이터를 전환하는 작업은 지원되지만 SYSTEM_VERSIONING은 ON입니다. 이는 유지 관리 기간을 도입하거나 일반 워크로드를 차단하지 않고도 기록 데이터의 일부를 정리할 수 있음을 의미합니다.

참고 항목

파티션 전환 작업을 수행하려면 기록 테이블에서 클러스터형 인덱스가 분할 스키마와 정렬되어야 합니다(ValidTo 포함 필수). 시스템에서 만든 기본 기록 테이블에는 ValidTo 및 ValidFrom 열이 포함된 클러스터형 인덱스가 있어 새 기록 데이터 분할, 삽입, 일반적인 임시 쿼리 작업에 적합합니다. 자세한 내용은 Temporal 테이블을 참조하세요.

슬라이딩 윈도우에는 수행해야 하는 두 가지 작업 집합이 있습니다.

분할 구성 작업
파티션 정기 유지 관리 작업

이 그림에서는 기록 데이터를 6개월 동안 유지하고 매월 데이터를 별도의 파티션에 유지한다고 가정해 보겠습니다. 또한 2023년 9월에 시스템 버전 관리를 활성화했다고 가정해 보겠습니다.

분할 구성 작업은 기록 테이블에 대한 초기 분할 구성을 만듭니다. 이 예제의 경우 월별로 슬라이딩 윈도우 크기와 동일한 개수의 파티션과 미리 준비된 추가 빈 파티션을 만듭니다(이 문서의 뒷부분에 설명). 이 구성을 통해 처음으로 반복적인 파티션 유지 관리 작업을 시작할 때 시스템에서 새 데이터를 제대로 저장할 수 있게 되며, 파티션을 데이터와 분할하지 않아 비용이 많이 드는 데이터 이동을 방지할 수 있습니다. 이 작업은 이 문서의 뒷부분에 나와 있는 예제 스크립트로 TRANSACT-SQL을 사용하여 수행해야 합니다.

다음 그림에서는 6개월 동안 데이터를 유지하기 위한 초기 분할 구성을 보여 줍니다.

참고 항목

분할 구성 시 RANGE LEFT 및 RANGE RIGHT를 사용할 때 성능에 미치는 영향에 대한 자세한 내용은 이 문서 뒷부분의 테이블 분할 시 성능 고려 사항을 참조하세요.

분할 열의 값에 관계없이 모든 새 행에 대상 파티션이 있는지 확인하기 위해 첫 번째 및 마지막 파티션은 각각 하한 및 상한 경계에서 열려 있습니다. 시간이 지남에 따라 기록 테이블의 새 행은 더 높은 파티션에 배치됩니다. 여섯 번째 파티션이 채워지면 대상 보존 기간에 도달합니다. 이것은 처음으로 파티션 정기 유지 관리 작업을 시작하는 시점입니다(이 예제에서는 한 달에 한 번씩 정기적으로 실행되도록 예약해야 함).

다음 그림에서는 파티션 정기 유지 관리 작업을 보여 줍니다(이 섹션의 뒷부분에 있는 자세한 단계 참조).

파티션 정기 유지 관리 작업에 대한 자세한 단계는 다음과 같습니다.

전환: 준비 테이블을 만든 다음, SWITCH PARTITION 인수와 함께 ALTER TABLE(Transact-SQL) 문을 사용하여 기록 테이블과 준비 테이블 사이에서 파티션을 전환합니다(예제 C. 테이블 간 파티션 전환 참조).
```
ALTER TABLE [<history table>] SWITCH PARTITION 1 TO [<staging table>];
```
파티션 전환 후 필요에 따라 준비 테이블에서 데이터를 보관한 다음, 준비 테이블을 삭제하거나 잘라내어 다음에 이 파티션 정기 유지 관리 작업을 수행해야 할 때를 준비할 수 있습니다.
MERGE RANGE: 1와 함께 ALTER PARTITION FUNCTION(Transact-SQL)을 빈 파티션 2를 파티션 MERGE RANGE와 병합합니다(예제 B 참조). 이 함수를 사용하여 하한 경계를 제거하면 빈 파티션 1을 이전 파티션 2와 효과적으로 병합하여 새 파티션 1을 형성합니다. 다른 파티션도 효과적으로 서수를 변경합니다.
SPLIT RANGE: SPLIT RANGE: SPLIT RANGE와 함께 ALTER PARTITION FUNCTION(Transact-SQL)을 사용하여 새 빈 파티션 7을 만듭니다(예제 A 참조). 이 함수를 사용하여 새 상한을 추가하면 다음 달에 별도의 파티션을 효과적으로 만듭니다.

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 that will be switched 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 that will be switched 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

이전 스크립트를 약간 수정하고 사용해 정기적인 월별 유지 관리 프로세스에서 사용할 수 있습니다.

단계 (1)에서는 제거할 달에 대한 새 준비 테이블을 만듭니다(이 예에서 10월이 그다음 달에 해당).
단계 (3)에서는 제거할 데이터의 월에 일치하는 제약 조건을 만들고 검사합니다(10월 파티션에 대해 ValidTo <= N'2023-10-31T23:59:59.999').
단계 (4)에서는 파티션 SWITCH을 새로 만든 준비 테이블로 1합니다.
단계 (6)에서는 10월에 대한 데이터를 이동한 후 하한 경계 MERGE RANGE(N'2023-10-31T23:59:59.999'를 병합하여 파티션 함수를 변경합니다.
단계 (7)에서 파티션 함수를 분할하여 10월 데이터를 이동한 후 새 상한 SPLIT RANGE (N'2024-04-30T23:59:59.999'를 만듭니다.

그러나 최적의 솔루션은 스크립트 수정 없이 매월 적절한 작업을 수행할 수 있는 일반 Transact-SQL 스크립트를 정기적으로 실행하는 것입니다. 제공된 매개 변수(병합해야 하는 하한 경계 및 파티션 분할로 만들어지는 새 경계)에 따라 동작하도록 이전 스크립트를 일반화할 수 있습니다. 매달 준비 테이블이 생성되지 않도록 하려면 미리 테이블을 만들고 전환할 파티션과 일치하도록 CHECK 제약 조건을 변경하여 다시 사용할 수 있습니다. Transact-SQL 스크립트를 사용하여 슬라이딩 윈도우를 완전히 자동화하는 방법에 대한 아이디어를 얻으려면 다음 페이지를 살펴보세요.

테이블 분할과 관련된 성능 고려 사항

데이터 이동으로 인해 상당한 성능 오버헤드가 발생할 수 있으므로 데이터 이동을 방지하기 위해 MERGE 및 SPLIT RANGE 작업을 수행하는 것이 중요합니다. 자세한 내용은 파티션 함수 수정을 참조하세요. 파티션 함수를 만들 때RANGE RIGHT 대신 RANGE LEFT를 사용하여 이 작업을 수행합니다.

먼저 시각적으로 RANGE LEFT 및 RANGE RIGHT 옵션의 의미를 설명해 보겠습니다.

파티션 함수를 RANGE LEFT로 정의할 때 지정된 값은 파티션의 상한입니다. RANGE RIGHT를 사용할 때 지정된 값은 파티션의 하한입니다. MERGE RANGE 작업을 사용하여 파티션 함수 정의에서 경계를 제거할 경우 기본적으로 경계가 포함된 파티션도 제거되도록 구현됩니다. 해당 파티션이 비어 있지 않으면 데이터가 MERGE RANGE 작업의 결과인 파티션으로 이동됩니다.

슬라이딩 윈도우 시나리오에서는 항상 파티션 하한 경계를 제거합니다.

RANGE LEFT 케이스: 파티션 하한 경계는 파티션이 전환된 후 비어 있는 파티션 1에 속하므로 MERGE RANGE에서는 데이터 이동이 발생하지 않습니다.
RANGE RIGHT 케이스: 파티션 하한 경계는 파티션 2을 전환하여 비웠으므로 비어 있지 않은 파티션 1에 속합니다. 이 경우 MERGE RANGE에서 데이터 이동이 발생합니다(파티션 2의 데이터가 파티션 1로 이동됨). 이 문제를 방지하려면 슬라이딩 윈도우 시나리오에서 RANGE RIGHT에는 항상 빈 상태인 파티션 1이 있어야 합니다. 즉, RANGE RIGHT를 사용하는 경우 RANGE LEFT 케이스에 비해 하나의 추가 파티션을 만들고 유지 관리해야 합니다.

결론: 슬라이딩 파티션에서 RANGE LEFT를 사용하면 파티션 관리가 훨씬 간단해지고 데이터 이동이 발생하지 않습니다. 그러나 날짜/시간 검사 문제를 처리할 필요가 없으므로 RANGE RIGHT로 파티션 경계를 정의하는 것이 약간 더 간단합니다.

사용자 지정 정리 스크립트 접근 방식 사용

테이블 분할을 실행할 수 없는 경우 또 다른 접근 방식은 사용자 지정 정리 스크립트를 사용하여 기록 테이블에서 데이터를 삭제하는 것입니다. 기록 테이블에서 데이터를 삭제하는 것은 SYSTEM_VERSIONING = OFF인 경우에만 가능합니다. 데이터 불일치를 방지하려면 유지 관리 기간(데이터를 수정하는 워크로드가 활성 상태가 아닌 경우) 또는 트랜잭션 내에서(다른 워크로드를 효과적으로 차단하는 경우) 정리를 수행합니다. 이 작업을 수행하려면 현재 및 기록 테이블에 대한 CONTROL 권한이 필요합니다.

일반 애플리케이션 및 사용자 쿼리를 최소한으로 차단하려면 트랜잭션 내에서 정리 스크립트를 수행할 때 지연을 두고 작은 청크로 데이터를 삭제합니다. 모든 시나리오에서 삭제할 각 데이터 청크에 적합한 크기는 없지만, 단일 트랜잭션에서 10,000개 이상의 행을 삭제하면 상당한 영향을 미칠 수 있습니다.

정리 논리는 모든 temporal 테이블에서 동일합니다. 따라서 데이터 기록을 제한하려는 모든 temporal 테이블에 대해 정기적인 실행이 예약된 일반 저장 프로시저를 통해 자동화할 수 있습니다.

다음 다이어그램은 실행 중인 작업에 미치는 영향을 줄이기 위해 단일 테이블에 대해 정리 논리를 구성하는 방식을 보여 줍니다.

프로세스를 구현하기 위한 몇 가지 대략적인 지침은 다음과 같습니다. 매일 실행되도록 정리 논리를 예약하고 데이터 정리가 필요한 모든 temporal 테이블을 반복합니다. SQL Server 에이전트 또는 다른 도구를 사용하여 이 프로세스를 예약합니다.

이전 다이어그램에 표시된 것처럼 작은 청크로 여러 번 반복하여 가장 오래된 행부터 가장 최근 행까지 모든 temporal 테이블에서 기록 데이터를 삭제하고 단일 트랜잭션에서 모든 행을 삭제하지 않도록 합니다.
기록 테이블에서 데이터의 일부를 제거하는 제네릭 저장 프로시저 호출로 모든 반복을 구현합니다(이 절차는 다음 코드 예제 참조).
프로세스를 호출할 때마다 개별 temporal 테이블에 대해 삭제해야 하는 행 수를 계산합니다. 행 수와 반복 수에 따라 모든 프로시저 호출에 대한 동적 분할 지점을 결정합니다.
temporal 테이블에 액세스하는 애플리케이션에 미치는 영향을 줄이기 위해 단일 테이블에 대한 반복 사이에 지연 기간을 두도록 계획합니다.

단일 temporal 테이블에 대한 데이터를 삭제하는 저장 프로시저는 다음 코드 조각과 같이 표시될 수 있습니다(이 코드를 주의 깊게 검토하고 환경에 적용하기 전에 조정).

DROP PROCEDURE IF EXISTS usp_CleanupHistoryData;
GO
CREATE PROCEDURE usp_CleanupHistoryData @temporalTableSchema SYSNAME,
    @temporalTableName SYSNAME,
    @cleanupOlderThanDate DATETIME2
AS
DECLARE @disableVersioningScript NVARCHAR(MAX) = '';
DECLARE @deleteHistoryDataScript NVARCHAR(MAX) = '';
DECLARE @enableVersioningScript NVARCHAR(MAX) = '';
DECLARE @historyTableName SYSNAME
DECLARE @historyTableSchema SYSNAME
DECLARE @periodColumnName 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 t1
  INNER JOIN sys.tables t2 ON t1.history_table_id = t2.object_id
  INNER JOIN sys.schemas s1 ON t1.schema_id = s1.schema_id
  INNER JOIN sys.schemas s2 ON t2.schema_id = s2.schema_id
  INNER JOIN sys.periods p ON p.object_id = t1.object_id
  INNER JOIN sys.columns 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;

/*Generate 3 statements that run inside a transaction:
  (1) SET SYSTEM_VERSIONING = OFF,
  (2) DELETE FROM history_table,
  (3) SET SYSTEM_VERSIONING = ON
  On SQL Server 2016, it is critical that (1) and (2) run in separate EXEC statements, or SQL Server generates the following error:
  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>'.
*/

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
    EXEC (@disableVersioningScript);
    EXEC (@deleteHistoryDataScript);
    EXEC (@enableVersioningScript);
COMMIT;

임시 기록 보존 정책 접근 방식 사용

적용 대상: SQL Server 2017(14.x) 이상 버전 및 Azure SQL Database.

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

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

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

앞의 예제에서 ValidTo 열은 SYSTEM_TIME 기간의 끝에 해당합니다.

보존 정책을 구성하는 방법

temporal 테이블에 대한 보존 정책을 구성하기 전에 먼저 임시 기록 보존이 데이터베이스 수준에서 사용하도록 설정되었는지 여부를 확인합니다.

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases;

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

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION ON;

보존 정책은 테이블을 만드는 동안 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
    )
);

다른 시간 단위 DAYS, WEEKS, MONTHS 및 YEARS를 사용하여 보존 기간을 지정할 수 있습니다. HISTORY_RETENTION_PERIOD를 생략할 경우 INFINITE 보존으로 가정됩니다. INFINITE 키워드를 명시적으로 사용할 수도 있습니다.

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

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

보존 정책의 현재 상태를 살펴보려면 데이터베이스 수준에서 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;

SQL Database에서 오래된 행을 삭제하는 방법

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

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

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

자세한 내용은 보존 정책을 사용하여 temporal 테이블에서 기록 데이터 관리를 참조하세요.

시스템 버전 관리된 temporal 테이블에서 기록 데이터의 보존 관리

기록 테이블에 대한 데이터 보존 관리

테이블 분할 방법 사용

Transact-SQL을 사용하여 기록 테이블에 파티션 만들기

Transact-SQL을 사용하여 슬라이딩 윈도우 시나리오에서 파티션 유지 관리

테이블 분할과 관련된 성능 고려 사항

사용자 지정 정리 스크립트 접근 방식 사용

임시 기록 보존 정책 접근 방식 사용

보존 정책을 구성하는 방법

SQL Database에서 오래된 행을 삭제하는 방법

피드백

피드백

추가 리소스

시스템 버전 관리된 temporal 테이블에서 기록 데이터의 보존 관리

기록 테이블에 대한 데이터 보존 관리

테이블 분할 방법 사용

Transact-SQL을 사용하여 기록 테이블에 파티션 만들기

Transact-SQL을 사용하여 슬라이딩 윈도우 시나리오에서 파티션 유지 관리

테이블 분할과 관련된 성능 고려 사항

사용자 지정 정리 스크립트 접근 방식 사용

임시 기록 보존 정책 접근 방식 사용

보존 정책을 구성하는 방법

SQL Database에서 오래된 행을 삭제하는 방법

관련 콘텐츠

피드백

피드백

추가 리소스