列ストア インデックス - 設計ガイダンスColumnstore indexes - Design guidance

適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database はいAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance はいAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics はいParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data Warehouse適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database はいAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance はいAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics はいParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data Warehouse

列ストア インデックスの設計に関する概要レベルの推奨事項です。High-level recommendations for designing columnstore indexes. 設計に関する少数の適切な意思決定は、列ストア インデックスが提供するように設計されている高いデータ圧縮率とクエリ パフォーマンスの実現に役立ちます。A small number of good design decisions helps you achieve the high data compression and query performance that columnstore indexes are designed to provide.

前提条件Prerequisites

この記事では、列ストアのアーキテクチャと用語に精通していることを想定しています。This article assumes you are familiar with columnstore architecture and terminology. 詳細については、「列ストア インデックス - 概要」と「列ストア インデックスのアーキテクチャ」を参照してください。For more information, see Columnstore indexes - Overview and Columnstore Index Architecture.

データ要件を認識するKnow your data requirements

列ストア インデックスを設計する前に、データ要件について可能な限り理解してください。Before designing a columnstore index, understand as much as possible about your data requirements. たとえば、次の質問に対する答えを考えてみてください。For example, think through the answers to these questions:

  • テーブルの大きさはどれくらいか。How large is my table?
  • 自分のクエリは主に広範囲の値をスキャンする分析を実行するか。Do my queries mostly perform analytics that scan large ranges of values? 列ストア インデックスは、特定の値の検索ではなく、広範囲のスキャンに対してうまく機能するように設計されています。Columnstore indexes are designed to work well for large range scans rather than looking up specific values.
  • 自分のワークロードは多数の更新と削除を実行するか。Does my workload perform lots of updates and deletes? 列ストア インデックスは、データが安定しているときにうまく機能します。Columnstore indexes work well when the data is stable. クエリが更新および削除するのは行の 10% 未満である必要があります。Queries should be updating and deleting less than 10% of the rows.
  • データ ウェアハウスのファクト テーブルとディメンション テーブルがあるか。Do I have fact and dimension tables for a data warehouse?
  • トランザクション ワークロードの分析を実行する必要があるか。Do I need to perform analytics on a transactional workload? 必要がある場合は、リアルタイム運用分析について列ストアの設計ガイダンスをご覧ください。If this is the case, see the columnstore design guidance for real-time operational analytics.

列ストア インデックスは必要でない場合があります。You might not need a columnstore index. ヒープまたはクラスター化インデックスを持つ行ストア テーブルは、データをシークするクエリ、特定の値の検索、狭い範囲の値でのクエリを実行するときに最適なパフォーマンスを発揮します。Rowstore tables with heaps or clustered indexes perform best on queries that seek into the data, searching for a particular value, or for queries on a small range of values. トランザクション ワークロードでは、広範囲のテーブル スキャンではなく主にテーブル シークを必要とする傾向があるため、行ストア インデックスを使用してください。Use rowstore indexes with transactional workloads since they tend to require mostly table seeks instead of large range table scans.

ニーズに最適な列ストア インデックスを選ぶChoose the best columnstore index for your needs

列ストア インデックスは、クラスター化または非クラスター化です。A columnstore index is either clustered or nonclustered. クラスター化列ストア インデックスでは、1 つ以上の非クラスター化 B ツリー インデックスを使用できます。A clustered columnstore index can have one or more nonclustered B-tree indexes. 列ストア インデックスは、簡単に試すことができます。Columnstore indexes are easy to try. テーブルを列ストア インデックスとして作成する場合は、列ストア インデックスを削除して、テーブルを行ストア テーブルに簡単に変換できます。If you create a table as a columnstore index, you can easily convert the table back to a rowstore table by dropping the columnstore index.

オプションと推奨事項の概要を次に示します。Here is a summary of the options and recommendations.

