Управление хранением данных журнала в темпоральных таблицах с системным управлением версиямиManage retention of historical data in system-versioned temporal tables

Применимо к:Applies to: даSQL Server 2016 (13.x);SQL Server 2016 (13.x)yesSQL Server 2016 (13.x);SQL Server 2016 (13.x) и более поздние версии ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed InstanceПрименимо к:Applies to: даSQL Server 2016 (13.x);SQL Server 2016 (13.x)yesSQL Server 2016 (13.x);SQL Server 2016 (13.x) and later ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed Instance

Вместе с темпоральными таблицами с системным управлением версиями таблица журнала может увеличить размер базы данных больше, чем это делают обычные таблицы, особенно в следующих условиях.With system-versioned temporal tables, the history table may increase database size more than regular tables, particularly under the following conditions:

  • Данные журнала сохраняются в течение длительного периода времени.You retain historical data for a long period of time
  • Обновляется или удаляется шаблон изменения данных большого объема.You have an update or delete heavy data modification pattern

Большой и постоянно растущий объем таблицы журнала может стать проблемой не только из-за затрат на хранение. Кроме этого, он может повлиять на производительность при выполнении темпоральных запросов.A large and ever-growing history table can become an issue both due to pure storage costs as well as imposing a performance tax on temporal querying. Таким образом, разработка политики хранения данных для управления данными в таблице журнала является важным аспектом планирования и управления жизненным циклом всех темпоральных таблиц.Hence, developing a data retention policy for managing data in the history table is an important aspect of planning and managing the lifecycle of every temporal table.

Управление хранением данных для таблицы журналаData retention management for history table

Управление хранением данных темпоральных таблиц начинается с определения необходимых сроков хранения для каждой таблицы.Managing temporal table data retention begins with determining the required retention period for each temporal table. Политика хранения в большинстве случаев должна рассматриваться как часть бизнес-логики в приложении, использующем темпоральные таблицы.Your retention policy, in most cases, should be considered to be part of the business logic of the application using the temporal tables. Например, к приложениям, связанным с аудитом данных и переходами по времени, предъявляются жесткие требования, касающиеся того, как долго данные журнала должны быть доступны для запросов по сети.For example, applications in data audit and time travel scenarios have firm requirements in terms of for how long historical data must be available for online querying.

Когда вы определите срок хранения данных, следующим шагом будет разработка плана управления данными журнала, а именно определение способа и места хранения данных журнала, а также способа удаления данных после истечения их срока хранения.Once you determine your data retention period, your next step is to develop a plan for managing historical data how and where you store your historical data and how to delete historical data that is older than your retention requirements. Существуют четыре подхода к управлению данными журнала в темпоральной таблице журнала:The following four approaches for managing historical data in the temporal history table are available:

С каждым из этих подходов логика миграция или очистки журнала данных основывается на столбце, который соответствует концу периода в текущей таблице.With each of these approaches, the logic for migrating or cleaning history data is based on the column that corresponds to end of period in the current table. Значение конца периода для каждой строки определяет момент, когда версия строки становится "закрытой", т. е. когда она попадает в таблицу журнала.The end of period value for each row determines the moment when the row version becomes "closed", i.e. when it lands in the history table. Например, условие SysEndTime < DATEADD (DAYS, -30, SYSUTCDATETIME ()) указывает на то, что записи журнала старше чем один месяц должны быть удалены или перемещены из таблицы журнала.For example, the condition SysEndTime < DATEADD (DAYS, -30, SYSUTCDATETIME ()) specifies that historical data older than one month needs to be removed or moved out from the history table.

Примечание

В примерах этого раздела используется пример темпоральной таблицы.The examples in this topic use this Temporal Table example.

Использование базы данных StretchUsing stretch database approach

Примечание

Использование Stretch Database применимо только к SQL Server 2019 (15.x)SQL Server 2019 (15.x) и не может применяться к База данных SQLSQL Database.Using the Stretch Database approach only applies to SQL Server 2019 (15.x)SQL Server 2019 (15.x) and does not apply to База данных SQLSQL Database.

база данных Stretch; в SQL Server 2019 (15.x)SQL Server 2019 (15.x) прозрачно переносит данных журнала в Azure.Stretch Database in SQL Server 2019 (15.x)SQL Server 2019 (15.x) migrates your historical data transparently to Azure. Для обеспечения дополнительной защиты можно зашифровать передаваемые данные с помощью функции SQL Server Постоянное шифрование .For additional security, you can encrypt data in motion using SQL Server's Always Encrypted feature. Кроме того, для защиты данных можно использовать защиту на уровне строк и другие дополнительные функции безопасности SQL Server с помощью средств Temporal и базы данных Stretch.Additionally, you can use Row-Level Security and other advanced SQL Server security features with Temporal and Stretch Database to protect your data.

