Поделиться через

Управление хранением исторических данных в темпоральных таблицах с системой версионности

  • Статья

применяется к: SQL Server 2016 (13.x) и более поздним версиям базе данных SQL AzureУправляемому экземпляру SQL Azureбазе данных SQL в Microsoft Fabric

При использовании системных версионированных темпоральных таблиц таблица истории может увеличить размер базы данных больше, чем обычные таблицы, особенно в следующих условиях:

  • Вы храните исторические данные в течение длительного периода времени.
  • У вас есть шаблон модификации данных, характеризующийся частыми обновлениями или удалениями.

Большая и постоянно растущая таблица истории может стать проблемой как из-за чистых затрат на хранение, так и повлиять на производительность во время выполнения временных запросов. Разработка политики хранения данных для управления данными в таблице истории является важным аспектом планирования и управления жизненным циклом каждой темпоральной таблицы.

Управление хранением данных для таблицы истории

Управление хранением данных темпоральных таблиц начинается с определения необходимых сроков хранения для каждой таблицы. Политика хранения в большинстве случаев должна быть частью бизнес-логики приложения с помощью темпоральных таблиц. Например, приложения в сценариях аудита данных и перемещения по времени имеют жесткие требования относительно того, как долго исторические данные должны быть доступны для онлайн-запросов.

После определения срока хранения данных необходимо разработать план для управления историческими данными. Определите, как и где хранятся исторические данные, а также как удалить исторические данные, которые старше ваших требований к хранению. Доступны следующие подходы к управлению историческими данными в темпоральной таблице истории.

С каждым из этих подходов логика миграции или очистки исторических данных основана на столбце, соответствующем концу периода в текущей таблице. Значение окончания периода для каждой строки определяет момент, когда версия строки становится закрытой, то есть, когда она попадает в таблицу журнала. Например, условие ValidTo < DATEADD (DAYS, -30, SYSUTCDATETIME ()) указывает на то, что записи журнала старше чем один месяц должны быть удалены или перемещены из таблицы журнала.

В примерах в этой статье используются примеры, созданные в статье "Создание системной темпоральной таблицы ".

Использование подхода секционирования таблиц

Секционированные таблицы и индексы могут сделать большие таблицы более управляемыми и масштабируемыми. С помощью подхода к секционированием таблиц можно реализовать настраиваемую очистку данных или автономную архивацию на основе условия времени. Секционирование таблиц также дает преимущества производительности при запросе темпоральных таблиц в подмножестве журнала данных с помощью исключения секций.

С секционированием таблиц можно реализовать скользящее окно, чтобы выгрузить самую старую часть исторических данных из исторической таблицы и сохранить размер сохраняемой части по возрасту. Скользящее окно поддерживает данные в таблице истории в соответствии с требуемым периодом хранения. Операция переключения данных из таблицы истории поддерживается в то время SYSTEM_VERSIONING, как ON, что означает, что вы можете очистить часть данных журнала, не вводя окно обслуживания или блокируя обычные рабочие нагрузки.

Примечание.

Чтобы выполнить переключение секций, кластеризованный индекс в таблице журнала должен быть выровнен со схемой секционирования (она должна содержать ValidTo). Таблица журнала по умолчанию, созданная системой, содержит кластеризованный индекс, который включает в себя ValidTo и ValidFrom столбцы, оптимальные для секционирования, вставки новых данных журнала и типичных временных запросов. Дополнительные сведения см. в разделе Темпоральные таблицы.

В скользящем окне есть два набора задач, которые необходимо выполнить:

  • Задачи настройки секционирования
  • Повторяющиеся задачи обслуживания секций

На рисунке предположим, что вы хотите хранить исторические данные в течение шести месяцев и хранить каждый месяц данных в отдельной секции. Кроме того, предположим, что вы активировали системное управление версиями в сентябре 2023 года.

При настройке секционирования создается исходная конфигурация секционирования для таблицы истории. В этом примере вы создаете те же числа секций, что и размер скользящего окна в месяцах, а также дополнительный готовый пустой раздел (описано далее в этой статье). Эта конфигурация гарантирует, что система может правильно хранить новые данные при первом запуске задачи обслуживания повторяющихся секций и гарантирует, что вы никогда не разбиваете секции с данными, чтобы избежать дорогостоящих перемещений данных. Эту задачу следует выполнить с помощью Transact-SQL с помощью примера сценария далее в этой статье.

На следующем рисунке показана начальная конфигурация секционирования для хранения шести месяцев данных.

Схема начальной конфигурации секционирования для сохранения данных за шесть месяцев.

Примечание.

Для получения информации о влиянии на производительность использования RANGE LEFT по сравнению с RANGE RIGHT при конфигурации секционирования см. Рекомендации по производительности при секционировании таблиц далее в этой статье.

