専用 SQL プールでのテーブルのパーティション分割Partitioning tables in dedicated SQL pool

専用 SQL プールでのテーブル パーティションの使用に関するレコメンデーションと例。Recommendations and examples for using table partitions in dedicated SQL pool.

テーブル パーティションの概要What are table partitions?

テーブル パーティションを使用すると、データを小さなデータ グループに分割できます。Table partitions enable you to divide your data into smaller groups of data. ほとんどの場合、テーブル パーティションはデータ列に作成されます。In most cases, table partitions are created on a date column. パーティション分割は、クラスター化列ストア、クラスター化インデックス、ヒープなど、専用 SQL プールのすべてのテーブル型でサポートされます。Partitioning is supported on all dedicated SQL pool table types; including clustered columnstore, clustered index, and heap. パーティション分割は、ハッシュ分散とラウンド ロビン分散の両方を含むあらゆる種類のディストリビューションでもサポートされます。Partitioning is also supported on all distribution types, including both hash or round robin distributed.

パーティション分割をすると、データのメンテナンスとクエリのパフォーマンスでメリットを得ることができます。Partitioning can benefit data maintenance and query performance. 両方のメリットを得られるか、片方のみかは、データの読み込み方法と、同じ列を両方の目的で使用できるかどうかによります。その理由は、パーティション分割を実行できるのが 1 つの列のみであるためです。Whether it benefits both or just one is dependent on how data is loaded and whether the same column can be used for both purposes, since partitioning can only be done on one column.

読み込みに対するメリットBenefits to loads

専用 SQL プールでパーティション分割する主なメリットは、パーティションの削除、切り替え、および結合の使用による、データの読み込みの効率性とパフォーマンスの向上です。The primary benefit of partitioning in dedicated SQL pool is to improve the efficiency and performance of loading data by use of partition deletion, switching and merging. ほとんどの場合、データは、データがデータベースに読み込まれる順序に密接に関連付けられている日付列でパーティション分割されます。In most cases data is partitioned on a date column that is closely tied to the order in which the data is loaded into the database. データを保持するためにパーティションを使用する最大のメリットの 1 つが、トランザクション ログの回避です。One of the greatest benefits of using partitions to maintain data is the avoidance of transaction logging. 単にデータを挿入、更新、または削除するのは最も簡単なアプローチですが、少しの配慮と労力を注いで読み込みプロセス中にパーティション分割を使用すると、大幅にパフォーマンスを向上できます。While simply inserting, updating, or deleting data can be the most straightforward approach, with a little thought and effort, using partitioning during your load process can substantially improve performance.

テーブルのセクションを手早く削除したり置き換えたりするには、パーティションの切り替えを使用できます。Partition switching can be used to quickly remove or replace a section of a table. たとえば、売上のファクト テーブルに過去 36 か月のデータのみが含まれるとします。For example, a sales fact table might contain just data for the past 36 months. 毎月末に、最も古い月の売上データがテーブルから削除されます。At the end of every month, the oldest month of sales data is deleted from the table. このデータは、最も古い月のデータを削除する delete ステートメントを使用して削除できます。This data could be deleted by using a delete statement to delete the data for the oldest month.

ただし、大量のデータを delete ステートメントで行単位で削除すると、非常に長い時間がかかるだけでなく、問題が生じた場合に、トランザクションが巨大なことにより、ロールバックに時間がかかるリスクが生じる可能性があります。However, deleting a large amount of data row-by-row with a delete statement can take too much time, as well as create the risk of large transactions that take a long time to rollback if something goes wrong. より最適なアプローチは、データの最も古いパーティションを削除する方法です。A more optimal approach is to drop the oldest partition of data. 個々の行の削除に時間がかかる可能性がある場合、パーティション全体を削除すると、わずかな時間で終了する可能性があります。Where deleting the individual rows could take hours, deleting an entire partition could take seconds.

クエリに対するメリットBenefits to queries