列ストア オプションColumnstore option 使用する場合に関する推奨事項Recommendations for when to use 圧縮Compression
クラスター化列ストア インデックスClustered columnstore index 用途:Use for:
1) スター スキーマまたはスノーフレーク スキーマがある従来のデータ ウェアハウス ワークロード1) Traditional data warehouse workload with a star or snowflake schema
2) 大量のデータを挿入し、更新と削除は最小限であるモノのインターネット (IoT)。2) Internet of Things (IOT) workloads that insert large volumes of data with minimal updates and deletes.
平均 10 倍Average of 10x
クラスター化列ストア インデックスの非クラスター化 B ツリー インデックスNonclustered B-tree indexes on a clustered columnstore index 以下の操作を実行するために使用します。Use to:
1.クラスター化列ストア インデックスに主キーと外部キーの制約を適用します。1. Enforce primary key and foreign key constraints on a clustered columnstore index.
2.特定の値または小さい範囲の値を検索するクエリを高速化します。2. Speed up queries that search for specific values or small ranges of values.
3.特定の行の更新と削除を高速化します。3. Speed up updates and deletes of specific rows.
平均 10 倍に加えて、NCI 用の追加ストレージ。10x on average plus some additional storage for the NCIs.
ディスクベースのヒープまたは B ツリー インデックスの非クラスター化列ストア インデックスNonclustered columnstore index on a disk-based heap or B-tree index 用途:Use for:
1) いくつかの分析クエリがある OLTP ワークロード。1) An OLTP workload that has some analytics queries. 分析用に作成された B ツリー インデックスを削除し、1 つの非クラスター化列ストアインデックスで置き換えることができます。You can drop B-tree indexes created for analytics and replace them with one nonclustered columnstore index.
2) 抽出、変換、および読み込み (ETL) 操作を実行してデータを別のデータ ウェアハウスに移動する多くの従来の OLTP ワークロード。2) Many traditional OLTP workloads that perform Extract Transform and Load (ETL) operations to move data to a separate data warehouse. 一部の OLTP テーブルに非クラスター化列ストア インデックスを作成すると、ETL と個別のデータ ウェアハウスを除外できます。You can eliminate ETL and a separate data warehouse by creating a nonclustered columnstore index on some of the OLTP tables.
NCCI は、平均で 10% 以上のストレージを必要とする追加のインデックスです。NCCI is an additional index that requires 10% more storage on average.
メモリ内のテーブルの列ストア インデックスColumnstore index on an in-memory table ベース テーブルがメモリ内テーブルであることを除いて、ディスク ベース テーブルの非クラスター化列ストア インデックスと同じ推奨事項。Same recommendations as nonclustered columnstore index on a disk-based table, except the base table is an in-memory table. 列ストア インデックスは追加のインデックスです。Columnstore index is an additional index.

大規模なデータ ウェアハウス テーブルにはクラスター化列ストア インデックスを使用するUse a clustered columnstore index for large data warehouse tables

クラスター化列ストア インデックスは複数のインデックスであり、主テーブル ストレージです。The clustered columnstore index is more than an index, it is the primary table storage. 大規模なデータ ウェアハウスのファクト テーブルとディメンション テーブルに対する高いデータ圧縮率とクエリ パフォーマンスの大幅な向上を実現します。It achieves high data compression and a significant improvement in query performance on large data warehousing fact and dimension tables. クラスター化列ストア インデックスは、トランザクション クエリよりも分析クエリに最適です。分析クエリは、特定の値の検索よりも、広範囲の値に対して操作を実行する傾向があるためです。Clustered columnstore indexes are best suited for analytics queries rather than transactional queries, since analytics queries tend to perform operations on large ranges of values rather than looking up specific values.

次の場合は、クラスター化列ストア インデックスの使用を検討してください。Consider using a clustered columnstore index when:

  • 各パーティションに少なくとも 100 万行がある場合。Each partition has at least a million rows. 列ストア インデックスでは、各パーティション内に行グループがあります。Columnstore indexes have rowgroups within each partition. 各パーティション内の行グループを満たすにはテーブルが小さすぎる場合、列ストアの圧縮とクエリ パフォーマンスのメリットは得られません。If the table is too small to fill a rowgroup within each partition, you won't get the benefits of columnstore compression and query performance.
  • クエリが主に値の範囲に対する分析を実行する場合。Queries primarily perform analytics on ranges of values. たとえば、列の平均値を検索するには、クエリですべての列値をスキャンする必要があります。For example, to find the average value of a column, the query needs to scan all the column values. 次に、値を加算して集計し、平均値を判断します。It then aggregates the values by summing them to determine the average.
  • 挿入のほとんどが大量のデータに対するもので、更新と削除は最小限である場合。Most of the inserts are on large volumes of data with minimal updates and deletes. モノのインターネット (IoT) などの多くのワークロードは大量のデータを挿入し、更新と削除は最小限です。Many workloads such as Internet of Things (IOT) insert large volumes of data with minimal updates and deletes. これらのワークロードは、クラスター化列ストア インデックスを使用する場合の圧縮とクエリ パフォーマンスのメリットを利用できます。These workloads can benefit from the compression and query performance gains that comes from using a clustered columnstore index.

