列ストア インデックス

xVelocity メモリ最適化列ストア インデックスでは、各列に対応するデータをグループ化して格納した後、すべての列を結合してインデックス全体を作成します。 この動作は、各行に対応するデータをグループ化して格納した後、すべての行を結合してインデックス全体を作成するという従来のインデックスとは異なります。 クエリの種類によっては、SQL Server クエリ プロセッサで列ストア レイアウトを利用することでクエリの実行回数を大幅に改善することができます。 データ ウェアハウスが急速に大規模化する中、意志決定支援アプリケーションおよび BI アプリケーションでは、非常に大きなデータ セットを迅速かつ正確に読み取り、有用な情報や知識として処理する機能が一刻も早く求められるようになっています。 このようなデータ ボリュームの巨大化と、求められる性能の急激な高度化によって、パフォーマンスを徐々に向上することでシステムを維持し、改善することが困難になっています。 SQL Server 列ストア インデックス テクノロジは、一般的なデータ ウェアハウスのデータ セットに特に適しています。 列ストア インデックスによって、フィルター処理クエリ、集計クエリ、グループ化クエリ、スター結合クエリなどの一般的なデータ ウェアハウス クエリのパフォーマンスを向上することで、ユーザーが快適にデータ ウェアハウスを利用できるようになります。

コンテンツ

基礎

• 基礎: 列ストア インデックスの説明

• 基礎: 列ストア インデックスの制約と制限

• 使用例: パーティション テーブルを含む列ストア インデックス

• 基礎: 一般的な列ストア インデックスのシナリオ

• 基礎: ビットマップ フィルターの最適化

ベスト プラクティス

• ベスト プラクティス: 列ストア インデックスのデータの更新

• ベスト プラクティス: 列ストア インデックスの列の選択

• ベスト プラクティス: パーティション テーブル

操作方法

• 方法: 列ストア インデックスを作成する

• 方法: 列ストア インデックスのサイズを判断する

• 方法: 列ストア インデックスのパフォーマンスのトラブルシューティングを実行する

基礎: 列ストア インデックスの説明

SQL Server データベース エンジン の列ストア インデックスを使用して、一般的なデータ ウェアハウス クエリの処理時間を大幅に短縮することができます。 一般的なデータ ウェアハウスのワークロードには、大量のデータを集約する処理が含まれています。 データ ウェアハウス システムおよび意志決定支援システムのパフォーマンスを向上するために一般的に使用される手法は、概要テーブルの事前計算、インデックス付きビュー、OLAP キューブなどです。 このような手法によってクエリの処理速度が大幅に向上する場合もありますが、このような手法は柔軟性に乏しく維持が困難であり、さらにクエリに関する問題それぞれに対して個別に設計する必要があります。

たとえば、ディメンション キー列 dk1 と dk2 を含む、ファクト テーブル F1 があるとします。 M が、集計関数 (SUM など) であるとします。 M(dk1) を参照するクエリを実行するたびに列 dk1 に対して M を計算するのではなく、概要テーブル F2(dk1, M) を作成して使用することで、結果を事前に算出でき、クエリの実行速度を向上することができます。 ただし、M(dk2) を参照する新しいクエリが必要になった場合は、この情報を含む新しい概要テーブル F3(dk2, M) を作成する必要があります。 テーブル内の列数が増大し、使用される可能性のある関数が増えると、この手法を維持することが難しくなり、必要なクエリをすべて扱うことが容易ではなくなります。

このオーバーヘッドが、ユーザーに大きな影響を及ぼす可能性があります。 ユーザーは SQL Server 列ストア インデックスを使用することで、他のソリューションのオーバーヘッドを減らすことができます。 また、列ストア インデクスを使用するとクエリの計算速度が向上するため、事前計算が不要になります。

SQL Server の列ストア テクノロジの特徴は、次のとおりです。