Используя базу данных Stretch, можно растянуть часть таблиц или все темпоральные таблицы журнала в Azure, и сервер SQL Server автоматически переместит данные журнала в Azure.Using the Stretch Database approach, you can stretch some or all of your temporal history tables to Azure and SQL Server will silently move historical data to Azure. Использование технологии Stretch для таблицы журнала не влияет на взаимодействие с темпоральной таблицей в части изменения данных и темпоральных запросов.Stretch-enabling a history table does not change how you interact with the temporal table in terms of data modification and temporal querying.

  • Растяжение всей таблицы журнала. Настройте базу данных Stretch для всей таблицы журнала, если вашим главным сценарием является аудит данных с частым изменением данных и редкими запросами данных журнала.Stretch the entire history table: Configure Stretch Database for your entire history table if your main scenario is data audit in the environment with frequent data changes and relatively rare querying on historical data. Другими словами, используйте этот подход, если производительность темпоральных запросов не критична.In other words, use this approach if performance of temporal querying is not critical. В этом случае экономическая эффективность, предоставляемая Azure, может оказаться привлекательной.In this case, the cost-effectiveness provided by Azure may be compelling. При растяжении всей таблицы журнала можно использовать либо мастер растяжения, либо службу Transact-SQL.When stretching the entire history table, you can either use the Stretch Wizard or Transact-SQL. Оба примера приведены ниже.Examples of both appear below.

  • Растяжение части таблицы журнала. Настройте базу данных Stretch только для части таблицы журнала, чтобы повысить производительность, если ваш главный сценарий включает в себя в основном запросы последних данных журнала, но при этом необходимо сохранить возможность запрашивать более ранние данные журнала при удаленном хранении этих данных по невысокой цене.Stretch a portion of the history table: Configure Stretch Database for only a portion of your history table to improve performance if your main scenario involves primarily querying recent historical data, but you wish to preserve the option to query older historical data when needed while storing this data remotely at a lower cost. Используя службу Transact-SQL, это можно сделать, указав функцию предиката, чтобы не переносить все строки из таблицы журнала, а указать только часть из них.With Transact-SQL, you can accomplish this by specifying a predicate function to select the rows that will be migrated from the history table rather than migrating all of the rows. При работе с темпоральными таблицами обычно имеет смысл перемещать данные по условию времени (т. е. на основе возраста версий строк в таблице журнала).When you work with temporal tables, it typically makes sense to move data based on time condition (i.e. based on age of the row version in the history table).

    Используя детерминированные функции предиката, можно сохранить часть журнала в одной базе данных вместе с текущими данными, а остальные переместить в Azure.Using a deterministic predicate function, you can keep portion of history in the same database with the current data, while the rest is migrated to Azure. Примеры и ограничения см. в разделе Выбор строк для миграции с использованием функции фильтров (база данных Stretch).For examples and limitations, see Select rows to migrate by using a filter function (Stretch Database). Поскольку недетерминированные функции являются недопустимыми, то, если требуется перемещать данные журнала по принципу скользящего окна, необходимо регулярно изменять определение встроенной функции предиката так, чтобы строки, сохраняемые локально, оставались неизменно одного возраста.Because non-deterministic functions are not valid, if you want to transfer history data in sliding window manner, you would need to regularly alter definition of the inline predicate function so that window of rows you keep locally is constant in terms of age. Скользящее окно позволяет непрерывно перемещать данные журнала старше одного месяца в Azure.Sliding window allows you to constantly move historical data older than one month to Azure. Пример такого подхода приведен ниже.An example of this approach appears below.

Примечание

Stretch Database перемещает данные в Azure.Stretch Database migrates data to Azure. Таким образом, необходимо иметь учетную запись Azure и подписку для выставления счетов.Therefore, you have to have an Azure account and a subscription for billing. Чтобы получить бесплатную пробную учетную запись Azure, щелкните Бесплатная пробная версия на один месяц.To get a free trial Azure account, click Free One-Month Trial.

Настроить темпоральную таблицу журнала для растяжения можно с помощью мастера растяжения или службы Transact-SQL, а включить растяжение для темпоральной таблицы журнала можно, если системное управление версиями включено (значение ON).You can configure a temporal history table for Stretch using either the Stretch Wizard or Transact-SQL, and you can stretch-enable a temporal history table while system-versioning is set to ON. Растяжение текущей таблицы не допускается, так как оно не имеет смысла.Stretching the current table is not allowed because it does not make sense to stretch the current table.

Использование мастера растяжения для растяжения всей таблицы журналаUsing the Stretch Wizard to stretch the entire history table

Самым простым способом для начинающих является использование мастера растяжения для включения растяжения для всей базы данных. Затем нужно выбрать темпоральную таблицу журнала в мастере растяжения (в этом примере предполагается, что таблица Department определена в качестве темпоральной таблицы с системным управлением версиями в изначально пустой базе данных).The easiest method for beginners is to use the Stretch Wizard to enable stretch for the entire database and then select the temporal history table within the Stretch wizard (this example assumes that you have configured the Department table as a system-versioned temporal table in an otherwise empty database). В SQL Server 2016 (13.x);SQL Server 2016 (13.x)нельзя щелкнуть правой кнопкой мыши саму темпоральную таблицу журнала и выбрать команду "Растяжение".In SQL Server 2016 (13.x);SQL Server 2016 (13.x), you cannot right-click the temporal history table itself and click Stretch.

  1. Щелкните базу данных правой кнопкой мыши и наведите указатель на пункт Задачи, затем на пункт Растяжениеи выберите пункт Включить , чтобы запустить мастер.Right-click your database and point to Tasks, point to Stretch, and then click Enable to launch the wizard.

  2. В окне Выбор таблиц установите флажок рядом с темпоральной таблицей журнала и нажмите кнопку "Далее".In the Select tables window, select the checkbox for the temporal history table and click Next.

    ![Выбор таблицы журнала на странице "Выбор таблиц"](../../relational-databases/tables/media/stretch-wizard-2-for-temporal.png "Выбор таблицы журнала на странице "Выбор таблиц"")Selecting the history table on the Select tables page

  3. В окне Настройка Azure укажите учетные данные для входа.In the Configure Azure window, provide your login credentials. Выполните вход в Microsoft Azure или зарегистрируйте учетную запись.Sign in to Microsoft Azure or sign-up for an account. Выберите подписку, которую будете использовать, и регион Azure.Select the subscription to use, select the Azure region. Затем создайте новый сервер или выберите существующий.Then either create a new server or select an existing server. Щелкните Далее.Click Next.

    Создание нового сервера Azure — мастер Stretch DatabaseCreate new Azure server - Stretch Database wizard

  4. В окне Учетные данные безопасности укажите пароль для главного ключа базы данных для обеспечения безопасности учетных данных исходной базы данных SQL Server и нажмите кнопку "Далее".In the Secure credentials window, provide a password for the database master key to secure your source SQL Server database credential and click Next.

    ![Страница "Учетные данные безопасности" мастера Stretch Database](../../relational-databases/tables/media/stretch-wizard-6.png "Страница "Учетные данные безопасности" мастера Stretch Database")Secure credentials page of the Stretch Database wizard

  5. В окне Выбор IP-адреса укажите диапазон IP-адресов для вашего сервера SQL Server, необходимый для взаимодействия сервера Azure с сервером SQL Server (если выбран существующий сервер, для которого уже существует правило брандмауэра, просто нажмите кнопку "Далее", чтобы использовать это правило).In the Select IP address window, provide the IP address range for your SQL Server to let your Azure server communicate with your SQL Server (if you select an existing server for which a firewall rule already exists, simply click Next here to use the existing firewall rule). Нажмите кнопку Далее, а затем — кнопку Готово, чтобы включить Stretch Database и выполнить растяжение темпоральной таблицы журнала.Click Next and then click Finish to enable Stretch Database and stretch the temporal history table.

    ![Страница "Выбор IP-адреса" мастера Stretch Database](../../relational-databases/tables/media/stretch-wizard-7.png "Страница "Выбор IP-адреса" мастера Stretch Database")Select IP address page of the Stretch Database wizard

  6. По завершении работы мастера убедитесь в том, что растяжение для вашей базы данных успешно включено.When the wizard completes, verify that your database was successfully stretch-enabled. Проверьте значки в обозревателе объектов. Они должны указывать, что для базы данных выполнено растяжение.Notice the icons in Object Explorer indicating the database was stretched.