Первая и последняя секции открыты на нижних и верхних границах соответственно, чтобы каждая новая строка попадала в нужную секцию независимо от значения в столбце секционирования. Со временем новые строки в таблице истории помещаются в более высокие разделы. Когда шестой раздел заполняется, вы достигаете заданного периода хранения. Сейчас самое время впервые запустить задачу по регулярному обслуживанию разделов. В этом примере необходимо периодически запускать его один раз в месяц.

На следующем рисунке показаны повторяющиеся задачи обслуживания секций (см. подробные инструкции далее в этом разделе).

Схема, показывающая повторяющиеся задачи обслуживания разделов.

Подробные шаги для повторяющихся задач обслуживания разделов следующие:

  1. SWITCH OUT: создайте промежуточную таблицу и переключите секцию между таблицей журнала и промежуточной таблицей с помощью инструкции ALTER TABLE с SWITCH PARTITION аргументом (см. пример C. Переключение секций между таблицами).

    ALTER TABLE [<history table>] SWITCH PARTITION 1 TO [<staging table>];

    После переключения разделов можно при необходимости архивировать данные из промежуточной таблицы, а затем удалить или усечь промежуточную таблицу, чтобы быть готовым к следующему выполнению этой повторяющейся задачи по обслуживанию разделов.

  2. MERGE RANGE: Объедините пустой раздел 1 с разделом 2 с помощью функции ALTER PARTITION FUNCTION с MERGE RANGE (см. пример B). Удаляя самую низкую границу с помощью этой функции, вы эффективно объединяете пустую секцию 1 с бывшим разделом 2 , чтобы сформировать новую секцию 1. Порядковые номера других секций также изменяются.

  3. SPLIT RANGE: создайте новый пустой раздел 7 с помощью ALTER PARTITION FUNCTION с SPLIT RANGE (см. пример A). При добавлении верхней границы с использованием этой функции происходит по сути создание отдельной секции для предстоящего месяца.

Используйте Transact-SQL для создания разделов в таблице истории