• カラムナ (列指向) データ形式: 従来のような行ベースのデータ組織 (行ストア形式と呼ばれます) とは異なり、列ストア インデックスが使用される SQL Server などの列指向データベース システムでは、データは一度に 1 つの列としてグループ化され、格納されます。 SQL Server クエリ プロセッサではこの新しいデータ レイアウトを利用でき、クエリの実行回数を大幅に改善することができます。

• クエリ結果の高速化: 列ストア インデックスの次の特徴によって、結果をより高速に生成できます。

  • 必要な列のみを読み取ります。 そのため、ディスクから読み取ってメモリに送られるデータの量が減り、後でメモリからプロセッサ キャッシュに送られるデータも減ります。

  • 列が効率的に圧縮されます。 これにより、読み取りや移動の必要があるデータの量が減ります。

  • ほとんどのクエリでは、テーブルの一部の列のみを操作します。 そのため、メモリに移動されない列が多数あります。 これに加えて圧縮効率も優れているため、バッファー プールの使用率が改善され、全体の入出力が減少します。

  • 高度なクエリ実行テクノロジにより、バッチと呼ばれる列のまとまりが効率的に処理され、CPU 使用率が下がります。

• キー列: 列ストア インデックスにはキー列の概念がないため、1 つのインデックスにおけるキー列の数の制限 (16) は列ストア インデックスには適用されません。

• クラスター化インデックス キー: ベース テーブルがクラスター化インデックスの場合、クラスター化されていない列ストア インデックスには、クラスター化キーのすべての列が含まれている必要があります。 CREATE INDEX ステートメントでクラスター化キーの列が指定されていない場合、その列は自動的に列ストア インデックスに追加されます。

• 分割: 列ストア インデックスとテーブル分割を組み合わせて使用できます。 テーブル分割の構文に変更を加える必要はありません。 パーティション テーブルの列ストア インデックスは、ベース テーブルにパーティションで固定する必要があります。 そのため、パーティション列が列ストア インデックスの列の 1 つである場合、非クラスター化列ストア インデックスはパーティション テーブルにのみ作成できます。

• レコード サイズ: インデックス キー レコードの 900 バイトのサイズ制限も、列ストア インデックスには適用されません。

• クエリ処理: SQL Server では、列ストア インデックスに加えてバッチ処理を導入し、データの列指向を利用しています。 列ストア構造とバッチ処理はどちらもパフォーマンス向上に役立ちますが、どちらか一方のみを利用した場合よりも、パフォーマンスの問題の調査が難しくなる可能性があります。

• テーブル更新不可: SQL Server 2012 の場合、列ストア インデックスを含むテーブルは更新できません。 この問題を回避するには、「ベスト プラクティス: 列ストア インデックスのデータの更新」を参照してください。

列ストア インデックスを作成するための構文については、「CREATE COLUMNSTORE INDEX (Transact-SQL)」を参照してください。

データ型

一般的なビジネス データ型を列ストア インデックスに含めることができます。 以下のデータ型を列ストア インデックスに含めることができます。

• char およびvarchar

• nchar および nvarchar (varchar(max) および nvarchar(max) を除く)

• decimal (および numeric) (有効桁数が 18 桁よりも多い場合を除く)

• int、bigint、smallint、および tinyint

• float (および real)

• bit

• money および smallmoney

• すべての日付と時刻のデータ型 (datetimeoffset で、小数点以下桁数が 2 を超える場合を除く)

以下のデータ型は、列ストア インデックスに含めることができません。

• binary および varbinary

• ntext、text、および image

• varchar(max) および nvarchar(max)

• uniqueidentifier

• rowversion (および timestamp)

• sql_variant

• decimal (および numeric) で、有効桁数が 18 桁よりも多い場合

• datetimeoffset で、小数点以下桁数が 2 を超える場合

• CLR 型 (hierarchyid および空間型)

• xml

パフォーマンスの低下の可能性

