システム バージョン管理されたテンポラル テーブルの履歴データの保有期間管理Manage Retention of Historical Data in System-Versioned Temporal Tables

このトピックの対象: ○SQL Server (2016 以降)○Azure SQL Database×Azure SQL Data Warehouse ×Parallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

テンポラル テーブルがシステム バージョン管理されているとき、履歴データによりデータベースのサイズが通常のテーブルより増えることがあります。特に、次の条件下で当てはまります。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. 次の 4 つの手法で、一時的な履歴テーブルの履歴データを管理できます。The following four approaches for managing historical data in the temporal history table are available:

  • Stretch DatabaseStretch Database

  • テーブル パーティション分割Table Partitioning

  • カスタム クリーンアップ スクリプトCustom Cleanup Script

  • 保持ポリシーRetention Policy

    いずれの手法でも、履歴データを移行またはクリーンアップするためのロジックは、現在のテーブルの期間終了に相当する列に基づきます。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 ()) では、1 か月以上経過した履歴データは履歴テーブルから削除されます。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.

注: このトピックの例では、この テンポラル テーブル例を使用しています。NOTE: The examples in this topic use this Temporal Table example.

Stretch Database 手法の利用Using Stretch Database approach

注: Stretch Database 手法の使用は、 SQL Server 2017SQL Server 2017 にのみ適用され、 SQL データベースSQL Databaseには適用されません。NOTE: Using the Stretch Database approach only applies to SQL Server 2017SQL Server 2017 and does not apply to SQL データベースSQL Database.

Stretch DatabaseSQL Server 2017SQL Server 2017 では、履歴データが Azure に透過的に移行されます。Stretch Database in SQL Server 2017SQL Server 2017 migrates your historical data transparently to Azure. セキュリティ強化のために、SQL Server の Always Encrypted 機能で移行中のデータを暗号化できます。For additional security, you can encrypt data in motion using SQL Server's Always Encrypted feature. また、 行レベルのセキュリティ やその他の高度な SQL Server セキュリティ機能を Temporal/Stretch Database と共に使用し、データを保護できます。Additionally, you can use Row-Level Security and other advanced SQL Server security features with Temporal and Stretch Database to protect your data.

Stretch Database 手法の利用では、一時的な履歴テーブルの一部または全部を 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-enabling a history table does not change how you interact with the temporal table in terms of data modification and temporal querying.

  • 履歴テーブル全体をストレッチ: 主なシナリオがデータ監査であり、データを頻繁に変更するが、履歴データにクエリを実行することは比較的まれであれば、履歴テーブル全体に Stretch Database を構成します。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.
    履歴テーブル全体をストレッチするとき、Stretch ウィザードまたは Transact-SQL を利用できます。When stretching the entire history table, you can either use the Stretch Wizard or Transact-SQL. 両方の例を以下に示します。Examples of both appear below.

  • 履歴テーブルの一部をストレッチ: 主なシナリオが最近の履歴データにクエリを実行することであるが、必要なときに古い履歴データにもクエリを実行できて、しかも、そのデータを離れた場所に低いコストで保管する場合、履歴テーブルの一部にのみ Stretch Database を構成し、性能を上げます。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 Database)」を参照してください。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. スライディング ウィンドウでは、1 か月以上経過した履歴データを 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 に移行します。NOTE: Stretch Database migrates data to Azure. そのため、Azure アカウントとサブスクリプションを請求のために用意する必要があります。Therefore, you have to have an Azure account and a subscription for billing. 無料の試用版 Azure アカウントを入手する場合ハードウェア、 1 か月間の無料試用版をクリックしてください。To get a free trial Azure account, click Free One-Month Trial.

一時的な履歴テーブルの Stretch は Stretch ウィザードまたは Transact-SQL で構成できます。システム バージョン管理を オンに設定しているとき、一時的な履歴テーブルのストレッチを有効にできます。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.

Stretch ウィザードを利用し、履歴テーブル全体をストレッチするUsing the Stretch Wizard to stretch the entire history table

初心者にとって最も簡単な方法は、Stretch ウィザードを利用して、データベース全体でストレッチを有効にし、Stretch ウィザード内で一時的な履歴テーブルを選択することです (この例では、本来であれば空のデータベースで、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)では一時的な履歴テーブル自体を右クリックし、[Stretch] をクリックすることはできません。In SQL Server 2016 (13.x)SQL Server 2016 (13.x), you cannot right-click the temporal history table itself and click Stretch.

  1. データベースを右クリックし、 [タスク] をポイントし、 [Stretch] をポイントします。それから、 [有効化] をクリックしてウィザードを起動します。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.

    [テーブルの選択] ページで履歴テーブルを選択する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 Database ウィザードCreate 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 ウィザードの [セキュリティで保護された資格情報] ページSecure credentials page of the Stretch Database wizard

  5. [IP アドレスの選択] ウィンドウで、Azure サーバーで SQL Server と通信するために、SQL Server の IP アドレス範囲を入力します (ファイアウォール ルールが既に設定されている既存のサーバーを選択する場合、ここで [次へ] をクリックし、既存のファイアウォール ルールを使用します)。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.

    Stretch Database ウィザードの [IP アドレスの選択] ページ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