Примечание

Если не удается разрешить растяжение для базы данных, просмотрите журнал ошибок.If the Enable Database for Stretch fails, review the error log. Распространенной ошибкой является неверная настройка правила брандмауэра.A common error is improperly configuring the firewall rule.

См. такжеSee also:

Использование Transact-SQL для растяжения всей таблицы журналаUsing Transact-SQL to stretch the entire history table

Вы также можете использовать Transact-SQL, чтобы включить растяжение на локальном сервере и включить растяжение для базы данных.You can also use Transact-SQL to enable Stretch on the local server and Enable Stretch Database for a database. После этого можно использовать Transact-SQL, чтобы разрешить Stretch Database в таблице.You can then use Transact-SQL to enable Stretch Database on a table. Используя базу данных, для которой уже была разрешена операция растяжения, выполните следующий сценарий Transact-SQL, позволяющий выполнить растяжение существующей таблицы журнала с системным управлением версиями:With a database previously enabled for Stretch Database, execute the following Transact-SQL script to stretch an existing system-versioned temporal history table:

ALTER TABLE <history table name>
SET (REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = OUTBOUND));

Использование Transact-SQL для растяжения части таблицы журналаUsing Transact-SQL to stretch a portion of the history table

Чтобы выполнить растяжение только части таблицы журнала, начните с создания встроенной функции предиката.To stretch only a portion of the history table, you start by creating an inline predicate function. Для примера предположим, что встроенная функция предиката была настроена в первый раз 1 декабря 2015 г. и требуется выполнить растяжение в Azure всех записей с датой журнала старше 1 ноября 2015 г.For this example, let's assume that you configured inline predicate function for the first time on December 1, 2015 and want to stretch to Azure all history date older than November 1, 2015. Чтобы сделать это, начнем с создания следующей функции:To accomplish this, start by creating the following function:

CREATE FUNCTION dbo.fn_StretchBySystemEndTime20151101(@systemEndTime datetime2)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS is_eligible
  WHERE @systemEndTime < CONVERT(datetime2, '2015-11-01T00:00:00', 101) ;

Далее используем следующий сценарий для добавления предиката фильтра в таблицу журнала и зададим для состояния перемещения значение OUTBOUND, чтобы включить перемещение данных для таблицы журнала на основе предиката.Next, use the following script to add the filter predicate to the history table and set the migration state to OUTBOUND to enable predicate based data migration for the history table.

ALTER TABLE <history table name>
SET (
      REMOTE_DATA_ARCHIVE = ON
        (
          FILTER_PREDICATE = dbo.fn_StretchBySystemEndTime20151101 (SysEndTime)
            , MIGRATION_STATE = OUTBOUND
        )
    )
;

Для обеспечения скользящего окна необходимо задать функцию предиката точно на каждый день (т. е. изменять условие фильтрации строк каждый день, прибавляя один день).To maintain a sliding window, you need to make predicate function to be accurate every day (i.e. change filtering row condition every day by one day). Приведенный далее скрипт должен выполняться 2 декабря 2015 г.:The following script is the script that you would you need to execute on December 2, 2015:

BEGIN TRAN
/*(1) Create new predicate function definition */
  CREATE FUNCTION dbo.fn_StretchBySystemEndTime20151102(@systemEndTime datetime2)
   RETURNS TABLE
    WITH SCHEMABINDING
      AS
        RETURN SELECT 1 AS is_eligible
          WHERE @systemEndTime < CONVERT(datetime2,'2015-11-02T00:00:00', 101)
  GO
 
/*(2) Set the new function as filter predicate */
  ALTER TABLE <history table name>
    SET
      (
        REMOTE_DATA_ARCHIVE = ON
          (
            FILTER_PREDICATE = dbo.fn_StretchBySystemEndTime20151102(SysEndTime),
              MIGRATION_STATE = OUTBOUND
          )
      )
COMMIT ;

Чтобы гарантировать правильность функции в любой день, используйте агент SQL Server или другой механизм планирования.Use SQL Server Agent or some other scheduling mechanism to ensure valid predicate function definition all the time.

Использование секционирования таблицUsing table partitioning approach

Секционирование таблиц может улучшить управляемость и масштабируемость больших таблиц.Table partitioning can make large tables more manageable and scalable. Используя секционирование таблиц журнала, можно реализовать пользовательскую очистку данных или архивирование в автономном режиме по условию времени.Using the table partitioning approach, you can use history table partitions to implement custom data cleanup or offline archival based on a time condition. Кроме того, секционирование таблиц обеспечивает выигрыш в производительности при выполнении запросов по темпоральным таблицам на подмножестве данных журнала путем устранения секций.Table partitioning will also give you performance benefits when querying temporal tables on a subset of data history by using partition elimination.

