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

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

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

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

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

Планируйте политику хранения данных

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

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

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

Выберите один из следующих способов выполнения действий для этих строк:

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

Примеры разбиения и пользовательской очистки в этой статье основаны на примерах из статьи «Создание системно-версионируемой темпоральной таблицы».

Используйте политику сохранения временной истории

Применимо к: SQL Server 2017 (14.x) и более поздним версиям, База данных SQL Azure, Управляемый экземпляр SQL Azure и SQL Database в Microsoft Fabric.

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

После определения политики хранения ядро СУБД запускает запланированную фоновую задачу, которая находит и прозрачно удаляет исторические строки, значение конца которых старше периода сохранения.

Настройка политики хранения

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

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;

Important

Вы можете настроить удержание временных таблиц, даже если is_temporal_history_retention_enabled это OFF, но ядро СУБД в этом случае не запускает автоматическую очистку для старых строк.

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

CREATE TABLE dbo.WebsiteUserInfo
(
    UserID INT NOT NULL PRIMARY KEY CLUSTERED,
    UserName NVARCHAR (100) NOT NULL,
    PagesVisited INT NOT NULL,
    ValidFrom DATETIME2 (0) GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2 (0) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.WebsiteUserInfoHistory,
        HISTORY_RETENTION_PERIOD = 6 MONTHS
    )
);

С такой политикой ряды dbo.WebsiteUserInfoHistory становятся пригодными для уборки, если они соответствуют следующим условиям:

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

Вы можете указать период удержания в DAYS, WEEKS, MONTHSили YEARS. Если не указывать HISTORY_RETENTION_PERIOD, срок хранения по умолчанию равен INFINITE. Вы также можете явно использовать ключевое INFINITE слово.

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

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

Important

При изменении значения SYSTEM_VERSIONING на OFF значение срока хранения не сохраняется. Установка SYSTEM_VERSIONING в значение ON без явного HISTORY_RETENTION_PERIOD приводит к сохранению INFINITE.

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

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 AS T1
    OUTER APPLY (
        SELECT is_temporal_history_retention_enabled
        FROM sys.databases
        WHERE name = DB_NAME()
) AS DB
    LEFT OUTER JOIN sys.tables AS T2
        ON T1.history_table_id = T2.object_id
WHERE T1.temporal_type = 2;

Как ядро СУБД удаляет устаревшие строки

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

Note

В документации термин B-tree обычно используется в ссылке на индексы. В индексах rowstore ядро СУБД реализует дерево B+. Это не относится к индексам columnstore или индексам в таблицах, оптимизированных для памяти. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Sql Server и Azure.

Индекс B-дерева для построчного хранилища

Кластерный индекс Rowstore должен начинаться со столбца, соответствующего концу SYSTEM_TIME периода. Если такого индекса нет, нельзя настроить конечный период удержания:

Msg 13765, Level 16, State 1
Setting finite retention period failed on system-versioned temporal table
'dbo.WebsiteUserInfo' because the history table 'dbo.WebsiteUserInfoHistory'
does not contain required clustered index. Consider creating a clustered
columnstore or B-tree index starting with the column that matches end of
SYSTEM_TIME period, on the history table.

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

Msg 13766, Level 16, State 1
Cannot drop the clustered index 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory'
because it is being used for automatic cleanup of aged data. Consider setting HISTORY_RETENTION_PERIOD to INFINITE on the corresponding system-versioned
temporal table if you need to drop this index.

Логика очистки кластерного индекса в Rowstore удаляет старые строки меньшими частями (до 10 000), минимизируя нагрузку на журнал базы данных и подсистему ввода-вывода. Хотя логика очистки использует требуемый индекс B-дерева, она не может гарантировать порядок удаления для строк старше периода сохранения. Не полагайтесь на порядок очистки в ваших приложениях.

Кластеризованный индекс хранилища столбцов

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

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

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

Очистка кластеризованного индекса columnstore работает оптимально, когда исторические строки поступают в порядке возрастания (упорядоченные по столбцу конца периода). Это условие всегда наблюдается, когда только SYSTEM_VERSIONING механизм заполняет таблицу истории. Если строки в таблице истории не упорядочены по столбцу конца периода (что может произойти при миграции существующих исторических данных), заново создайте кластерный индекс столбцового хранилища поверх правильно упорядочённого индекса B-дерева для достижения оптимальной производительности.

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

/* Create B-tree ordered by the end-of-period column */
CREATE CLUSTERED INDEX IX_WebsiteUserInfoHistory
    ON WebsiteUserInfoHistory(ValidTo) WITH (DROP_EXISTING = ON);