パーティション分割により、クエリのパフォーマンスを向上させることもできます。Partitioning can also be used to improve query performance. パーティション分割されたデータにフィルターを適用するクエリは、該当するパーティションのみにスキャンを制限できます。A query that applies a filter to partitioned data can limit the scan to only the qualifying partitions. このフィルター処理方法では、テーブルのフル スキャンを回避でき、データの小さいサブセットのみをスキャンすることができます。This method of filtering can avoid a full table scan and only scan a smaller subset of data. クラスター化列ストア インデックスの導入では、述語の削除によるパフォーマンスのメリットはあまりありませんが、クエリにメリットをもたらす場合があります。With the introduction of clustered columnstore indexes, the predicate elimination performance benefits are less beneficial, but in some cases there can be a benefit to queries.

たとえば、売上のファクト テーブルが販売日フィールドを使用して 36 か月にパーティション分割されている場合は、販売日をフィルター処理するクエリによって、フィルターと一致しないパーティションの検索を省略できます。For example, if the sales fact table is partitioned into 36 months using the sales date field, then queries that filter on the sale date can skip searching in partitions that don't match the filter.

パーティションのサイズ変更Sizing partitions

パーティション分割を使用すると一部のシナリオのパフォーマンスが向上する可能性がありますが、パーティションが 多すぎる テーブルを作成すると、特定の状況でパフォーマンスが低下する可能性があります。While partitioning can be used to improve performance some scenarios, creating a table with too many partitions can hurt performance under some circumstances. これらの問題は、特にクラスター化列ストア テーブルに当てはまります。These concerns are especially true for clustered columnstore tables.

パーティション分割が役立つように、パーティション分割を使用する時期と作成するパーティション数を把握することが重要です。For partitioning to be helpful, it is important to understand when to use partitioning and the number of partitions to create. パーティションの数が多すぎるかどうかについて厳格なルールはなく、データと、同時に読み込むパーティションの数によります。There is no hard fast rule as to how many partitions are too many, it depends on your data and how many partitions you loading simultaneously. パーティション分割構成が成功すると、通常、パーティションの数は数十個から数百個程度であり、数千個にまでなることはありません。A successful partitioning scheme usually has tens to hundreds of partitions, not thousands.

クラスター化列ストア テーブルでパーティションを作成するときは、各パーティションに属している行数が重要になります。When creating partitions on clustered columnstore tables, it is important to consider how many rows belong to each partition. クラスター化列ストア テーブルの圧縮とパフォーマンスを最適化するためには、ディストリビューションおよびパーティションあたり少なくとも 100 万行が必要です。For optimal compression and performance of clustered columnstore tables, a minimum of 1 million rows per distribution and partition is needed. 専用 SQL プールでは、パーティションが作成される前に、各テーブルが 60 個の分散データベースに既に分割されています。Before partitions are created, dedicated SQL pool already divides each table into 60 distributed databases.

テーブルに追加されるすべてのパーティション分割は、バックグラウンドで作成されたディストリビューションに追加されたものです。Any partitioning added to a table is in addition to the distributions created behind the scenes. この例では、売上のファクト テーブルに 36 か月のパーティションが含まれる場合、専用 SQL プールに 60 のディストリビューションがあるとすると、売上のファクト テーブルには 1 か月あたり 6 千万行、すべての月を指定する場合は 21 億行を含める必要があります。Using this example, if the sales fact table contained 36 monthly partitions, and given that a dedicated SQL pool has 60 distributions, then the sales fact table should contain 60 million rows per month, or 2.1 billion rows when all months are populated. テーブルに含まれる行が、パーティションごとの推奨される最小の行数よりも少ない場合、パーティションあたりの行数を増やすためにパーティション数を少なくすることを検討する必要があります。If a table contains fewer than the recommended minimum number of rows per partition, consider using fewer partitions in order to increase the number of rows per partition.

