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

  • Статья

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

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

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

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

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

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

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

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

Примечание.

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

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

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

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

Примечание.

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

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

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

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

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

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

Diagram showing initial partitioning configuration to keep six months of data.

Примечание.

Ознакомьтесь с рекомендациями по повышению производительности с секционированием таблиц далее в этой статье, чтобы узнать, RANGE LEFT как использовать производительность и RANGE RIGHT при настройке секционирования.

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

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

Diagram showing the recurring partition maintenance tasks.

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

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

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

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

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

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

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

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

Давайте сначала визуально объясним смысл RANGE LEFT и RANGE RIGHT варианты:

Diagram showing the RANGE LEFT and RANGE RIGHT options.

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

В сценарии с скользящим окном всегда удаляются самые низкие границы секций.

  • 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 разрешение на текущие таблицы и таблицы журнала.

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

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

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

Diagram showing how your cleanup logic should be organized for a single table to reduce impact on the running workloads.

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

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

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

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

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

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

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

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;

Как База данных SQL удаляет устаревшие строки?

Процесс очистки зависит от макета индекса таблицы журналов. Только таблицы журнала с кластеризованным индексом (B+ дерево или columnstore) могут иметь ограниченную политику хранения. Для очистки устаревших данных во всех темпоральных таблицах с ограниченным периодом хранения создается фоновая задача. Логика очистки для кластеризованного индекса rowstore (B⁺-дерево) удаляет устаревшие строки небольшими фрагментами (до 10 тыс. строк), что позволяет минимизировать нагрузку на журнал базы данных и подсистему ввода-вывода. Хотя логика очистки использует обязательный индекс дерева B+, порядок удаления строк старше срока хранения не может быть твердо гарантирован. Поэтому не зависимостей от порядка очистки в приложениях.

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

Screenshot of clustered columnstore retention.

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

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

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