注: データベースのストレッチを有効化できなかった場合は、エラー ログを確認してください。NOTE: 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 を使用して、ローカル サーバー上で Stretch を有効にすることや、 データベースに対して Stretch Database を有効にすることもできます。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. 以前に、データベースの Stretch Database を有効にした場合、次の 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. たとえば、2015 年 12 月 1 日に初めてインライン述語関数を構成していて、2015 年 11 月 1 日より前のすべての履歴データを Azure にストレッチすると想定します。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   
                )  
        )   
;  

スライディング ウィンドウを維持するには、毎日、述語関数を正確にする必要があります (毎日、行のフィルター処理条件を 1 日単位で変更します)。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). 次のスクリプトは、2015 年 12 月 2 日に実行する必要があるスクリプトです。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 Agent またはその他のスケジュール作成メカニズムを利用します。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 がオンになっているときにサポートされます。つまり、保守管理ウィンドウを導入したり、通常の作業負荷をブロックしたりしなくても履歴データの一部をクリーンアップできます。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 が含まれている必要があります)。NOTE: 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.

スライディング ウィンドウ手法は 2 セットのタスクからなり、ユーザーがそれを実行する必要があります。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 年 9 月にシステムのバージョン管理を有効にすると仮定します。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 を使用する場合のパフォーマンス上の違いについては、下の「テーブル パーティション分割におけるパフォーマンス上の考慮事項」を参照してください。NOTE: See Performance considerations with table partitioning below for the performance implications of using RANGE LEFT versus RANGE RIGHT when configuring partitioning.

最初と最後のパーティションがそれぞれ、下と上の境界で “オープン” になっており、パーティション分割列の値に関係なく、すべての新しい行に対象パーティションが与えられます。Note that 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. 6 番目のパーティションがいっぱいになると、目標とした保有期間に到達したことになります。When 6th partition gets filled up, we will have reached the targeted retention period. その瞬間、定期 (繰り返し) パーティション保守管理タスクが初めて始まります (定期的に実行するようにスケジュールする必要があり、この例では月 1 回になっています)。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. SWITCH OUT: ステージング テーブルを作成し、ALTER TABLE (Transact-SQL) ステートメントと SWITCH PARTITION 引数を利用し、履歴テーブルとステージング テーブルの間でパーティションを切り替えます (「例 C. テーブル間のパーティションの切り替え」を参照してください)。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. MERGE RANGE: ALTER PARTITION FUNCTION (Transact-SQL) と MERGE RANGE を利用し、空のパーティション 1 とパーティション 2 をマージします (例 B を参照)。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. SPLIT RANGE: ALTER PARTITION FUNCTION (Transact-SQL) と SPLIT RANGE を利用し、新しい空のパーティション 7 を作成します (例 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 年 9 月に開始する 6 か月のスライディング ウィンドウ手法を作成します。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. この例では、MERGE RANGE を利用して 2015 年 9 月にパーティションをスイッチ アウトし、SPLIT RANGE を利用して 2016 年 3 月に新しいパーティションを追加します。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 [ox_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) では、削除する月に新しいステージング テーブルを作成します (例では、10 月が次の月になります)。In step (1) create new staging table for the month you want to remove (October would be next one in our example)

  2. 手順 (3) では、削除するデータの月に一致する制約を作成し、確認します。10 月のパーティションの [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) では、パーティション 1 を新しく作成したステージング テーブルに切り替えます。In step (4) SWITCH partition 1 to newly created staging table

  4. 手順 (6) では、10 月のデータを移した後に、下位の境界を結合し、パーティション関数を変更します。 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) では、10 月のデータを移した後に、上位の境界を新しく作成し、パーティション関数を分割します。 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). 毎月のステージング テーブル作成を避けるために、1 つのステージング テーブルを事前に作成し、スイッチ アウトされるパーティションに一致するようにチェック制約を変更して再利用できます。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. 詳細については、「パーティション関数の変更」を参照してください。これは、CREATE PARTITION FUNCTION (Transact-SQL) の実行時に、RANGE RIGHT ではなく、RANGE LEFT で実現します。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 場合と比較して 1 つ追加でパーティションを作成し、保守管理する必要があります。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 とテーブル パーティション分割を利用する手法が実行できない場合、3 番目の手法は、カスタム クリーンアップ スクリプトを利用して履歴テーブルからデータを削除することになります。In cases when the Stretch Database and table partitioning approached are not viable options, the third approach is to delete the data from history table using the 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. このデータのまとまりのサイズについては、あらゆるシナリオに適用できる最適なサイズというものはありませんが、1 回のトランザクションで 10,000 行以上を削除すると、大幅な影響が現れる可能性があります。While there is no optimal size of 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.