意志決定支援クエリのパフォーマンスは、多くの場合、大きなテーブルに対して列ストア インデックスを使用することで改善されますが、クエリによっては悪化する場合があり、またワークロード全体のパフォーマンスも悪化する場合があります。 一般的に、クエリ オプティマイザーではコストベースの手法を使用し、列ストア インデックスによってクエリの全体的なパフォーマンスが向上する場合にのみ、列ストア インデックスの使用を決定します。 ただし、オプティマイザーが使用するコスト モデルは概算であり、行ストア (B ツリーまたはヒープ) を使用してテーブルにアクセスした方がパフォーマンスの向上が見込めた場合でも、そのテーブルに列ストア インデックスが使用されることがあります。 その場合、IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX クエリ ヒントを使用するか、インデックス ヒントを使用して、オプティマイザーに列ストア インデックスを指定します。 それでもオプティマイザーには、列ストア インデックスの情報が含まれる場合があります。 そのため、ごくまれに、この方法でもパフォーマンスの問題が解決されないことがあります。 列ストア インデックスを使用してもワークロードのパフォーマンスが改善せず、インデックス ヒントを使用しても問題を解決できない場合は、列ストア インデックスを削除し、行ストア処理に戻してください。

問題の領域

SQL Server 列ストア インデックスと列ベースのクエリ処理は、大規模なファクト テーブルを含む一般的なデータ ウェアハウス クエリに最適化されています。また、スター スキーマ構成で結合され、グループ化され、集計される小規模のディメンション テーブルにも適しています。 このようなクエリは、ファクト テーブル内の行数が大きい場合でもデータが集計されるため、一般的に小さな結果セットを返します。 列ストア インデックスを使用するクエリのパフォーマンスは、次のいずれかの条件に該当する場合には、低下する可能性があります。

• データが集計されていないため結果セットが大きい場合。 大きな結果セットを返すときには、本質的に、小さな結果セットを返すよりも時間がかかります。

• 結合、フィルター処理、集計のいずれも実行されない場合。 この場合、バッチ処理が発生しません。 そのため、列ストア インデックスの効果は圧縮と読み取り列数の削減に限られます。

• 2 つの大きなテーブルを結合する必要があり、その際にメモリに収まらない大きなハッシュ テーブルが作成され、ディスクへの書き込みが必要になる場合。

• 多数の列が返されることにより、取得する列ストア インデックスの数が増える場合。

• 列ストア インデックスに対する結合条件に、複数の列が含まれている場合。

以上のいずれかの原因で処理速度が低下した場合、このセクションで説明した方法を使用して解決することができます。

先頭に戻る

基礎: 列ストア インデックスの制約と制限

基本的な制限

列ストア インデックスには、次の制限があります。

• 1,024 より多い列を持つことはできません。

• クラスター化できません。 非クラスター化列ストア インデックスのみが利用可能です。

• 一意なインデックスにすることはできません。

• ビューまたはインデックス付きビュー上では作成できません。

• スパース列を含めることはできません。

• 主キーまたは外部キーとして機能することはできません。

• ALTER INDEX ステートメントを使用して変更することはできません。 そのため、削除して再作成する必要があります。 ALTER INDEX を使用して、列ストア インデックスを無効にしてから再作成することができます。

• INCLUDE キーワードを使用して作成することはできません。

• インデックスを並べ替えるための ASC または DESC キーワードを含めることはできません。 列ストア インデックスは、圧縮アルゴリズムに従って順序付けされます。 インデックスには、並べ替えは許可されていません。 列ストア インデックスから選択された値が検索アルゴリズムによって並べ替えられる場合がありますが、結果セットを確実に並べ替えるには、ORDER BY 句を使用する必要があります。

• 従来のインデックスの方法で統計を使用または保持しません。

• FILESTREAM 属性を持つ列を含めることはできません。 インデックスで使用されていないテーブルの他の列には、FILESTREAM 属性を含めることができます。

列ストア インデックスを持つテーブルは更新できない

この問題を回避するには、「ベスト プラクティス: 列ストア インデックスのデータの更新」を参照してください。

メモリの制約の影響