次の場合はクラスター化列ストア インデックスを使用しないでください。Don't use a clustered columnstore index when:

  • テーブルに、varchar(max)、nvarchar(max)、varbinary(max) データ型が必要な場合。The table requires varchar(max), nvarchar(max), or varbinary(max) data types. または、これらの列が含まれないように列ストア インデックスを設計します。Or, design the columnstore index so that it doesn't include these columns.
  • テーブル データが永続的でない場合。The table data is not permanent. データをすばやく格納および削除する必要がある場合は、ヒープまたは一時テーブルの使用を検討してください。Consider using a heap or temporary table when you need to store and delete the data quickly.
  • テーブルの行数がパーティションあたり 100 万未満の場合。The table has less than one million rows per partition.
  • 更新と削除がテーブルに対する操作の 10% を超える場合。More than 10% of the operations on the table are updates and deletes. 大量の更新と削除は断片化の原因となります。Large numbers of updates and deletes cause fragmentation. すべてのデータを列ストアに強制的に入れて断片化を解消する再編成という操作を実行するまで、断片化は圧縮率とクエリ パフォーマンスに影響します。The fragmentation affects compression rates and query performance until you run an operation called reorganize that forces all data into the columnstore and removes fragmentation. 詳しくは、列ストア インデックスでインデックスの断片化を最小限に抑える方法に関する記事をご覧ください。For more information, see Minimizing index fragmentation in columnstore index.

詳しくは、「Columnstore indexes - data warehousing」(列ストア インデックス - データ ウェアハウス) をご覧ください。For more information, see Columnstore indexes - data warehousing.

B ツリー 非クラスター化インデックスを追加してテーブルのシークを効率化するAdd B-tree nonclustered indexes for efficient table seeks

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 以降では、クラスター化列ストア インデックスのセカンダリ インデックスとして非クラスター化 B ツリー インデックスを作成できます。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can create nonclustered B-tree indexes as secondary indexes on a clustered columnstore index. 列ストア インデックスが変更されると、非クラスター化 B ツリー インデックスが更新されます。The nonclustered B-tree index is updated as changes occur to the columnstore index. これは、うまく利用するとメリットのある強力な機能です。This is a powerful feature that you can use to your advantage.

セカンダリ B ツリー インデックスを使用すると、すべての行をスキャンせずに特定の行を効率的に検索できます。By using the secondary B-tree index, you can efficiently search for specific rows without scanning through all the rows. 他のオプションも使用できます。Other options become available too. たとえば、B ツリー インデックスで UNIQUE 制約を使用して、主キーまたは外部キー制約を適用できます。For example, you can enforce a primary or foreign key constraint by using a UNIQUE constraint on the B-tree index. 一意でない値は B ツリー インデックスに挿入できないため、SQL ServerSQL Server でその値を列ストアに挿入することはできません。Since a non-unique value will fail to insert into the B-tree index, SQL ServerSQL Server cannot insert the value into the columnstore.

列ストア インデックスの B ツリー インデックスは次の目的で使用することを検討してください。Consider using a B-tree index on a columnstore index to:

  • 特定の値または小さい範囲の値を検索するクエリを実行する。Run queries that search for particular values or small ranges of values.
  • 主キー制約や外部キー制約などの制約を適用する。Enforce a constraint such as a primary key or foreign key constraint.
  • 更新や削除の操作を効率的に実行する。Efficiently perform update and delete operations. B ツリー インデックスは、テーブルまたはテーブルのパーティション全体をスキャンせずに、更新や削除のために特定の行をすばやく検索できます。The B-tree index is able to quickly locate the specific rows for updates and deletes without scanning the full table or partition of a table.
  • B ツリー インデックスの格納に使用可能な追加の記憶域がある。You have additional storage available to store the B-tree index.