Используйте следующий скрипт Transact-SQL, чтобы создать функцию секционирования, схему секционирования и повторно создать кластеризованный индекс так, чтобы он был выровнен по схеме секционирования и соответствующим секциям. В этом примере вы создаете шестимесячное скользящее окно с ежемесячными разделами, начиная с сентября 2023 года.

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 для поддержания секций в сценарии скользящего окна. В этом примере вы замените раздел на сентябрь 2023 г. с помощью MERGE RANGE, а затем добавите новый раздел на март 2024 г. с помощью SPLIT RANGE.

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. На шаге (1) создайте новую промежуточную таблицу для месяца, который вы хотите удалить (октябрь будет следующим в этом примере).
  2. На шаге (3) создайте и проверьте ограничение, соответствующее месяцу данных, которые необходимо удалить: ValidTo <= N'2023-10-31T23:59:59.999' для секции октября.
  3. На шаге (4) SWITCH разделите 1 на только что созданную промежуточную таблицу.
  4. На шаге (6) измените функцию раздела путем объединения нижней границы: MERGE RANGE(N'2023-10-31T23:59:59.999' после перемещения данных за октябрь.
  5. На шаге (7) разделите функцию разбиения, создав новую верхнюю границу: SPLIT RANGE (N'2024-04-30T23:59:59.999' после удаления данных за октябрь.

Однако оптимальное решение — регулярно запускать универсальный скрипт Transact-SQL, который выполняет соответствующее действие каждый месяц без изменений. Вы можете обобщить предыдущий скрипт для выполнения указанных параметров (нижняя граница, которую необходимо объединить, и новую границу, созданную с разделением секции). Чтобы избежать создания промежуточной таблицы каждый месяц, можно создать ее заранее и повторно использовать, изменив ограничение проверки на соответствие секции, которую вы выключаете. Дополнительные сведения см. в статье о том, как скользящее окно может быть полностью автоматизировано.

Вопросы, связанные с производительностью при секционировании таблиц

Чтобы избежать перемещения данных, необходимо выполнить MERGESPLIT RANGE операции, так как перемещение данных может нести значительные затраты на производительность. Для получения дополнительной информации см. раздел Изменение функции разбиения. Вы делаете это с помощью RANGE LEFT, а не RANGE RIGHT, когда создаете функцию секционирования.

На следующей схеме описаны RANGE LEFT и RANGE RIGHT параметры.

Схема параметров 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 строк в одной транзакции может привести к значительному штрафу.

Логика очистки одинакова для каждой темпоральной таблицы, поэтому она может быть автоматизирована с помощью универсальной хранимой процедуры, которую планируется периодически выполнять для каждой темпоральной таблицы, для которой требуется ограничить журнал данных.

На следующей схеме показано, как логика очистки должна быть структурирована для одной таблицы, чтобы снизить влияние на текущие рабочие нагрузки.

Схема, показывающая, как должна быть организована логика очистки для одной таблицы, чтобы уменьшить влияние на выполняемые рабочие нагрузки.

Ниже приведены общие рекомендации по реализации процесса. Запланируйте выполнение логики очистки так, чтобы оно происходило ежедневно, и обойдите все темпоральные таблицы, для которых требуется очистка данных. Используйте агент SQL Server или другое средство для планирования этого процесса:

  • Удалите исторические данные в каждой темпоральной таблице, начиная с старейших до последних строк в нескольких итерациях в небольших блоках, и избегайте удаления всех строк в одной транзакции, как показано на предыдущей схеме.

  • Реализуйте каждую итерацию в качестве вызова универсальной хранимой процедуры, которая удаляет часть данных из таблицы журнала (см. следующий пример кода для этой процедуры).

  • Подсчитайте, сколько строк необходимо удалять из одной темпоральной таблицы при каждом запуске процесса. На основе результата и количества итераций, которые требуется выполнить, определите динамические точки разделения для каждого вызова процедуры.

  • Запланируйте период задержки между итерациями для одной таблицы, чтобы уменьшить влияние на приложения, обращаюющиеся к темпоральной таблице.

Хранимая процедура, которая удаляет данные для одной темпоральной таблицы, может выглядеть следующим фрагментом кода. Внимательно просмотрите этот код и настройте его перед применением в вашей среде.

Этот скрипт создает три выражения, которые выполняются в рамках транзакции.

  1. SET SYSTEM_VERSIONING = OFF
  2. DELETE FROM <history_table>
  3. SET SYSTEM_VERSIONING = ON

В SQL Server 2016 (13.x) первые два шага должны выполняться в отдельных EXEC инструкциях, или 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 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;

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) и более поздних версий и База данных SQL Azure.

Хранение темпоральных журналов можно настроить на уровне отдельных таблиц, что позволит пользователям создавать гибкие политики устаревания. Для хранения темпоральных данных необходимо задать только один параметр во время создания таблицы или изменения схемы.

После определения политики хранения ядро СУБД регулярно проверяет наличие исторических строк, которые имеют право на автоматическую очистку данных. Идентификация соответствующих строк и их удаление из таблицы журнала происходит прозрачно в фоновой задаче, запланированной и запущенной системой. Условие возраста для строк таблицы журнала проверяется на основе столбца, представляющего конец SYSTEM_TIME периода (в этих примерах ValidTo столбец). Если для периода хранения задано шесть месяцев, например строки таблиц, подходящие для очистки, соответствуют следующему условию:

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 после операции восстановления в определенный момент времени (PITR). Чтобы включить очистку хранимой временной истории для базы данных, выполните следующий запрос. Необходимо заменить <myDB> на имя базы данных, которую вы хотите изменить.

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, , WEEKSMONTHSи YEARS. Если HISTORY_RETENTION_PERIOD опущено, предполагается сохранение INFINITE. Вы также можете явно использовать ключевое INFINITE слово.

В некоторых сценариях может потребоваться настроить хранение после создания таблицы или изменить ранее настроенное значение. В этом случае используйте инструкцию ALTER TABLE :

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

Чтобы проверить текущее состояние политики хранения, используйте следующий пример. Этот запрос присоединяет флаг включения временного хранения на уровне базы данных с периодами хранения для отдельных таблиц:

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) могут иметь ограниченную политику хранения данных. Фоновая задача создается для выполнения устаревшей очистки данных для всех темпоральных таблиц с конечным периодом хранения. Логика очистки кластеризованного индекса rowstore (B+ tree) удаляет устаревшие строки в небольших блоках (до 10 000), минимизируя давление на журнал базы данных и подсистему ввода-вывода. Хотя логика очистки использует требуемый индекс дерева B+, порядок удаления строк старше срока хранения не может быть гарантирован. Не полагайтесь на порядок очистки в ваших приложениях.

Задача очистки для кластеризованного колоночного хранилища удаляет целые группы строк сразу (обычно содержащие до 1 миллиона строк каждая), что более эффективно, особенно если исторические данные создаются в большом объеме.

Снимок экрана: хранение кластеризованного столбцового хранилища.

Очистка сжатия и хранения данных делает кластеризованный индекс columnstore идеальным выбором для сценариев, когда рабочая нагрузка быстро создает большое количество исторических данных. Подобная ситуация типична для интенсивных рабочих нагрузок по обработке транзакций, в которых темпоральные таблицы используются для контроля и аудита изменений, анализа тенденций и приема данных Интернета вещей.

Дополнительные сведения см. в статье "Управление историческими данными в темпоральных таблицах с политикой хранения".

Связанный контент