列ストア処理は、メモリ内の処理に最適化されています。 SQL Server は、メモリ不足に陥ったときにデータおよび大部分のデータ構造をディスクに書き込む機能を備えています。 メモリの制約が厳しい場合、処理に行ストアが使用されます。 アクセス方法として列ストア インデックスが選択されているが、必要なデータ構造を構築するにはメモリが足りない状況が発生する場合もあります。 クエリ処理に厳しいメモリ制限が適用される場合、最初に列ストア処理が行われた後で既定の低速のコード パスに移行するため、ある程度パフォーマンスが低下することがあります。 すべてのクエリで効果的なメモリ要件は、特定のクエリによって決まります。 列ストア インデックスを構築するには、インデックス内の列数 × DOP (並列処理の次数) × 8 MB に相当するメモリが必要です (概算値)。一般的に、文字列である列の比率が増えると、必要なメモリの量も増加します。 そのため、DOP を減らすことで、列ストア インデックスを構築するために必要なメモリを減らすことができます。

一部の式の評価が他の式よりも高速化される

列ストア インデックスが使用される場合、一部の一般的な式は、一度に 1 つの行を処理するモードではなくバッチ モードを使用して評価されます。 列ストア インデックスの使用によるクエリの高速化に加え、バッチ モードによってさらに高速化されます。 一部のクエリ実行演算子はバッチ モード処理に対応しません。

列ストア インデックスは SEEK をサポートしない

クエリによって返される行がごくわずかであると予想される場合、オプティマイザーによって列ストア インデックスが選択される可能性はほとんどありません (例: "干し草の中の針" タイプのクエリ)。 テーブル ヒントの FORCESEEK を使用すると、オプティマイザーでは、列ストア インデックスが検討されなくなります。

列ストア インデックスと同時に使用できない機能:

• ページと行の圧縮、および vardecimal ストレージ形式 (列ストア インデックスは、既に別の形式で圧縮されているため)

• レプリケーション

• 変更の追跡

• 変更データ キャプチャ

• ファイルストリーム

先頭に戻る

使用例: パーティション テーブルを含む列ストア インデックス

このトピックの例では、 AdventureWorksDW2012 サンプル データベース内の FactResellerSalesPtnd という名前のパーティション テーブルを使用します。 パーティション テーブルで列ストア インデックスをテストするには、 AdventureWorksDW2012 データベースに接続し、次のコードを実行して、ファクト テーブルをパーティション分割したテーブルを作成します。

注
サンプル データベースの詳細と、データベースのダウンロード方法の詳細については、「AdventureWorks サンプル データベース」を参照してください。

FactResellerSalesPtnd テーブルの作成