Секционирование таблиц позволяет реализовать принцип скользящего окна для перемещения более старой части данных журнала из таблицы журнала и поддержания размера сохраняемой части постоянным по критерию возраста. В таблице журнала сохраняются только те данные, срок которых равен заданному сроку хранения.With table partitioning, you can implement a sliding window approach to move out oldest portion of the historical data from the history table and keep the size of the retained part constant in terms of age - maintaining data in the history table equal to required retention period. Операция по перемещению данных из таблицы журнала поддерживается, если для параметра SYSTEM_VERSIONING задано значение ON. Это означает, что можно очистить часть данных журнала без введения периода обслуживания или блокировки регулярных рабочих нагрузок.The operation of switching data out from the history table is supported while SYSTEM_VERSIONING is ON, which means that you can clean a portion of the history data without introducing a maintenance windows or blocking your regular workloads.

Примечание

Чтобы можно было выполнить переключение секций, кластеризованный индекс в таблице журнала должен быть согласован со схемой секционирования. (Она должна содержать SysEndTime.)In order to perform partition switching, your clustered index on history table must be aligned with the partitioning schema (it has to contain SysEndTime). Таблица журнала по умолчанию, создаваемая системой, содержит кластеризованный индекс, который включает в себя столбцы SysEndTime и SysStartTime, что оптимально для секционирования, вставки новых данных журнала и выполнения типичных темпоральных запросов.The default history table created by the system contains a clustered index that includes the SysEndTime and SysStartTime columns, which is optimal for partitioning, inserting new history data, and typical temporal querying. Дополнительные сведения см. в разделе Temporal Tables.For more information, see Temporal Tables.

Использование скользящего окна предполагает выполнение двух наборов задач.A sliding window approach has two sets of tasks that you need to perform:

  • Задачи настройки секционированияA partitioning configuration task
  • Повторяющиеся задачи обслуживания секцийRecurring partition maintenance tasks

Для иллюстрации предположим, что нам нужно хранить данные журнала в течение 6 месяцев и что мы хотим сохранять каждый месяц данных в отдельной секции.For the illustration, let's assume that we want to keep historical data for 6 months and that we want to keep every month of data in a separate partition. Кроме того, предположим, что мы активировали системное управление версиями в сентябре 2015 г.Also, let's assume that we activated system-versioning in September of 2015.

При настройке секционирования создается начальная конфигурация секционирования для таблицы журнала.A partitioning configuration task creates the initial partitioning configuration for the history table. В этом примере мы создадим секции в количестве, равном размеру скользящего окна в месяцах, и еще одну дополнительную пустую предварительно подготовленную секцию (о ней будет сказано ниже).For this example, we would create the same number partitions as the size of sliding window, in months, plus one additional empty partition pre-prepared (explained below). Эта конфигурация гарантирует, что система будет правильно сохранять новые данные при первом запуске повторяющейся задачи обслуживания секции, а также что никогда не произойдет разбиение секций с данными, а значит, не потребуется перемещение данных, на которое может потребоваться много времени.This configuration ensures that the system will be able to store new data correctly when we start the recurring partition maintenance task for the first time and guarantees that we never split partitions with data to avoid expensive data movements. Эту задачу следует выполнить с помощью службы Transact-SQL, используя пример сценария, приведенный ниже.You should perform this task using Transact-SQL using the example script below.

На следующем рисунке показана начальная конфигурация секционирования для сохранения данных за 6 месяцев.The following picture shows initial partitioning configuration to keep 6 months of data.

СекционированиеPartitioning

Примечание

При настройке секционирования изучите вопросы, связанные с производительностью, чтобы понять, как влияет на производительность использование параметров RANGE LEFT и RANGE RIGHT.See Performance considerations with table partitioning below for the performance implications of using RANGE LEFT versus RANGE RIGHT when configuring partitioning.

Первая и последняя секции "открыты" на верхней и нижней границах соответственно, чтобы обеспечить секцию назначения для каждой новой строки независимо от значения в столбце секционирования.The first and last partition are "open" on lower and upper boundaries respectively to ensure that every new row has destination partition regardless of the value in partitioning column. Со временем новые строки в таблице журнала будут перемещаться в более высокие секции.As time goes by, new rows in history table will land in higher partitions. Когда будет заполнена шестая секция, будет достигнут заданный срок хранения.When 6th partition gets filled up, we will have reached the targeted retention period. Это тот момент, когда в первый раз должна быть запущена повторяющаяся задача обслуживания секций. (В этом примере должен быть запланирован периодический запуск задачи один раз в месяц.)This is the moment to start the recurring partition maintenance task for the first time (it needs to be scheduled to run periodically, once per month in this example).

На следующем рисунке представлены повторяющиеся задачи обслуживания секций (см. инструкции ниже).The following picture illustrates the recurring partition maintenance tasks (see detailed steps below).

Секционирование2Partitioning2