非クラスター化列ストア インデックスを使用したリアルタイム分析Use a nonclustered columnstore index for real-time analytics

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 以降では、行ストア ディスク ベース テーブルまたはメモリ内 OLTP テーブルで非クラスター化列ストア インデックスを使用できます。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can have a nonclustered columnstore index on a rowstore disk-based table or an in-memory OLTP table. これにより、トランザクション テーブルに対して分析をリアルタイムで実行できるようになります。This makes it possible to run the analytics in real-time on a transactional table. トランザクションは基になるテーブルで発生しますが、列ストア インデックスに対して分析を実行できます。While transactions are occurring on the underlying table, you can run analytics on the columnstore index. 1 つのテーブルで両方のインデックスを管理するため、行ストアと列ストア両方のインデックスに対して変更をリアルタイムで使用できます。Since one table manages both indexes, changes are available in real-time to both the rowstore and the columnstore indexes.

列ストア インデックスは行ストア インデックスよりもデータ圧縮率が 10 倍高いため、必要な追加ストレージがわずかで済みます。Since a columnstore index achieves 10x better data compression than a rowstore index, it only needs a small amount of extra storage. たとえば、圧縮された行ストア テーブルが 20 GB を必要とする場合、列ストア インデックスではさらに 2 GB 必要な場合があります。For example, if the compressed rowstore table takes 20 GB, the columnstore index might require an additional 2 GB. 必要な追加領域は、非クラスター化列ストア インデックス内の列数によっても異なります。The additional space required also depends on the number of columns in the nonclustered columnstore index.

次の場合は、非クラスター化列ストア インデックスの使用を検討してください。Consider using a nonclustered columnstore index to:

  • トランザクション行ストア テーブルに対して分析をリアルタイムで実行する場合。Run analytics in real-time on a transactional rowstore table. 分析用に設計された既存の B ツリー インデックスを非クラスター化列ストア インデックスで置き換えることができます。You can replace existing B-tree indexes that are designed for analytics with a nonclustered columnstore index.

  • 別のデータ ウェアハウスの必要をなくす場合。Eliminate the need for a separate data warehouse. 従来、企業では行ストア テーブルでトランザクションを実行し、データを別のデータ ウェアハウスに読み込んで分析を実行しています。Traditionally, companies run transactions on a rowstore table and then load the data into a separate data warehouse to run analytics. 多くのワークロードでは、トランザクション テーブルに非クラスター化列ストア インデックスを作成して、読み込みプロセスと別のデータ ウェアハウスを排除できます。For many workloads, you can eliminate the loading process and the separate data warehouse by creating a nonclustered columnstore index on transactional tables.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) には、このシナリオのパフォーマンスを高めるいくつかの方法が用意されています。offers several strategies to make this scenario performant. OLTP アプリケーションを変更せずに非クラスター化列ストア インデックスを有効にできるため、とても簡単に試すことができます。It's very easy to try it since you can enable a nonclustered columnstore index with no changes to your OLTP application.

処理リソースを追加するには、読み取り可能なセカンダリに対して分析を実行できます。To add additional processing resources, you can run the analytics on a readable secondary. 読み取り可能なセカンダリを使用すると、トランザクションのワークロードと分析ワークロードの処理が分離されます。Using a readable secondary separates the processing of the transactional workload and the analytics workload.

詳しくは、「Get started with columnstore indexes for real-time operational analytics」(列ストア インデックスを使用したリアルタイム運用分析の概要) をご覧くださいFor more information, see Get started with columnstore indexes for real-time operational analytics

最適な列ストア インデックスの選択について詳しくは、Sunil Agarwal のブログで「Which columnstore index is right for my workload?」(自分のワークロードに適した列ストア インデックス) をご覧ください。For more information on choosing the best columnstore index, see Sunil Agarwal's blog Which columnstore index is right for my workload?.

テーブルのパーティションを使用してデータ管理とクエリのパフォーマンスを高めるUse table partitions for data management and query performance

列ストア インデックスでは、データの管理とアーカイブの優れた方法であるパーティション分割がサポートされます。Columnstore indexes support partitioning which is a good way to manage and archive data. パーティション分割では、操作を 1 つ以上のパーティションに制限することでクエリのパフォーマンスも向上します。Partitioning also improves query performance by limiting operations to one or more partitions.

パーティションを使用してデータを管理しやすくするUse partitions to make the data easier to manage

大規模なテーブルの場合、データの範囲を管理する唯一の現実的な方法は、パーティションの使用です。For large tables, the only practical way to manage ranges of data is by using partitions. 行ストア テーブルのパーティションの利点は、列ストア インデックスにも適用されます。The advantages of partitions for rowstore tables also apply to columnstore indexes.