• 次のコードを実行して、FactResellerSales テーブルをパーティション分割した、FactResellerSalesPtnd という名前のテーブルを作成します。

  USE AdventureWorksDW2012;
  GO
  
  CREATE PARTITION FUNCTION [ByOrderDateMonthPF](int) AS RANGE RIGHT 
  FOR VALUES (
      20050701, 20050801, 20050901, 20051001, 20051101, 20051201, 
      20060101, 20060201, 20060301, 20060401, 20060501, 20060601, 
      20060701, 20060801, 20060901, 20061001, 20061101, 20061201, 
      20070101, 20070201, 20070301, 20070401, 20070501, 20070601, 
      20070701, 20070801, 20070901, 20071001, 20071101, 20071201, 
      20080101, 20080201, 20080301, 20080401, 20080501, 20080601, 
      20080701, 20080801, 20080901, 20081001, 20081101, 20081201
  ) 
  GO
  
  CREATE PARTITION SCHEME [ByOrderDateMonthRange] 
  AS PARTITION [ByOrderDateMonthPF] 
  ALL TO ([PRIMARY]) 
  GO
  
  -- Create a partitioned version of the FactResellerSales table
  CREATE TABLE [dbo].[FactResellerSalesPtnd]( 
      [ProductKey] [int] NOT NULL, 
      [OrderDateKey] [int] NOT NULL, 
      [DueDateKey] [int] NOT NULL, 
      [ShipDateKey] [int] NOT NULL, 
      [CustomerKey] [int] NOT NULL, 
      [EmployeeKey] [int] NOT NULL, 
      [PromotionKey] [int] NOT NULL, 
      [CurrencyKey] [int] NOT NULL, 
      [SalesTerritoryKey] [int] NOT NULL, 
      [SalesOrderNumber] [nvarchar](20) NOT NULL, 
      [SalesOrderLineNumber] [tinyint] NOT NULL, 
      [RevisionNumber] [tinyint] NULL, 
      [OrderQuantity] [smallint] NULL, 
      [UnitPrice] [money] NULL, 
      [ExtendedAmount] [money] NULL, 
      [UnitPriceDiscountPct] [float] NULL, 
      [DiscountAmount] [float] NULL, 
      [ProductStandardCost] [money] NULL, 
      [TotalProductCost] [money] NULL, 
      [SalesAmount] [money] NULL, 
      [TaxAmt] [money] NULL, 
      [Freight] [money] NULL, 
      [CarrierTrackingNumber] [nvarchar](25) NULL, 
      [CustomerPONumber] [nvarchar](25) NULL,
      OrderDate [datetime] NULL,
      DueDate [datetime] NULL,
      ShipDate [datetime] NULL
  ) ON ByOrderDateMonthRange(OrderDateKey);
  GO
  
  -- Using simple or bulk logged recovery mode, and then the TABLOCK 
  -- hint on the target table of the INSERT…SELECT is a best practice
  -- because it causes minimal logging and is therefore much faster.
  ALTER DATABASE AdventureWorksDW2012 SET RECOVERY SIMPLE;
  GO
  
  -- Copy the data from the FactResellerSales into the new table
  INSERT INTO dbo.FactResellerSalesPtnd WITH(TABLOCK)
  SELECT * FROM dbo.FactResellerSales;
  GO
  
  -- Create the columnstore index
  CREATE NONCLUSTERED COLUMNSTORE INDEX [csindx_FactResellerSalesPtnd]
  ON [FactResellerSalesPtnd]
  ( 
      [ProductKey], 
      [OrderDateKey], 
      [DueDateKey], 
      [ShipDateKey], 
      [CustomerKey], 
      [EmployeeKey], 
      [PromotionKey], 
      [CurrencyKey], 
      [SalesTerritoryKey], 
      [SalesOrderNumber], 
      [SalesOrderLineNumber], 
      [RevisionNumber], 
      [OrderQuantity], 
      [UnitPrice], 
      [ExtendedAmount], 
      [UnitPriceDiscountPct], 
      [DiscountAmount], 
      [ProductStandardCost], 
      [TotalProductCost], 
      [SalesAmount], 
      [TaxAmt], 
      [Freight], 
      [CarrierTrackingNumber], 
      [CustomerPONumber], 
      [OrderDate],
      [DueDate],
      [ShipDate]
  );
  

次に、列ストア インデックスの効果が得られるクエリを実行し、列ストア インデックスが使用されたことを確認します。

列ストア インデックスのテスト

1. Ctrl キーを押しながら M キーを押すか、[クエリ] メニューの [実際の実行プランを含める] をクリックします。 これで、SQL Server Management Studio によって使用される実際の実行プランのグラフィカル表示が有効になります。

2. クエリ エディター ウィンドウで、次のクエリを実行します。

   SELECT SalesTerritoryKey, SUM(ExtendedAmount) AS SalesByTerritory
   FROM FactResellerSalesPtnd
   GROUP BY SalesTerritoryKey;
   

   [結果] ウィンドウの [実行プラン] タブを開き、クラスター化されていない csindx_FactResellerSalesPtnd インデックスのインデックス スキャンがクエリ プランで選択されていることを確認します。

   注
   グラフィカルなプラン表示アイコンの詳細については、「プラン表示の論理操作と物理操作のリファレンス」を参照してください。

   先頭に戻る