GO

/* Re-create the clustered columnstore index */
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
    ON WebsiteUserInfoHistory WITH (DROP_EXISTING = ON);

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

CREATE NONCLUSTERED INDEX IX_WebHistNCI
    ON WebsiteUserInfoHistory(UserName);

Предыдущее утверждение не работает со следующей ошибкой:

Msg 13772, Level 16, State 1
Cannot create non-clustered index on a temporal history table 'WebsiteUserInfoHistory' since it has finite retention period and clustered columnstore index defined.

Таблицы запроса с политикой хранения данных

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

Следующий скриншот показывает план запроса для базового запроса. В этом примере предполагается период хранения в одинMONTH для таблицы WebsiteUserInfo:

SELECT *
FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

План запроса включает дополнительный фильтр на столбце конца периода (ValidTo) в операторе Clustered Index Scan (выделено на следующем изображении) в таблице истории.

Скриншот плана запроса с дополнительным фильтром хранения в столбце ValidTo в таблице истории.

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

Скриншот плана запроса при прямом запросе к таблице истории без фильтра удержания.

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

Аспекты восстановления на определенный момент времени

Когда вы восстанавливаете базу данных в определённый момент времени, на уровне базы данных отключается временное сохранение (is_temporal_history_retention_enabled установлено на OFF). Такое поведение позволяет проверять исторические строки старше срока хранения до того, как задача очистки удалит их. Чтобы возобновить автоматическую очистку восстановленной базы данных, установите TEMPORAL_HISTORY_RETENTION обратно на ON.

Note

База данных, созданная на уровне Premium в База данных SQL Azure, сохраняет резервные копии до 35 дней, так что вы можете восстановить её в определённом месте в этом окне. Для временной таблицы с месячным периодом хранения можно изучать исторические строки возрастом до 65 дней, отправляя запросы к таблице истории непосредственно в восстановленной базе данных.

Используйте разделение таблиц

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

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

Note

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

Скользящее окно требует двух задач:

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

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

При настройке секционирования создается исходная конфигурация секционирования для таблицы истории. В этом примере вы создаёте такое же количество разделов, как размер скользящего окна, за месяцы плюс один дополнительный пустой раздел. Такая конфигурация гарантирует, что система сможет правильно хранить новые данные при первом запуске повторяющейся задачи по поддержанию раздела. Это также гарантирует, что вы не разделяете разделы, содержащие данные, что позволяет избежать дорогостоящих перемещений данных. Определите функцию разбиения с помощью RANGE LEFT, а не RANGE RIGHT. Для получения дополнительной информации см. раздел «Вопросы производительности при разбиении таблиц » позже в этой статье.

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

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

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

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

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

Каждый запуск повторяющейся задачи по обслуживанию выполняет следующие этапы:

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

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

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

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

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

Используйте 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 to switch 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 to switch 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;

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

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

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

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

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

Схема параметров RANGE LEFT и RANGE RIGHT.

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

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

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

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

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

Вот несколько основных рекомендаций по реализации этого процесса:

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

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

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

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

Следующая сохранённая процедура удаляет данные для одной временной таблицы. Он находит таблицу журнала и столбец окончания периода в представлениях каталога, а затем выполняет три инструкции внутри транзакции: SET SYSTEM_VERSIONING = OFF, DELETE FROM <history_table> и SET SYSTEM_VERSIONING = ON. Внимательно изучите этот код и скорректируйте его перед применением в своей среде.

В SQL Server 2016 (13.x) первые два шага должны выполняться в отдельных EXECUTE инструкциях, или 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 AS NVARCHAR (MAX) = '';
DECLARE @deleteHistoryDataScript AS NVARCHAR (MAX) = '';
DECLARE @enableVersioningScript AS NVARCHAR (MAX) = '';
DECLARE @historyTableName AS SYSNAME;
DECLARE @historyTableSchema AS SYSNAME;
DECLARE @periodColumnName AS 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 AS t1
         INNER JOIN sys.tables AS t2
             ON t1.history_table_id = t2.object_id
         INNER JOIN sys.schemas AS s1
             ON t1.schema_id = s1.schema_id
         INNER JOIN sys.schemas AS s2
             ON t2.schema_id = s2.schema_id
         INNER JOIN sys.periods AS p
             ON p.object_id = t1.object_id
         INNER JOIN sys.columns AS 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;
    EXECUTE (@disableVersioningScript);
    EXECUTE (@deleteHistoryDataScript);
    EXECUTE (@enableVersioningScript);
COMMIT TRANSACTION;

Связанные материалы

  • Last updated on