Подробные инструкции для повторяющихся задач обслуживания секций.The detailed steps for the recurring partition maintenance tasks are:

  1. ОТКЛЮЧЕНИЕ. Создайте промежуточную таблицу, а затем переключите секцию между таблицей журнала и промежуточной таблицей с помощью инструкции ALTER TABLE (Transact-SQL) с параметром SWITCH PARTITION (см. "Пример В. Переключение секций между таблицами").SWITCH OUT: Create a staging table and then switch a partition between the history table and the staging table using the ALTER TABLE (Transact-SQL) statement with the SWITCH PARTITION argument (see Example C. Switching partitions between tables).

    ALTER TABLE <history table> SWITCH PARTITION 1 TO <staging table>
    

    После переключения секций можно, при необходимости, архивировать данные из промежуточной таблицы, а затем удалить или сделать усечение промежуточной таблицы, чтобы она была готова к следующему выполнению этой повторяющейся задачи обслуживания секций.After the partition switch, you can optionally archive the data from staging table and then either drop or truncate the staging table to be ready for the next time you need to perform this recurring partition maintenance task.

  2. СЛИЯНИЕ ДИАПАЗОНА. Выполните слияние пустой секции 1 с секцией 2, используя инструкцию ALTER PARTITION FUNCTION (Transact-SQL) с параметром MERGE RANGE (см. пример Б).MERGE RANGE: Merge the empty partition 1 with partition 2 using the ALTER PARTITION FUNCTION (Transact-SQL) with MERGE RANGE (See example B). При удалении нижней границы с использованием этой функции происходит, по сути, слияние пустой секции 1 с бывшей секцией 2 и образование новой секции 1.By removing the lowest boundary using this function, you effectively merge the empty partition 1 with the former partition 2 to form new partition 1. Порядковые номера других секций также изменяются.The other partitions also effectively change their ordinals.

  3. РАЗБИЕНИЕ ДИАПАЗОНА. Создайте пустую секцию 7, используя инструкцию ALTER PARTITION FUNCTION (Transact-SQL) с параметром SPLIT RANGE (см. пример A).SPLIT RANGE: Create a new empty partition 7 using the ALTER PARTITION FUNCTION (Transact-SQL) with SPLIT RANGE (See example A). При добавлении верхней границы с использованием этой функции происходит по сути создание отдельной секции для предстоящего месяца.By adding a new upper boundary using this function, you effectively create a separate partition for the upcoming month.

Использование службы Transact-SQL для создания секций в таблице журналаUse Transact-SQL to create partitions on history table

Используйте сценарий Transact-SQL, приведенный в окне кода ниже, для создания функции секционирования, схемы секционирования и повторного создания кластеризованного индекса для согласования секций со схемой секционирования.Use the Transact-SQL script in the code window below to create the partition function, the partition schema, and recreate the clustered index to be partition-aligned with the partition schema, partitions. В этом примере будет создано скользящее окно размером шесть месяцев с ежемесячными секциями, начиная с сентября 2015 г.For this example, we will creating a six-month sliding window approach with monthly partitions beginning September, 2015.

BEGIN TRANSACTION
/*Create partition function*/
    CREATE PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_SysEndTime] (datetime2(7))
        AS RANGE LEFT FOR VALUES
          (
            N'2015-09-30T23:59:59.999'
          , N'2015-10-31T23:59:59.999'
          , N'2015-11-30T23:59:59.999'
          , N'2015-12-31T23:59:59.999'
          , N'2016-01-31T23:59:59.999'
          , N'2016-02-29T23:59:59.999'
          )
/*Create partition scheme*/
    CREATE PARTITION SCHEME [sch_Partition_DepartmentHistory_By_SysEndTime]
        AS PARTITION [fn_Partition_DepartmentHistory_By_SysEndTime]
            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]
        (
            [SysEndTime] ASC
          , [SysStartTime] 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_SysEndTime] ([SysEndTime])

COMMIT TRANSACTION;

Использование службы Transact-SQL для сохранения секций в сценарии со скользящим окномUsing Transact-SQL to maintain partitions in sliding window scenario

Используйте сценарий Transact-SQL, приведенный в окне кода ниже, для поддержания секций для случая скользящего окна.Use the Transact-SQL script in the code window below to maintain partitions in the sliding window scenario. В этом примере мы отключим секцию для сентября 2015 г., используя инструкцию MERGE RANGE, а затем добавим новую секцию для марта 2016 г., используя инструкцию SPLIT RANGE.For this example, we will switch out the partition for September of 2015 using MERGE RANGE and then add a new partition for March of 2016 using SPLIT RANGE.

BEGIN TRANSACTION
/*(1) Create staging table */
    CREATE TABLE [dbo].[staging_DepartmentHistory_September_2015]
        (
            [DeptID] [int] NOT NULL
          , [DeptName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
          , [ManagerID] [int] NULL
          , [ParentDeptID] [int] NULL
          , [SysStartTime] [datetime2](7) NOT NULL
          , [SysEndTime] [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_2015]
        ON [dbo].[staging_DepartmentHistory_September_2015]
        (
            [SysEndTime] ASC
          , [SysStartTime] 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_2015] WITH CHECK
        ADD CONSTRAINT [chk_staging_DepartmentHistory_September_2015_partition_1]
            CHECK ([SysEndTime]<=N'2015-09-30T23:59:59.999')
    ALTER TABLE [dbo].[staging_DepartmentHistory_September_2015]
        CHECK CONSTRAINT [chk_staging_DepartmentHistory_September_2015_partition_1]
/*(4) Switch partition to staging table*/
    ALTER TABLE [dbo].[DepartmentHistory]
        SWITCH PARTITION 1 TO [dbo].[staging_DepartmentHistory_September_2015]
        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_2015];
      DROP TABLE [dbo].[staging_DepartmentHIstory_September_2015];
*/
/*(6) merge range to move lower boundary one month ahead*/
    ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_SysEndTime]()
        MERGE RANGE(N'2015-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_SysEndTime] NEXT USED [PRIMARY]
        ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_SysEndTime]() SPLIT RANGE(N'2016-03-31T23:59:59.999')
COMMIT TRANSACTION