基礎: 一般的な列ストア インデックスのシナリオ

スター データベース スキーマおよびスノーフレーク データベース スキーマは、通常、データの操作効率よりもデータの取得速度が重視される多次元データ ウェアハウスやデータ マートで使用されます。 SQL Server 2012 の列ストア テクノロジにより、スター スキーマおよびスノーフレーク スキーマを対象とするクエリを検出し、高速化することができます。

例:

注
以降の例ではテーブルがそれほど大きくないため、バッチ処理が使用されない場合があります。 並列処理などのバッチ実行モードは、コストの高いクエリにのみ使用されます。

A: 2 つのテーブルを結合する集計クエリ

• 製品 215 の販売数を四半期単位で計算するスター結合クエリがあるとします。 FactResellerSalesPtnd という名前のファクト テーブルが、OrderDateKey 列で分割されます。 DimDate という名前の 1 つのディメンション テーブルが、日付キーでの主キーと外部キーのリレーションシップを利用してファクト テーブルにリンクされます。

  SELECT d.CalendarYear,
      d.CalendarQuarter,
      COUNT(*) AS NumberSold
  FROM dbo.FactResellerSalesPtnd AS f
      JOIN dbo.DimDate AS d
      ON f.OrderDateKey = d.DateKey
  WHERE ProductKey = 215
  GROUP BY d.CalendarYear, d.CalendarQuarter
  ORDER BY d.CalendarYear, d.CalendarQuarter;
  

また、単一のテーブルのみを対象とするクエリが使用される場合も考えられます。 そのような場合、SQL Server ではバッチ実行と列ストア テクノロジを利用して、クエリの実行速度の向上を試みます。

B: 1 つのテーブルを対象とする単純な集計クエリ

• ファクト テーブルは FactResellerSalesPtnd です。このテーブルは OrderDateKey 列で分割されます。 次のクエリは、行数と注文の数を返します。

  SELECT COUNT(*) AS NumberOfRows, 
      COUNT(DISTINCT(f.SalesOrderNumber)) AS NumberOfOrders
  FROM dbo.FactResellerSalesPtnd AS f;
  

  一般的なデータ ウェアハウスのシナリオのクエリの場合、クエリの実行に列ストア インデックスとバッチ実行モードを使用すると、実行速度が一般的に 1.5 倍～ 10 倍向上します。 一部のスター結合クエリでは、その効果はもっと大きくなります。

基礎: ビットマップ フィルターの最適化

SQL Server では、列指向形式のデータのレイアウトだけでなく、クエリ実行時のパフォーマンスを高めるためにストレージ エンジンに渡されるビットマップ フィルターも使用されます。 このビットマップ フィルターにより、結合が実行される前に、含まれる行の数を削減し、結合演算子で処理する行の数を減らすことで、クエリの実行速度を向上します。 ビットマップはハッシュ結合のビルド側に作成されますが、実際のビットマップ チェックはハッシュ結合のプローブ側で実行されます。 グラフィカル実行プランまたは XML 実行プランを使用して、ビットマップ フィルターの使用状況を調べることができます。

ベスト プラクティス: 列ストア インデックスのデータの更新

列ストア インデックスを含むテーブルは更新できません。 この問題は、次の 3 つの方法で回避できます。

• 列ストア インデックスを含むテーブルを更新するには、列ストア インデックスを削除し、必要に応じて INSERT、DELETE、UPDATE、または MERGE の操作を実行した後で、列ストア インデックスを再構築します。

• テーブルをパーティション分割して、パーティションを切り替えます。 一括挿入の場合、ステージング テーブルにデータを挿入し、そのステージング テーブルに列ストア インデックスを構築した後、そのステージング テーブルを空のパーティションに切り替えます。 それ以外の更新の場合、メイン テーブルのパーティションをステージング テーブルに変換し、そのステージング テーブルの列ストア インデックスを無効にするか削除し、更新操作を実行します。その後、ステージング テーブルに列ストア インデックスを再構築するか再作成して、ステージング テーブルをメイン テーブルに戻します。