詳細については、インデックス作成に関する記事を参照してください。この記事には、クラスター列ストア インデックスの質を評価できるクエリについて記載されています。For more information, see the Indexing article, which includes queries that can assess the quality of cluster columnstore indexes.

SQL Server との構文の相違点Syntax differences from SQL Server

専用 SQL プールには、SQL Server よりも簡単なパーティションの定義方法が導入されています。Dedicated SQL pool introduces a way to define partitions that is simpler than SQL Server. パーティション関数とパーティション構成は、SQL Server のものであるため、専用 SQL プールでは使用されません。Partitioning functions and schemes are not used in dedicated SQL pool as they are in SQL Server. 代わりに、必要なのは、パーティション分割された列と境界点を特定することだけです。Instead, all you need to do is identify partitioned column and the boundary points.

パーティション分割の構文は、SQL Server と若干異なる場合がありますが、基本的な概念は同じです。While the syntax of partitioning may be slightly different from SQL Server, the basic concepts are the same. SQL Server および専用 SQL プールでは、テーブルごとに 1 つのパーティション列がサポートされます。このパーティション列で、範囲指定によるパーティションを指定することができます。SQL Server and dedicated SQL pool support one partition column per table, which can be ranged partition. パーティション分割の詳細については、「パーティション テーブルとパーティション インデックス」を参照してください。To learn more about partitioning, see Partitioned Tables and Indexes.

次の例では、CREATE TABLE ステートメントを使用して、FactInternetSales テーブルを OrderDateKey 列でパーティション分割します。The following example uses the CREATE TABLE statement to partition the FactInternetSales table on the OrderDateKey column:

CREATE TABLE [dbo].[FactInternetSales]
(
    [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,   DISTRIBUTION = HASH([ProductKey])
,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                    (20000101,20010101,20020101
                    ,20030101,20040101,20050101
                    )
                )
)
;

SQL Server からのパーティション分割の移行Migrating partitioning from SQL Server

SQL Server のパーティション定義を専用 SQL プールに移行するには、次の操作を行います。To migrate SQL Server partition definitions to dedicated SQL pool simply:

パーティション分割されたテーブルを SQL Server インスタンスから移行する場合、各パーティションに含まれる行数を調べるうえで以下の SQL が役立つ場合があります。If you are migrating a partitioned table from a SQL Server instance, the following SQL can help you to figure out the number of rows that in each partition. 専用 SQL プールで同じパーティション分割の粒度を使用する場合、パーティションごとの行数が 60 の倍数で減少することに注意してください。Keep in mind that if the same partitioning granularity is used in dedicated SQL pool, the number of rows per partition decreases by a factor of 60.

-- Partition information for a SQL Server Database
SELECT      s.[name]                        AS      [schema_name]
,           t.[name]                        AS      [table_name]
,           i.[name]                        AS      [index_name]
,           p.[partition_number]            AS      [partition_number]
,           SUM(a.[used_pages]*8.0)         AS      [partition_size_kb]
,           SUM(a.[used_pages]*8.0)/1024    AS      [partition_size_mb]
,           SUM(a.[used_pages]*8.0)/1048576 AS      [partition_size_gb]
,           p.[rows]                        AS      [partition_row_count]
,           rv.[value]                      AS      [partition_boundary_value]
,           p.[data_compression_desc]       AS      [partition_compression_desc]
FROM        sys.schemas s
JOIN        sys.tables t                    ON      t.[schema_id]         = s.[schema_id]
JOIN        sys.partitions p                ON      p.[object_id]         = t.[object_id]
JOIN        sys.allocation_units a          ON      a.[container_id]      = p.[partition_id]
JOIN        sys.indexes i                   ON      i.[object_id]         = p.[object_id]
                                            AND     i.[index_id]          = p.[index_id]