たとえば、行ストアと列ストアの両方のテーブルは、次の目的でパーティションを使用します。For example, both rowstore and columnstore tables use partitions to:

  • 増分バックアップのサイズを制御する。Control the size of incremental backups. パーティションをバックアップしてファイル グループを分離し、読み取り専用としてマークできます。You can back up partitions to separate filegroups and then mark them as read-only. これにより、今後のバックアップでは読み取り専用ファイル グループがスキップされます。By doing this, future backups will skip the read-only filegroups.
  • 古いパーティションを低コストのストレージに移動してストレージ コストを節約する。Save storage costs by moving an older partition to less expensive storage. たとえば、パーティションの切り替えを使用して、パーティションを低コストの記憶域の場所に移動できます。For example, you could use partition switching to move a partition to a less expensive storage location.
  • パーティションに対する操作を制限して操作を効率的に実行する。Perform operations efficiently by limiting the operations to a partition. たとえば、インデックスのメンテナンスのために、断片化されたパーティションのみを対象とすることができます。For example, you can target only the fragmented partitions for index maintenance.

さらに、列ストア インデックスでは、パーティション分割を次の目的で使用します。Additionally, with a columnstore index, you use partitioning to:

  • ストレージ コストをさらに 30% を節約する。Save an additional 30% in storage costs. COLUMNSTORE_ARCHIVE 圧縮オプションを使用して古いパーティションを圧縮することができます。You can compress older partitions with the COLUMNSTORE_ARCHIVE compression options. データのクエリ パフォーマンスは低下しますが、パーティションのクエリ頻度が低い場合は許容できます。The data will be slower for query performance, which is acceptable if the partition is queries infrequently.

パーティションを使用してクエリのパフォーマンスを向上させるUse partitions to improve query performance

パーティションを使用して、スキャンするクエリを特定のパーティションのみに制限して、スキャンする行数を制限できます。By using partitions, you can limit your queries to scan only specific partitions which limits the number of rows to scan. たとえば、インデックスが年でパーティション分割され、クエリが昨年のデータを分析する場合、スキャンする必要があるのは 1 つのパーティション内のデータだけです。For example, if the index is partitioned by year and the query is analyzing data from last year, it only needs to scan the data in one partition.

列ストア インデックスに使用するパーティションを少なくするUse fewer partitions for a columnstore index

データ サイズが十分に大きくない限り、列ストア インデックスは、行ストア インデックスに使用するよりも少ないパーティションで最高のパフォーマンスを発揮します。Unless you have a large enough data size, a columnstore index performs best with fewer partitions than what you might use for a rowstore index. パーティションごとに少なくとも 100 万行があるのでない場合、行のほとんどは、列ストア圧縮のパフォーマンス上のメリットがないデルタストアに入ります。If you don't have at least one million rows per partition, most of your rows might go to the deltastore where they don't receive the performance benefit of columnstore compression. たとえば、10 個のパーティションがあり、各パーティションが 100,000 行を受け取るテーブルに 100万行を読み込んだ場合は、すべての行がデルタ行グループに入ります。For example, if you load one million rows into a table with 10 partitions and each partition receives 100,000 rows, all of the rows will go to delta rowgroups.

例:Example:

  • 1,000,000 行を 1 つのパーティションまたは非パーティション テーブルに読み込みます。Load 1,000,000 rows into one partition or a non-partitioned table. 1,000,000 行を含む 1 つの圧縮行グループを取得します。You get one compressed rowgroup with 1,000,000 rows. これは、高いデータ圧縮と高速なクエリ パフォーマンスに優れています。This is great for high data compression and fast query performance.
  • 1,000,000 行を 10 個のパーティションに均等に読み込みます。Load 1,000,000 rows evenly into 10 partitions. 各パーティションは、列ストア圧縮の最小しきい値未満である 100,000 行を受け取ります。Each partition gets 100,000 rows, which is less than the minimum threshold for columnstore compression. その結果、列ストア インデックスには、それぞれ 100,000 行を含む 10 個のデルタ行グループがある可能性があります。As a result the columnstore index could have 10 delta rowgroups with 100,000 rows in each. デルタ行グループを列ストアに強制的に移動する方法があります。There are ways to force the delta rowgroups into the columnstore. ただし、これらが列ストア インデックス内の行のみである場合、圧縮された行グループは、最適な圧縮とクエリ パフォーマンスを発揮するには小さくなりすぎます。However, if these are the only rows in the columnstore index, the compressed rowgroups will be too small for best compression and query performance.