• 静的データは列ストア インデックスを含むメイン テーブルに格納します。新しいデータ、および変更される可能性が高い最新のデータは、列ストア インデックスを含まず、スキーマが同じである別のテーブルに格納します。 最新のデータを含むテーブルに更新を適用します。 データのクエリを実行するには、そのクエリを各テーブルに対応する 2 つのクエリとして再作成し、UNION ALL を使用してその 2 つの結果セットを結合します。 大きなメイン テーブルに対してサブクエリを実行する際に、列ストア インデックスの効果が得られます。 それと比較して、更新可能なテーブルが十分に小さければ、列ストア インデックスがなくてもパフォーマンスに悪影響が及ぶことはありません。 2 つのテーブルの UNION ALL であるビューに対してクエリを実行することもできますが、パフォーマンス上の利点が小さくなる可能性があります。 パフォーマンスはクエリ プランに左右されます。また、クエリ プランはクエリ、データ、および基数の推定値の影響を受けます。 ビューを使用する利点は、ビューの INSTEAD OF トリガーによって列ストア インデックスが含まれないテーブルに更新を迂回できることと、ユーザーやアプリケーションがビューのメカニズムを把握できることです。 UNION ALL を使用するどちらかの手法を使用する場合、一般的なクエリに対するパフォーマンスをテストし、パフォーマンス向上の効果が減少するデメリットよりもこの手法を使用するメリットの方が大きいかどうかを判断してください。

注
テーブルを読み取り専用にするためのメカニズムとして列ストア インデックスを作成しないでください。 列ストア インデックスを含むテーブルの更新に関する制限事項は、将来のリリースでは保証されていません。 読み取り専用の動作が必要な場合は、読み取り専用ファイルグループを作成して、テーブルをそのファイルグループに移動してください。

ベスト プラクティス: 列ストア インデックスの列の選択

列ストア インデックスのパフォーマンス上の利点は、クエリを処理するために読み取って操作する必要のあるデータ ページの数を減らす圧縮機能によって得られます。 圧縮機能は、重複した値が大量に含まれる文字型列や数値型列に対して使用した場合に最も効率が高くなります。 たとえば、ディメンション テーブルの中に、郵便番号、市区町村、および販売地域の列が含まれているとします。 各市区町村に多数の郵便番号があり、さらに各販売地域に多数の市区町村がある場合、販売地域の列の圧縮率が最も高くなります。市区町村の列はそれよりも圧縮率が低く、郵便番号の列の圧縮率が最も低くなります。 どの列も列ストア インデックスに適していますが、販売地域コードの列を列ストア インデックスを追加することで列ストア圧縮の効果を最大にすることができます。郵便番号の列を追加した場合、その効果は最も小さくなります。

先頭に戻る

ベスト プラクティス: パーティション テーブル

列ストア インデックスは、一般的にパーティション分割が使用される非常に大きなデータ ウェアハウスのシナリオでクエリをサポートするために開発されました。 列ストア インデックスを含むテーブルのデータを定期的に更新する必要がある場合に、パーティション分割をお勧めします。 列ストア インデックスのパーティションを更新する方法については、前のセクション「ベスト プラクティス: 列ストア インデックスのデータの更新」を参照してください。

先頭に戻る

方法: 列ストア インデックスを作成する

列ストア インデックスの作成は、一般的なインデックスの作成と似ています。 列ストア インデックスを作成するには、Transact-SQL を使用するか、SQL Server Management Studio のグラフィカル ツールを使用します。

Transact-SQL を使用した列ストア インデックスの作成

• クエリ エディター ウィンドウで、CREATE COLUMNSTORE INDEX ステートメントを実行します。 例については、前の「FactResellerSalesPtnd テーブルの作成」を参照してください。 詳細については、「CREATE COLUMNSTORE INDEX (Transact-SQL)」を参照してください。

