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

  • Статья

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

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

  • Данные журнала сохраняются в течение длительного периода времени.
  • Обновляется или удаляется шаблон изменения данных большого объема.

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

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

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

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

С каждым из этих подходов логика миграция или очистки журнала данных основывается на столбце, который соответствует концу периода в текущей таблице. Значение окончания периода для каждой строки определяет момент, когда версия строки становится закрытой, то есть, когда она попадает в таблицу журнала. Например, условие 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: слияние пустой секции с секцией с помощью ФУНКЦИИ ALTER PARTITION 1 2 с MERGE RANGE (см. пример B). Удаляя самую низкую границу с помощью этой функции, вы эффективно объединяете пустую секцию 1 с бывшим разделом 2 , чтобы сформировать новую секцию 1. Порядковые номера других секций также изменяются.

  3. SPLIT RANGE: создайте пустую секцию с помощью ФУНКЦИИ ALTER PARTITION 7 с 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, который выполняет соответствующее действие каждый месяц без изменений. Вы можете обобщить предыдущий скрипт для выполнения указанных параметров (нижняя граница, которую необходимо объединить, и новую границу, созданную с разделением секции). Чтобы избежать создания промежуточной таблицы каждый месяц, можно создать ее заранее и повторно использовать, изменив ограничение проверки на соответствие секции, которую вы выключаете. Дополнительные сведения см. в статье о том, как скользящее окно может быть полностью автоматизировано.

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

Чтобы избежать перемещения данных, необходимо выполнить MERGE SPLIT 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+, порядок удаления строк старше срока хранения не может быть гарантирован. Не зависимостей от порядка очистки в приложениях.

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

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

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

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

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