パーティション分割について詳しくは、Sunil Agarwal のブログ記事「Should I partition my columnstore index?」(自分の列ストア インデックスは分割する必要があるか) をご覧ください。For more information about partitioning, see Sunil Agarwal's blog post, Should I partition my columnstore index?.

適切なデータの圧縮方法を選ぶChoose the appropriate data compression method

列ストア インデックスは、データ圧縮に関する 2 つの選択肢 (列ストア圧縮とアーカイブ圧縮) を提供しています。The columnstore index offers two choices for data compression: columnstore compression and archive compression. 圧縮オプションは、インデックスの作成時、または後で ALTER INDEX ...REBUILD を使用して変更するときに選択できます。You can choose the compression option when you create the index, or change it later with ALTER INDEX ... REBUILD.

列ストア圧縮を使用して最適なクエリ パフォーマンスを実現するUse columnstore compression for best query performance

列ストア圧縮は通常、行ストア インデックスよりも 10 倍高い圧縮率を実現します。Columnstore compression typically achieves 10x better compression rates over rowstore indexes. これは列ストア インデックスの標準的な圧縮方法で、高速なクエリ パフォーマンスを実現します。It is the standard compression method for columnstore indexes and enables fast query performance.

アーカイブ圧縮を使用して最適なデータ圧縮を実現するUse archive compression for best data compression

アーカイブ圧縮は、クエリのパフォーマンスが重要ではないときに、圧縮率を最大化するように設計されています。Archive compression is designed for maximum compression when query performance is not as important. 列ストア圧縮よりも高いデータ圧縮率を実現しますが、価格は高くなります。It achieves higher data compression rates than columnstore compression, but it comes with a price. データの圧縮と圧縮解除に時間がかかるため、高速なクエリ パフォーマンスには適していません。It takes longer to compress and decompress the data, so it is not well-suited for fast query performance.

行ストア テーブルを列ストア インデックスに変換するときに最適化を使用するUse optimizations when you convert a rowstore table to a columnstore index

データが行ストア テーブルに既に存在する場合は、CREATE COLUMNSTORE INDEX を使用して、テーブルをクラスター化列ストア インデックスに変換できます。If your data is already in a rowstore table, you can use CREATE COLUMNSTORE INDEX to convert the table to a clustered columnstore index. 次のように、テーブルが変換された後にクエリのパフォーマンスを改善するいくつかの最適化があります。There are a couple optimizations that will improve query performance after the table is converted, described next.

MAXDOP を使用して行グループの品質を向上させるUse MAXDOP to improve rowgroup quality

ヒープまたはクラスター化 B ツリー インデックスを列ストア インデックスに変換するプロセッサの最大数を構成できます。You can configure the maximum number of processors for converting a heap or clustered B-tree index to a columnstore index. プロセッサを構成するには、最大並列度オプション (MAXDOP) を使用します。To configure the processors, use the maximum degree of parallelism option (MAXDOP).

大量のデータがある場合、MAXDOP 1 では遅すぎる可能性があります。If you have large amounts of data, MAXDOP 1 will likely be too slow. MAXDOP を 4 に増やすとうまく動作します。Increasing MAXDOP to 4 works fine. この結果、最適な行数がない少数の行グループが生成される場合は、ALTER INDEX REORGANIZE を実行してそれらをバックグラウンドでマージできます。If this results in a few rowgroups that do not have the optimal number of rows you can run ALTER INDEX REORGANIZE to merge them together in the background.

B ツリー インデックスの並べ替え順序を保持するKeep the sorted order of a B-tree index

B ツリー インデックスは並べ替えられた順序で行を既に格納しているため、行を列ストア インデックスに圧縮するときにその順序を維持すると、クエリのパフォーマンスが向上することがあります。Since the B-tree index already stores rows in a sorted order, preserving that order when the rows get compressed into the columnstore index can improve query performance.

列ストア インデックスは、データを並べ替えませんが、メタデータを使用して、各行グループ内の各列セグメントの最小値と最大値を追跡します。The columnstore index does not sort the data, but it does use metadata to track the minimum and maximum values of each column segment in each rowgroup. 値の範囲をスキャンする場合、行グループをスキップするときに簡単に計算できます。When scanning for a range of values, it can quickly compute when to skip the rowgroup. データが並べ替えられていると、より多くの行グループをスキップできます。When the data is ordered, more rowgroups can be skipped.