SQL Server Management Studio を使用した列ストア インデックスの作成

1. Management Studio のオブジェクト エクスプローラーを使用して SQL Server データベース エンジンのインスタンスに接続します。

2. オブジェクト エクスプローラーで、SQL Server インスタンスを展開し、[データベース] を展開します。データベースを展開してからテーブルを展開し、テーブルを右クリックして [新しいインデックス] をポイントして、[非クラスター化列ストア インデックス] をクリックします。

3. [インデックス名] ダイアログ ボックスの [全般] タブで、新しいインデックスの名前を入力し、[追加] をクリックします。

4. [複数列の選択] ダイアログ ボックスで、列ストア インデックスに追加する列を選択し、[OK] を 2 回クリックします。インデックスが作成されます。

方法: 列ストア インデックスのサイズを判断する

列ストア インデックスは、セグメントと辞書の両方で構成されます。 次の例では、列ストア インデックス (FactResellerSalesPtnd テーブル内) の合計サイズを、sys.column_store_segments および sys.column_store_dictionaries の on_disk_size 列を結合することで判断します。

SELECT SUM(on_disk_size_MB) AS TotalSizeInMB
FROM
(
   (SELECT SUM(css.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
    FROM sys.indexes AS i
    JOIN sys.partitions AS p
        ON i.object_id = p.object_id 
    JOIN sys.column_store_segments AS css
        ON css.hobt_id = p.hobt_id
    WHERE i.object_id = object_id('FactResellerSalesPtnd') 
    AND i.type_desc = 'NONCLUSTERED COLUMNSTORE') 
  UNION ALL
   (SELECT SUM(csd.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
    FROM sys.indexes AS i
    JOIN sys.partitions AS p
        ON i.object_id = p.object_id 
    JOIN sys.column_store_dictionaries AS csd
        ON csd.hobt_id = p.hobt_id
    WHERE i.object_id = object_id('FactResellerSalesPtnd') 
    AND i.type_desc = 'NONCLUSTERED COLUMNSTORE') 
) AS SegmentsPlusDictionary

方法: 列ストア インデックスのパフォーマンスのトラブルシューティングを実行する

列ストア インデックスが使用されているかどうかを判断するには、クエリ実行プランを調べます。 次の 3 つの要素が表示されているときに、最大の効果が得られます。

• クエリ実行プランに列ストア インデックスが表示されている。

  

  Columnstore Index Scan 操作アイコン

  列ストア インデックスが使用されていないときに、列ストアを使用することでクエリのパフォーマンスが向上すると考えられる場合、WITH (INDEX(<indexname>)) ヒントを使用して強制的に列ストア インデックスを使用する際に、クエリのパフォーマンスを評価します。 次に、インデックス ヒントを使用したクエリの例を示します。

  SELECT d.CalendarYear,
      d.CalendarQuarter,
      COUNT(*) AS NumberSold
  FROM dbo.FactResellerSalesPtnd AS f WITH (INDEX(csindx_FactResellerSalesPtnd))
      JOIN dbo.DimDate AS d
      ON f.OrderDateKey = d.DateKey
  WHERE ProductKey = 215
  GROUP BY d.CalendarYear, d.CalendarQuarter
  ORDER BY d.CalendarYear, d.CalendarQuarter;
  

• グラフィカルなクエリ プランで、列ストア インデックスのアイコンにマウス ポインターを合わせると、実際の実行モードが行ではなくバッチであると表示される。

• グラフィカルな実行プランに Bitmap 物理操作のアイコンが表示される。これは、ビットマップ フィルターによって結合処理の前に行数が削減されていることを示しています。

  

  Bitmap 操作アイコン

先頭に戻る

関連タスク

CREATE COLUMNSTORE INDEX (Transact-SQL)

関連コンテンツ

sys.column_store_dictionaries (Transact-SQL)

sys.column_store_segments (Transact-SQL)