JOIN        sys.data_spaces ds              ON      ds.[data_space_id]    = i.[data_space_id]
LEFT JOIN   sys.partition_schemes ps        ON      ps.[data_space_id]    = ds.[data_space_id]
LEFT JOIN   sys.partition_functions pf      ON      pf.[function_id]      = ps.[function_id]
LEFT JOIN   sys.partition_range_values rv   ON      rv.[function_id]      = pf.[function_id]
                                            AND     rv.[boundary_id]      = p.[partition_number]
WHERE       p.[index_id] <=1
GROUP BY    s.[name]
,           t.[name]
,           i.[name]
,           p.[partition_number]
,           p.[rows]
,           rv.[value]
,           p.[data_compression_desc]
;

パーティションの切り替えPartition switching

専用 SQL プールでは、パーティションの分割、結合、および切り替えがサポートされています。Dedicated SQL pool supports partition splitting, merging, and switching. これらの各機能は、ALTER TABLE ステートメントを使用して実行されます。Each of these functions is executed using the ALTER TABLE statement.

2 つのテーブル間でパーティションを切り替えるには、それぞれの境界に合わせてパーティションが配置されていることと、テーブル定義が一致していることを確認する必要があります。To switch partitions between two tables, you must ensure that the partitions align on their respective boundaries and that the table definitions match. テーブルで値の範囲を適用する際に CHECK 制約は使用できないため、ソース テーブルにターゲットテーブルと同じパーティション境界が含まれている必要があります。As check constraints are not available to enforce the range of values in a table, the source table must contain the same partition boundaries as the target table. パーティション境界が同じでない場合、パーティションのメタデータが同期されないため、パーティションの切り替えは失敗します。If the partition boundaries are not then same, then the partition switch will fail as the partition metadata will not be synchronized.

データが含まれたパーティションを分割する方法How to split a partition that contains data

既にデータが含まれているパーティションを分割する最も効率的な方法は、 CTAS ステートメントを使用することです。The most efficient method to split a partition that already contains data is to use a CTAS statement. パーティション テーブルがクラスター化列ストアの場合、テーブルのパーティションを分割するには、パーティションを空にしておく必要があります。If the partitioned table is a clustered columnstore, then the table partition must be empty before it can be split.

次の例は、パーティション分割された列ストア テーブルを作成します。The following example creates a partitioned columnstore table. 各パーティションに 1 つの行を挿入します。It inserts one row into each partition:

CREATE TABLE [dbo].[FactInternetSales]
(
        [ProductKey]            int          NOT NULL
    ,   [OrderDateKey]          int          NOT NULL
    ,   [CustomerKey]           int          NOT NULL
    ,   [PromotionKey]          int          NOT NULL
    ,   [SalesOrderNumber]      nvarchar(20) NOT NULL
    ,   [OrderQuantity]         smallint     NOT NULL
    ,   [UnitPrice]             money        NOT NULL
    ,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,   DISTRIBUTION = HASH([ProductKey])
,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                    (20000101
                    )
                )
)
;

INSERT INTO dbo.FactInternetSales
VALUES (1,19990101,1,1,1,1,1,1);
INSERT INTO dbo.FactInternetSales
VALUES (1,20000101,1,1,1,1,1,1);

次のクエリは、sys.partitions カタログ ビューを使用して、行数を検索します。The following query finds the row count by using the sys.partitions catalog view:

SELECT  QUOTENAME(s.[name])+'.'+QUOTENAME(t.[name]) as Table_name
,       i.[name] as Index_name
,       p.partition_number as Partition_nmbr
,       p.[rows] as Row_count
,       p.[data_compression_desc] as Data_Compression_desc
FROM    sys.partitions p
JOIN    sys.tables     t    ON    p.[object_id]   = t.[object_id]
JOIN    sys.schemas    s    ON    t.[schema_id]   = s.[schema_id]
JOIN    sys.indexes    i    ON    p.[object_id]   = i.[object_Id]
                            AND   p.[index_Id]    = i.[index_Id]
WHERE t.[name] = 'FactInternetSales'
;

次の分割コマンドは、エラー メッセージを受信します。The following split command receives an error message:

ALTER TABLE FactInternetSales SPLIT RANGE (20010101);

パーティションが空でないため、ALTER PARTITION ステートメントの Msg 35346, Level 15, State 1, Line 44 SPLIT 句が失敗しました。Msg 35346, Level 15, State 1, Line 44 SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. テーブルに列ストア インデックスが存在するときは、空のパーティションのみを分割できます。Only empty partitions can be split in when a columnstore index exists on the table. ALTER PARTITION ステートメントを発行する前に列ストア インデックスを無効にし、ALTER PARTITION が完了したら列ストア インデックスを再構築することを検討します。Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.

ただし、CTAS を使用して、データを保持する新しいテーブルを作成できます。However, you can use CTAS to create a new table to hold the data.

CREATE TABLE dbo.FactInternetSales_20000101
    WITH    (   DISTRIBUTION = HASH(ProductKey)
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101
                                )
                            )
            )
AS
SELECT *
FROM    FactInternetSales
WHERE   1=2
;

パーティション境界が配置されているので、切り替えが許可されます。As the partition boundaries are aligned, a switch is permitted. これにより、後で分割できる空のパーティションを含むソース テーブルをそのままにしておくことができます。This will leave the source table with an empty partition that you can subsequently split.

ALTER TABLE FactInternetSales SWITCH PARTITION 2 TO  FactInternetSales_20000101 PARTITION 2;

ALTER TABLE FactInternetSales SPLIT RANGE (20010101);

後は、CTAS を使用して新しいパーティション境界に合わせてデータを配置し、データをメイン テーブルに切り替えるだけです。All that is left is to align the data to the new partition boundaries using CTAS, and then switch the data back into the main table.

CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
    WITH    (   DISTRIBUTION = HASH([ProductKey])
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101,20010101
                                )
                            )
            )
AS
SELECT  *
FROM    [dbo].[FactInternetSales_20000101]
WHERE   [OrderDateKey] >= 20000101
AND     [OrderDateKey] <  20010101
;

ALTER TABLE dbo.FactInternetSales_20000101_20010101 SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2;

データの移動が完了したら、ターゲット テーブルの統計を更新することをお勧めします。Once you have completed the movement of the data, it is a good idea to refresh the statistics on the target table. 統計を更新することで、各パーティション内のデータの新しい分散が統計に正確に反映されます。Updating statistics ensures the statistics accurately reflect the new distribution of the data in their respective partitions.

UPDATE STATISTICS [dbo].[FactInternetSales];

データを含むパーティションにワンステップで新しいデータを読み込むLoad new data into partitions that contain data in one step

パーティションの切り替えを使用したパーティションへのデータの読み込みは、ユーザーには表示されないテーブルに新しいデータをステージングできる便利な方法です。Loading data into partitions with partition switching is a convenient way to stage new data in a table that is not visible to users. ビジー システムでは、パーティションの切り替えに関連したロックの競合への対応が困難な場合があります。It can be challenging on busy systems to deal with the locking contention associated with partition switching.

パーティション内の既存のデータを取り除くには、データをスイッチアウトするために ALTER TABLE が必要でした。To clear out the existing data in a partition, an ALTER TABLE used to be required to switch out the data. それから、新しいデータにスイッチインするために別の ALTER TABLE が必要でした。Then another ALTER TABLE was required to switch in the new data.

専用 SQL プールでは、ALTER TABLE コマンドで TRUNCATE_TARGET オプションがサポートされています。In dedicated SQL pool, the TRUNCATE_TARGET option is supported in the ALTER TABLE command. TRUNCATE_TARGET により、ALTER TABLE コマンドは、パーティション内の既存のデータを新しいデータで上書きします。With TRUNCATE_TARGET the ALTER TABLE command overwrites existing data in the partition with new data. 以下の例は、CTAS を使用して、既存のデータで新しいテーブルを作成して新しいデータを挿入し、次に、既存のデータを上書きして、すべてのデータをターゲット テーブルに切り替えています。Below is an example that uses CTAS to create a new table with the existing data, inserts new data, then switches all the data back into the target table, overwriting the existing data.