次の図は、実行中のワークロードに対する影響を抑えるように、1 つのテーブルのクリーンアップ ロジックを調整する方法を示しています。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 Agent または別のツールを利用し、このプロセスをスケジュールします。Use SQL Server Agent or different tool to schedule this process:

  • 最古の行から最新の行まで、すべてのテンポラル テーブルの履歴データを削除します。数回繰り返し、小さなまとまりで実行します。上の図のように、1 回のトランザクションで全行を削除することは避けます。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 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 dynamically split points for every procedure invocation.

  • 1 つテーブルに対して繰り返しの間に一定の遅延を与え、テンポラル テーブルにアクセスするアプリケーションに与える影響を抑えます。Plan to have a period of delay between iterations for a single table to reduce impact on applications that access the temporal table.

    1 つのテンポラル テーブルのデータを削除するストアド プロシージャは次のコード スニペットのようになります (このコードを注意深く確認し、調整してから自分の環境で適用してください)。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: SET SYSTEM_VERSIONING = OFF, DELETE FROM history_table, SET SYSTEM_VERSIONING = ON */  
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

注: テンポラル履歴保有期間ポリシーを使う方法は、 SQL データベースSQL Database および SQL Server 2017 の CTP 1.3 以降に適用されます。NOTE: Using the Temporal History Retention Policy approach applies to SQL データベースSQL 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. テンポラル保有期間は簡単に適用できます。必要なのは、テーブル作成時またはスキーマ変更時にパラメーターを 1 つ設定することだけです。Applying temporal retention is simple: it requires only one parameter to be set during table creation or schema change.

保有期間ポリシーを定義した後、Azure SQL Database は自動データ クリーンアップの対象になる履歴行があるかどうかの定期的な確認を開始します。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. たとえば、保有期間が 6 か月に設定されている場合、クリーンアップの対象となるテーブルの行は次の条件を満たします。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 は既定で ON に設定されていますが、ユーザーは ALTER DATABASE ステートメントでそれを変更できます。Database flag is_temporal_history_retention_enabled is set to ON by default, but users can change it with ALTER DATABASE statement. ポイントインタイム リストア操作の後では、OFF に自動的に設定されます。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 Database が期限切れの行を削除する方法How SQL Database deletes aged rows?

クリーンアップ プロセスは、履歴テーブルのインデックスのレイアウトに依存します。The cleanup process depends on the index layout of the history table. 有限の保持期間ポリシーを構成できるのはクラスター化インデックス (B ツリーまたは列ストア) を使っている履歴テーブルだけであることに注意する必要があります。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. 行ストア (B ツリー) クラスター化インデックスのクリーンアップ ロジックは、データベース ログと I/O サブシステムへの負荷を最小限に抑えるため、小さいチャンク (最大 10 K) で期限切れの行を削除します。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. クリーンアップ ロジックは必要な B ツリー インデックスを利用しますが、保有期間より古い行の削除の順序は確実には保証できません。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.

クラスター化列ストアのクリーンアップ タスクは、行グループ全体を一度に削除します (通常、各グループには 100 万行が含まれます)。これは非常に効率的であり、履歴データが速いペースで生成されているときは特にそうです。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.

クラスター化列ストアの保有期間Clustered columnstore retention

優れたデータ圧縮と効率的な保有期間のクリーンアップにより、クラスター化列ストア インデックスはワークロードが急速に大量の履歴データを生成するシナリオに最適な選択肢になります。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. このようなパターンは、変更の追跡と監査、傾向分析、または IoT のデータ取り込みにテンポラル テーブルを使うトランザクション処理の多いワークロードで一般的なものです。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.

参照See Also

テンポラル テーブル Temporal Tables
システム バージョン管理されたテンポラル テーブルの概要 Getting Started with System-Versioned Temporal Tables
テンポラル テーブルのシステム一貫性のチェック Temporal Table System Consistency Checks
テンポラル テーブルでのパーティション分割 Partitioning with Temporal Tables
テンポラル テーブルの考慮事項と制約 Temporal Table Considerations and Limitations
テンポラル テーブル セキュリティ Temporal Table Security
メモリ最適化テーブルでのシステム バージョン管理されたテンポラル テーブル System-Versioned Temporal Tables with Memory-Optimized Tables
テンポラル テーブル メタデータのビューおよび関数Temporal Table Metadata Views and Functions