変換中に並べ替え順序を維持するには:To preserve the sorted order during conversion:

  • DROP_EXISTING 句を指定した CREATE COLUMNSTORE INDEX を使用します。Use CREATE COLUMNSTORE INDEX with the DROP_EXISTING clause. これにより、インデックスの名前も保持されます。This also preserves the name of the index. 既に行ストア インデックスの名前を使用しているスクリプトがある場合、それらを更新する必要はありません。If you have scripts that already use the name of the rowstore index you won't need to update them.

    次の例では、MyFactTable という名前のテーブル上のクラスター化された行ストア インデックスをクラスター化列ストア インデックスに変換します。This example converts a clustered rowstore index on a table named MyFactTable to a clustered columnstore index. インデックス名 ClusteredIndex_d473567f7ea04d7aafcac5364c241e09 は同じままになります。The index name, ClusteredIndex_d473567f7ea04d7aafcac5364c241e09, stays the same.

    CREATE CLUSTERED COLUMNSTORE INDEX ClusteredIndex_d473567f7ea04d7aafcac5364c241e09  
    ON MyFactTable  
    WITH (DROP_EXISTING = ON);  
    

以下は、列ストア インデックスを作成して保守するためのタスクです。These are tasks for creating and maintaining columnstore indexes.

タスクTask 参照トピックReference Topics NotesNotes
テーブルを列ストアとして作成する。Create a table as a columnstore. CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL) SQL Server 2016 (13.x)SQL Server 2016 (13.x)以降、テーブルをクラスター化列ストア インデックスとして作成できます。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can create the table as a clustered columnstore index. 最初に行ストア テーブルを作成して列ストアに変換する必要はありません。You do not have to first create a rowstore table and then convert it to columnstore.
列ストア インデックスを持つメモリ テーブルを作成します。Create a memory table with a columnstore index. CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL) SQL Server 2016 (13.x)SQL Server 2016 (13.x)以降、列ストア インデックスを持つ、メモリ最適化テーブルを作成できます。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can create a memory-optimized table with a columnstore index. 列ストア インデックスは、テーブルの作成後に、ALTER TABLE ADD INDEX 構文を使用して追加することもできます。The columnstore index can also be added after the table is created, using the ALTER TABLE ADD INDEX syntax.
行ストア テーブルを列ストアに変換する。Convert a rowstore table to a columnstore. CREATE COLUMNSTORE INDEX (Transact-SQL)CREATE COLUMNSTORE INDEX (Transact-SQL) 既存のヒープまたはバイナリ ツリーを列ストアに変換します。Convert an existing heap or binary tree to a columnstore. この変換を実行するときの既存のインデックスとインデックス名の処理方法を例示します。Examples show how to handle existing indexes and also the name of the index when performing this conversion.
列ストア テーブルを行ストアに変換する。Convert a columnstore table to a rowstore. CREATE CLUSTERED INDEX (Transact-SQL)列ストア テーブルを行ストア ヒープに戻すCREATE CLUSTERED INDEX (Transact-SQL) or Convert a columnstore table back to a rowstore heap この変換は通常は必要ありませんが、状況によっては必要になる場合があります。Usually this conversion isn't necessary, but there can be times when you need to convert. 列ストアをヒープまたはクラスター化インデックスに変換する方法を例示します。Examples show how to convert a columnstore to a heap or clustered index.
行ストア テーブルで列ストア インデックスを作成する。Create a columnstore index on a rowstore table. CREATE COLUMNSTORE INDEX (Transact-SQL)CREATE COLUMNSTORE INDEX (Transact-SQL) 行ストア テーブルでは列ストア インデックスを 1 つ使用できます。A rowstore table can have one columnstore index. SQL Server 2016 (13.x)SQL Server 2016 (13.x)以降、列ストア インデックスにフィルター条件を指定できるようになりました。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the columnstore index can have a filtered condition. 基本構文を例示します。Examples show the basic syntax.
運用分析のパフォーマンスの高いインデックスを作成する。Create performant indexes for operational analytics. 列ストアを使用したリアルタイム運用分析の概要Get started with Columnstore for real time operational analytics 補完的な列ストア インデックスと B ツリー インデックスを作成する方法について説明します。OLTP クエリでは B ツリー インデックスが使用され、分析クエリでは列ストア インデックスが使用されます。Describes how to create complementary columnstore and B-tree indexes so that OLTP queries use B-tree indexes and analytics queries use columnstore indexes.
データ ウェアハウス用のパフォーマンスの高い列ストア インデックスを作成する。Create performant columnstore indexes for data warehousing. 列ストア インデックス - データ ウェアハウスColumnstore indexes - data Warehousing 列ストア テーブルで B ツリー インデックスを使用して、パフォーマンスの高いデータ ウェアハウス クエリを作成する方法について説明します。Describes how to use B-tree indexes on columnstore tables to create performant data warehousing queries.
B ツリー インデックスを使用して列ストア インデックスに主キー制約を適用するUse a B-tree index to enforce a primary key constraint on a columnstore index. 列ストア インデックス - データ ウェアハウスColumnstore indexes - data warehousing B ツリー インデックスと列ストア インデックスを組み合わせて、列ストア インデックスに主キー制約を適用する方法を示します。Shows how to combine B-tree and columnstore indexes to enforce primary key constraints on the columnstore index.
列ストア インデックスを削除するDrop a columnstore index DROP INDEX (Transact-SQL)DROP INDEX (Transact-SQL) 列ストア インデックスは、B ツリー インデックスが使用する標準の DROP INDEX 構文を使って削除します。Dropping a columnstore index uses the standard DROP INDEX syntax that B-tree indexes use. クラスター化列ストア インデックスを削除すると、列ストア テーブルがヒープに変換されます。Dropping a clustered columnstore index will convert the columnstore table to a heap.
列ストア インデックスから行を削除するDelete a row from a columnstore index DELETE (Transact-SQL)DELETE (Transact-SQL) DELETE (Transact-SQL) を使用して行を削除します。Use DELETE (Transact-SQL) to delete a row.