CREATE TABLE [dbo].[FactInternetSales_NewSales]
    WITH    (   DISTRIBUTION = HASH([ProductKey])
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101,20010101
                                )
                            )
            )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
WHERE   [OrderDateKey] >= 20000101
AND     [OrderDateKey] <  20010101
;

INSERT INTO dbo.FactInternetSales_NewSales
VALUES (1,20000101,2,2,2,2,2,2);

ALTER TABLE dbo.FactInternetSales_NewSales SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2 WITH (TRUNCATE_TARGET = ON);  

テーブル パーティションのソース管理Table partitioning source control

ソース管理システムでテーブル定義が 古く ならないように、次の方法を検討することをお勧めします。To avoid your table definition from rusting in your source control system, you may want to consider the following approach:

  1. パーティション値を含まないパーティション テーブルとしてテーブルを作成します。Create the table as a partitioned table but with no partition values

    CREATE TABLE [dbo].[FactInternetSales]
    (
        [ProductKey]            int          NOT NULL
    ,   [OrderDateKey]          int          NOT NULL
    ,   [CustomerKey]           int          NOT NULL
    ,   [PromotionKey]          int          NOT NULL
    ,   [SalesOrderNumber]      nvarchar(20) NOT NULL
    ,   [OrderQuantity]         smallint     NOT NULL
    ,   [UnitPrice]             money        NOT NULL
    ,   [SalesAmount]           money        NOT NULL
    )
    WITH
    (   CLUSTERED COLUMNSTORE INDEX
    ,   DISTRIBUTION = HASH([ProductKey])
    ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES () )
    )
    ;
    
  2. SPLIT (分割) します。SPLIT the table as part of the deployment process:

     -- Create a table containing the partition boundaries
    
    CREATE TABLE #partitions
    WITH
    (
        LOCATION = USER_DB
    ,   DISTRIBUTION = HASH(ptn_no)
    )
    AS
    SELECT  ptn_no
    ,       ROW_NUMBER() OVER (ORDER BY (ptn_no)) as seq_no
    FROM    (
        SELECT CAST(20000101 AS INT) ptn_no
        UNION ALL
        SELECT CAST(20010101 AS INT)
        UNION ALL
        SELECT CAST(20020101 AS INT)
        UNION ALL
        SELECT CAST(20030101 AS INT)
        UNION ALL
        SELECT CAST(20040101 AS INT)
    ) a
    ;
    
     -- Iterate over the partition boundaries and split the table
    
    DECLARE @c INT = (SELECT COUNT(*) FROM #partitions)
    ,       @i INT = 1                                 --iterator for while loop
    ,       @q NVARCHAR(4000)                          --query
    ,       @p NVARCHAR(20)     = N''                  --partition_number
    ,       @s NVARCHAR(128)    = N'dbo'               --schema
    ,       @t NVARCHAR(128)    = N'FactInternetSales' --table
    ;
    
    WHILE @i <= @c
    BEGIN
        SET @p = (SELECT ptn_no FROM #partitions WHERE seq_no = @i);
        SET @q = (SELECT N'ALTER TABLE '+@s+N'.'+@t+N' SPLIT RANGE ('+@p+N');');
    
        -- PRINT @q;
        EXECUTE sp_executesql @q;
        SET @i+=1;
    END
    
     -- Code clean-up
    
    DROP TABLE #partitions;
    

この方法では、ソース管理のコードを静的なコードとして維持し、パーティション境界値は動的にすることが可能になるので、時間の経過に伴って、データベースとともに進化させることができます。With this approach, the code in source control remains static and the partitioning boundary values are allowed to be dynamic; evolving with the database over time.

次のステップNext steps

テーブルの開発の詳細については、テーブルの概要に関する記事を参照してください。For more information about developing tables, see the articles on Table Overview.