Можно немного изменить сценарий, приведенный выше, и использовать его в регулярной ежемесячной процедуре обслуживания:You can slightly modify script above and use it in regular monthly maintenance process:

  1. На шаге (1) создадим новую промежуточную таблицу для месяца, который нужно удалить (в нашем примере следующим будет октябрь).In step (1) create new staging table for the month you want to remove (October would be next one in our example).
  2. В шаге (3) создадим и проверим ограничение, соответствующее месяцу данных, который нужно удалить: [SysEndTime]<=N'2015-10-31T23:59:59.999' для секции октября.In step (3) create and check constraint that matches the month of data you want to remove: [SysEndTime]<=N'2015-10-31T23:59:59.999' for October partition.
  3. В шаге (4) выполним операцию SWITCH для секции 1 и переключим ее на вновь созданную промежуточную таблицу.In step (4) SWITCH partition 1 to newly created staging table.
  4. В шаге (6) изменим функцию секционирования путем слияния нижней границы: MERGE RANGE(N'2015-10-31T23:59:59.999' после перемещения данных за октябрь.In step (6) alter partition function by merging lower boundary: MERGE RANGE(N'2015-10-31T23:59:59.999' after you moved out data for October.
  5. В шаге (7) разобьем секцию, создав новую верхнюю границу: SPLIT RANGE (N'2016-04-30T23:59:59.999' после перемещения данных за октябрь.In step (7) split partition function creating new upper boundary: SPLIT RANGE (N'2016-04-30T23:59:59.999' after you moved out data for October.

Тем не менее оптимальным решением будет регулярно запускать типовой сценарий Transact-SQL, который может выполнять необходимые действия каждый месяц без изменения сценария.However, the optimal solution would be to regularly run a generic Transact-SQL script that is a capable of performing the appropriate action every month without script modification. Можно обобщить описанный сценарий, введя обработку переданных параметров (нижней границы, которую требуется объединить, и новой границы, создаваемой путем разбиения секции).It is possible to generalize the script above to act upon provided parameters (lower boundary that needs to be merged and new boundary that will be created by with partition split). Чтобы не создавать промежуточную таблицу для каждого месяца, можно создать одну таблицу заранее и повторно использовать ее, изменяя проверочное ограничение в соответствии с той секцией, которая будет выключена. Просмотрите следующие страницы, на которых вы можете найти подсказки о том, как можно полностью автоматизировать скользящее окно с помощью скрипта Transact-SQL.In order to avoid staging table creation every month, you can create one beforehand and reuse by changing check constraint to match partition that will be switched out. Take a look at the following pages to get ideas on how sliding window can be fully automated using a Transact-SQL script.

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

Очень важно выполнять операции MERGE и SPLIT RANGE так, чтобы избегать перемещения данных, так как перемещение данных может привести к значительному снижению производительности.It is important to perform the MERGE and SPLIT RANGE operations to avoid any data movement as data movement can incur significant performance overhead. Дополнительные сведения см. в разделе Изменение функции секционирования. Это достигается скорее с помощью параметра RANGE LEFT, чем параметра RANGE RIGHT, когда вы используете инструкцию CREATE PARTITION FUNCTION (Transact-SQL).For more information, see Modify a Partition Function.You accomplish this by using RANGE LEFT rather than RANGE RIGHT when you CREATE PARTITION FUNCTION (Transact-SQL).

Давайте сначала визуально продемонстрируем смысл параметров RANGE LEFT и RANGE RIGHT:Let's first visually explain meaning of the RANGE LEFT and RANGE RIGHT options:

Секционирование3Partitioning3

При определении функции секционирования как RANGE LEFT указанные значения являются верхними границами секций.When you define a partition function as RANGE LEFT, the specified values are the upper boundaries of the partitions. При использовании параметра RANGE RIGHT указанные значения являются нижними границами секций.When you use RANGE RIGHT, the specified values are the lower boundaries of the partitions. При использовании операции MERGE RANGE для удаления границы из определения функции секционирования базовая реализация удаляет также секцию, которая содержит эту границу.When you use the MERGE RANGE operation to remove a boundary from the partition function definition, the underlying implementation also removes the partition which contains the boundary. Если эта секция не пуста, данные будут перемещены в секцию, которая является результатом операции MERGE RANGE.If that partition is not empty, data will be moved to the partition that is result of MERGE RANGE operation.

В сценарии со скользящим окном мы всегда удаляем самую нижнюю границу секции.In sliding window scenario, we always remove lowest partition boundary.

  • Случай RANGE LEFT. В случае параметра RANGE LEFT самая нижняя граница секции принадлежит секции 1, которая становится пустой (после отключения секции), поэтому операция MERGE RANGE не приводит к перемещению данных.RANGE LEFT case: In RANGE LEFT case, the lowest partition boundary belongs to partition 1, which is empty (after partition switch out), so MERGE RANGE won't incur any data movement.
  • Случай RANGE RIGHT. В случае параметра RANGE RIGHT самая нижняя граница секции принадлежит секции 2, которая не является пустой, так как предполагается, что при отключении была очищена секция 1. В этом случае операция MERGE RANGE приведет к перемещению данных. (Данные из секции 2 будут перемещены в секцию 1.)RANGE RIGHT case: In RANGE RIGHT case, the lowest partition boundary belongs to partition 2, which is not empty as we assumed that partition 1 was emptied by switch out. In this case MERGE RANGE will incur data movement (data from partition 2 will be moved to partition 1). Чтобы избежать этого, при применении параметра RANGE RIGHT в сценарии со скользящим окном необходимо, чтобы секция 1 была всегда пуста.To avoid this, RANGE RIGHT in the sliding window scenario needs to have partition 1, which is always empty. Это означает, что, если используется параметр RANGE RIGHT, необходимо создавать и поддерживать одну дополнительную секцию, чего не нужно делать в случае параметра RANGE LEFT.This means that if we use RANGE RIGHT, we should create and maintain one additional partition compared to RANGE LEFT case.

Заключение. Использование параметра RANGE LEFT при скользящем секционировании значительно упрощает управление секциями и позволяет избежать перемещения данных.Conclusion: Using RANGE LEFT in sliding partition is much simpler for the partition management and avoids data movement. Однако определение границ секционирования с использованием параметра RANGE RIGHT несколько проще, так как при этом не требуется решать вопросы, связанные с течением времени.However, defining partition boundaries with RANGE RIGHT is slightly simpler as you don't have to deal with datetime time tick issues.

Использование пользовательского сценария очисткиUsing custom cleanup script approach

В тех случаях, когда подход Stretch Database или секционирование таблиц неприемлемо, можно применить третий способ удаления данных из таблицы журнала с помощью пользовательского сценария очистки.In cases when the Stretch Database and table partitioning approaches are not viable options, the third approach is to delete the data from history table using a custom cleanup script. Удаление данных из таблицы журнала возможно, только если SYSTEM_VERSIONING = OFF.Deleting data from history table is possible only when SYSTEM_VERSIONING = OFF. Чтобы избежать несогласованности данных, необходимо выполнять очистку в течение периода обслуживания (когда рабочие нагрузки, изменяющие данные, неактивны) или в рамках одной транзакции (на время которой блокируются другие рабочие нагрузки).In order to avoid data inconsistency, perform cleanup either during the maintenance window (when workloads that modify data are not active) or within a transaction (effectively blocking other workloads). Для выполнения этой операции требуется разрешение CONTROL для текущей таблицы и таблицы журнала.This operation requires CONTROL permission on current and history tables.

При выполнении сценария очистки внутри транзакции удаляйте данные небольшими фрагментами с задержкой, чтобы не создавать помеху для работы обычных приложений и запросов пользователей.To minimally block regular applications and user queries, delete data in smaller chunks with a delay when performing the cleanup script inside a transaction. Не существует единой рекомендации по оптимальному размеру фрагмента удаляемых данных для всех сценариев, но в любом случае удаление более чем 10 000 строк в одной транзакции может оказать существенное влияние на работу.While there is no optimal size for each data chunk to be deleted for all scenarios, deleting more than 10,000 rows in a single transaction may impose a significant impact.

Для всех темпоральных таблиц используется одна и та же логика очистки. Поэтому можно сравнительно легко автоматизировать весь процесс с помощью универсальной хранимой процедуры, запускаемой периодически для каждой темпоральной таблицы, для которой требуется ограничить данные журнала.The cleanup logic is the same for every temporal table, so it can be automated relatively easily through a generic stored procedure that you schedule to run periodically for every temporal table for which you want to limit data history.

На следующей схеме показано, как должна быть организована логика очистки для одиночной таблицы, чтобы снизить влияние на выполнение рабочих нагрузок.The following diagram illustrates how your cleanup logic should be organized for a single table to reduce impact on the running workloads.

CustomCleanUpScriptDiagramCustomCleanUpScriptDiagram

Ниже приведены общие рекомендации по реализации процесса.Here are some high-level guidelines for implementing the process. Составьте план ежедневного выполнения очистки с перебором всех темпоральных таблиц, для которых требуется очистка данных.Schedule cleanup logic to run every day and iterate over all temporal tables that need data cleanup. Для планирования этого процесса используйте Агент SQL Server или другое средство:Use SQL Server Agent or different tool to schedule this process:

  • Удаляйте данные журнала из каждой темпоральной таблицы, начиная с самой старой строки и до последней, в несколько итераций небольшими фрагментами. Не удаляйте все строки в одной транзакции. Процесс показан на рисунке, приведенном выше.Delete historical data in every temporal table starting from the oldest to the most recent rows in several iterations in small chunks and avoid deleting all rows in a single transaction as shown on the picture above.
  • Реализуйте каждую итерацию как вызов универсальной хранимой процедуры, удаляющей часть данных из таблицы журнала. (См. пример кода для выполнения этой процедуры ниже.)Implement every iteration as an invocation of generic stored procedure that removes a portion of data from the history table (see code example below for this procedure).
  • Подсчитайте, сколько строк необходимо удалять из одной темпоральной таблицы при каждом запуске процесса.Calculate how many rows you need to delete for an individual temporal table every time you invoke the process. Зная это число и требуемое количество итераций, динамически определите точки разбиения для каждого вызова процедуры.Based on that and number of number of iterations you want to have, determine dynamic split points for every procedure invocation.
  • Задайте периоды задержки между итерациями по одной таблице, чтобы уменьшить помеху для работы приложений, обращающихся к этой темпоральной таблице.Plan to have a period of delay between iterations for a single table to reduce impact on applications that access the temporal table.

Пример хранимой процедуры, удаляющей данные из одной темпоральной таблицы, приведен в следующем фрагменте кода. (Внимательно просмотрите этот код и внесите необходимые поправки прежде, чем применять его в вашей среде.)A stored procedure that deletes the data for a single temporal table might look like in the following code snippet (review this code carefully and adjust it before apply in your environment):

DROP PROCEDURE IF EXISTS sp_CleanupHistoryData;
GO

CREATE PROCEDURE sp_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 = s.name, @period_col_nm = c.name
        FROM sys.tables t1
            JOIN sys.tables t2 on t1.history_table_id = t2.object_id
        JOIN sys.schemas s on t2.schema_id = s.schema_id
            JOIN sys.periods p on p.object_id = t1.object_id
           JOIN sys.columns c on p.end_column_id = c.column_id and c.object_id = t1.object_id
                  WHERE
                 t1.name = @tblName and s.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 will 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 will generate 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 TRAN
    EXEC (@disableVersioningScript);
    EXEC (@deleteHistoryDataScript);
    EXEC (@enableVersioningScript);
COMMIT;

Применение подхода с использованием политики хранения темпоральных журналовUsing temporal history retention policy approach

Примечание

Подход с использованием политики хранения темпоральных журналов может применяться к База данных SQLSQL Database с SQL Server 2017 начиная с CTP 1.3.Using the Temporal History Retention Policy approach applies to База данных SQLSQL Database and SQL Server 2017 starting from CTP 1.3.

Хранение темпоральных журналов можно настроить на уровне отдельных таблиц, что позволит пользователям создавать гибкие политики устаревания.Temporal history retention can be configured at the individual table level, which allows users to create flexible aging polices. Применять темпоральные журналы легко: для настройки во время создания таблицы или изменения схемы требуется только один параметр.Applying temporal retention is simple: it requires only one parameter to be set during table creation or schema change.

Как только политика хранения определена, база данных Azure SQL начинает регулярно проверять, подходят ли исторические строки для автоматической очистки данных.After you define retention policy, Azure SQL Database starts checking regularly if there are historical rows that are eligible for automatic data cleanup. Идентификация совпадающих строк и их удаление из таблицы журналов осуществляется прозрачно, в рамках фоновой задачи, которая планируются и выполняются системой.Identification of matching rows and their removal from the history table occur transparently, in the background task that is scheduled and run by the system. Условие устаревания строк в таблицах журналов проверяется по столбцу, представляющему окончание периода SYSTEM_TIME.Age condition for the history table rows is checked based on the column representing end of SYSTEM_TIME period. Например, если срок хранения составляет шесть месяцев, для очистки подходят строки, отвечающие следующему условию:If retention period, for example, is set to six months, table rows eligible for cleanup satisfy the following condition:

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

В предыдущем примере предполагалось, что столбец ValidTo соответствует окончанию периода SYSTEM_TIME.In the preceding example, we assumed that ValidTo column corresponds to the end of SYSTEM_TIME period.

Настройка политики храненияHow to configure retention policy

Прежде чем настраивать политику хранения для темпоральной таблицы, проверьте, включено ли временное хранение журналов на уровне базы данных:Before you configure retention policy for a temporal table, check first whether temporal historical retention is enabled at the database level:

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases

Флаг базы данных is_temporal_history_retention_enabled по умолчанию установлен, однако пользователи могут его изменить с помощью инструкции ALTER DATABASE.Database flag is_temporal_history_retention_enabled is set to ON by default, but users can change it with ALTER DATABASE statement. Кроме того, флаг автоматически снимается после операции восстановления на определенный момент времени.It is also automatically set to OFF after point in time restore operation. Чтобы включить очистку хранения журналов для базы данных, выполните следующую инструкцию:To enable temporal history retention cleanup for your database, execute the following statement:

ALTER DATABASE <myDB>
SET TEMPORAL_HISTORY_RETENTION ON

Политика хранения настраивается во время создания таблицы путем указания значения для параметра HISTORY_RETENTION_PERIOD:Retention policy is configured during table creation by specifying value for the HISTORY_RETENTION_PERIOD parameter:

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

Период хранения можно указать в различных единицах времени: днях (DAYS), неделях (WEEKS), месяцах (MONTHS) и годах (YEARS).You can specify retention period by using different time units: DAYS, WEEKS, MONTHS, and YEARS. Если параметр HISTORY_RETENTION_PERIOD опущен, это означает, что срок хранения НЕ ОГРАНИЧЕН (INFINITE).If HISTORY_RETENTION_PERIOD is omitted, INFINITE retention is assumed. Кроме того, ключевое слово INFINITE можно использовать явным образом.You can also use INFINITE keyword explicitly. В некоторых сценариях настройка хранения может потребоваться после создания таблицы либо для того, чтобы изменить значение, заданное ранее.In some scenarios, you may want to configure retention after table creation, or to change previously configured value. В этом случае используйте инструкцию ALTER TABLE:In that case use ALTER TABLE statement:

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

Чтобы узнать текущее состояние политики хранения, выполните указанный ниже запрос, объединяющий флаг включения временного хранения на уровне базы данных со сроками хранения для отдельных таблиц:To review current state of the retention policy, use the following query that joins temporal retention enablement flag at the database level with retention periods for individual tables:

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 удаляет устаревшие строки?How SQL Database deletes aged rows

Процесс очистки зависит от макета индекса таблицы журналов.The cleanup process depends on the index layout of the history table. Следует отметить, что ограниченный срок хранения можно настроить только для таблиц журналов с кластеризованным индексом (columnstore или сбалансированным деревом) .It is important to notice that only history tables with a clustered index (B-tree or columnstore) can have finite retention policy configured. Для очистки устаревших данных во всех темпоральных таблицах с ограниченным периодом хранения создается фоновая задача.A background task is created to perform aged data cleanup for all temporal tables with finite retention period. Логика очистки для кластеризованного индекса rowstore (сбалансированное дерево) удаляет устаревшие строки небольшими фрагментами (до 10 тыс. строк), что позволяет минимизировать нагрузку на журнал базы данных и подсистему ввода-вывода.Cleanup logic for the rowstore (B-tree) clustered index deletes aged rows in smaller chunks (up to 10K) minimizing pressure on database log and I/O subsystem. Несмотря на то что в логике очистки используется индекс сбалансированного дерева, порядок удаления строк, возраст которых превышает срок хранения, может не соблюдаться.Although cleanup logic utilizes required B-tree index, order of deletions for the rows older than retention period cannot be firmly guaranteed. В связи с этим не включайте в приложения никакие зависимости от порядка очистки.Hence, do not take any dependency on the cleanup order in your applications.

Задача очистки для кластеризованного индекса columnstore удаляет всю группу строк за раз (обычно каждая такая группа содержит по миллиону строк) — это очень эффективно, особенно в случаях, когда исторические данные создаются быстрыми темпами.The cleanup task for the clustered columnstore removes entire row groups at once (typically contain 1 million of rows each), which is very efficient, especially when historical data is generated at a high pace.

Кластеризованный период удержания columnstoreClustered columnstore retention

Превосходное сжатие данных и эффективная очистка хранилища делают кластеризованный индекс columnstore наиболее подходящим для случаев, когда рабочая нагрузка вызывает быстрое образование большого количества исторических данных.Excellent data compression and efficient retention cleanup makes clustered columnstore index a perfect choice for scenarios when your workload rapidly generates high amount of historical data. Подобная ситуация типична для интенсивных рабочих нагрузок по обработке транзакций, в которых темпоральные таблицы используются для контроля и аудита изменений, анализа тенденций и приема данных Интернета вещей.That pattern is typical for intensive transactional processing workloads that use temporal tables for change tracking and auditing, trend analysis, or IoT data ingestion.

Дополнительные сведения см. в статье Управление историческими данными в темпоральных таблицах с политикой хранения.Please check Manage historical data in Temporal Tables with retention policy for more details.

Дальнейшие действияNext steps