列ストア 行: SQL ServerSQL Server は行を論理的に削除されたとしてマークしますが、インデックスが再構築されるまで行の物理ストレージを再確保することはありません。columnstore row: SQL ServerSQL Server marks the row as logically deleted but does not reclaim the physical storage for the row until the index is rebuilt.

デルタストア 行: SQL ServerSQL Server は論理的および物理的に行を削除します。deltastore row: SQL ServerSQL Server logically and physically deletes the row.
列ストア インデックスの行を更新するUpdate a row in the columnstore index UPDATE (Transact-SQL)UPDATE (Transact-SQL) UPDATE (Transact-SQL) を使用して行を更新します。Use UPDATE (Transact-SQL) to update a row.

列ストア 行: SQL ServerSQL Server は行を論理的に削除されたとしてマークし、更新された行をデルタストアに挿入します。columnstore row: SQL ServerSQL Server marks the row as logically deleted, and then inserts the updated row into the deltastore.

デルタストア 行: SQL ServerSQL Server は、デルタストアの行を更新します。deltastore row: SQL ServerSQL Server updates the row in the deltastore.
デルタストアのすべての行を強制的に列ストアに移動します。Force all rows in the deltastore to go into the columnstore. ALTER INDEX (Transact-SQL) ...REBUILDALTER INDEX (Transact-SQL) ... REBUILD

インデックスの再編成と再構築Reorganize and Rebuild Indexes
ALTER INDEX に REBUILD オプションを指定すると、すべての行が列ストアに強制的に移動されます。ALTER INDEX with the REBUILD option forces all rows to go into the columnstore.
列ストア インデックスを最適化するDefragment a columnstore index ALTER INDEX (Transact-SQL)ALTER INDEX (Transact-SQL) ALTER INDEX ...REORGANIZE は、列ストア インデックスをオンラインで最適化します。ALTER INDEX ... REORGANIZE defragments columnstore indexes online.
テーブルと列ストア インデックスをマージする。Merge tables with columnstore indexes. MERGE (Transact-SQL)MERGE (Transact-SQL)

次のステップNext steps

空の列ストア インデックスを作成するには:To create an empty columnstore index for:

既存の行ストア ヒープまたは B ツリー インデックスをクラスター化列ストア インデックスに変換する方法、または非クラスター化列ストア インデックスを作成する方法の詳細については、「CREATE COLUMNSTORE INDEX (Transact-SQL)」を参照してください。For more information on how convert an existing rowstore heap or B-tree index into a clustered columnstore index, or to create a nonclustered columnstore index, refer to CREATE COLUMNSTORE INDEX (Transact-SQL).