リアルタイムの運用分析を始めるための列ストア インデックスの活用方法

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric の SQL データベース

SQL Server 2016 (13.x) にはリアルタイム運用分析が導入されており、同じデータベース テーブル上で分析ワークロードと OLTP ワークロードの両方を同時に実行できます。 分析をリアルタイムで実行するだけでなく、ETL やデータ ウェアハウスも不要になります。

リアルタイムの運用分析について説明

従来、企業は運用 (OLTP) ワークロードと分析ワークロード用に別個のシステムを保有していました。 このようなシステムでは、抽出、変換、読み込み (ETL) ジョブにより、データを運用ストアから分析ストアに定期的に移動します。 分析データは通常、分析クエリを専門的に実行するデータ ウェアハウスやデータ マートに格納されています。 今まではこのソリューションが標準でしたが、主に 3 つの課題を抱えています。

• Complexity. ETL の実装には、修正された行を読み込むだけでも大量のコーディングを必要とする場合があります。 変更された行を識別するのが困難なこともあります。
• Cost. ETL の実装には、追加のハードウェアやソフトウェアのライセンスを購入するためのコストが必要です。
• データ待機時間。 ETL の実装により、分析の実行に時間遅延が発生します。 たとえば、ETL ジョブを各営業日の最後に実行する場合、分析クエリは少なくとも 1 日前のデータに対して実行されます。 多くの企業にとって、ビジネスの基盤はリアルタイムでデータを分析することにあるため、この遅延は許容されません。 たとえば、不正行為の検出には運用データに対するリアルタイムの分析が必要です。

リアルタイム運用分析は、これらの課題を解決します。

分析ワークロードと OLTP ワークロードの実行の基になるテーブルが同じであるため、時間遅延は発生しません。 リアルタイム分析を使用できるシナリオでは、ETL が不要になり、別個のデータ ウェアハウスを購入して維持する必要がなくなるため、コストと複雑さが大幅に軽減されます。

Note

リアルタイム運用分析は、運用ワークロードと分析ワークロードの両方を実行できる、エンタープライズ リソース プランニング (ERP) アプリケーションなどの単一データソースのシナリオをターゲットにしています。 分析ワークロードを実行する前に複数のソースからのデータを統合する必要がある、またはキューブなどの事前集計データを使用して高度な分析パフォーマンスを必要とするときは、別個のデータ ウェアハウスが必要になることもあります。

リアルタイム分析では、行ストア テーブルで更新可能な非クラスター化列ストア インデックスが使用されます。 列ストア インデックスは、OLTP ワークロードと分析ワークロードが同じデータの別のコピーに対して実行されるように、データのコピーを管理します。 これにより、両方のワークロードを同時に実行してもパフォーマンス上の影響を最小限に抑えます。 データベース エンジンはインデックスの変更を自動的に管理するため、OLTP の変更が分析のために常に最新の状態に保たれます。 この設計により、最新のデータに対するリアルタイム分析を実現しています。 これは、ディスク ベース テーブルとメモリ最適化テーブルの両方で機能します。

作業開始の例

リアルタイム分析を開始するには、次の手順に従います。

1. 分析に必要なデータを含む、運用スキーマ内のテーブルを識別します。

2. 各テーブルに対して、本来 OLTP ワークロードでの既存の分析を高速化するために設計された B ツリー インデックスをすべて削除します。 それらを単一の非クラスター化列ストア インデックスに置き換えます。 これにより、維持するインデックスが少なくなるため、OLTP ワークロードの全体的なパフォーマンスを向上させることができます。

   --This example creates a nonclustered columnstore index on an existing OLTP table.
   --Create the table
   CREATE TABLE t_account (
       accountkey int PRIMARY KEY,
       accountdescription nvarchar (50),
       accounttype nvarchar(50),
       unitsold int
   );
   
   --Create the columnstore index with a filtered condition
   CREATE NONCLUSTERED COLUMNSTORE INDEX account_NCCI
   ON t_account (accountkey, accountdescription, unitsold)
   ;
   

   メモリ最適化テーブルの列ストア インデックスを使用すると、インメモリ OLTP と列ストア テクノロジを統合して運用分析を行い、OLTP ワークロードと分析ワークロードの両方に高いパフォーマンスを提供できます。 メモリ最適化テーブルの列ストア インデックスはクラスター化インデックスである必要があります。つまり、すべての列を含める必要があります。

   -- This example creates a memory-optimized table with a columnstore index.
   CREATE TABLE t_account (
       accountkey int NOT NULL PRIMARY KEY NONCLUSTERED,
       Accountdescription nvarchar (50),
       accounttype nvarchar(50),
       unitsold int,
       INDEX t_account_cci CLUSTERED COLUMNSTORE
       )
       WITH (MEMORY_OPTIMIZED = ON );
   

アプリケーションに変更を加えることがなく、リアルタイム運用分析を実行する準備が整いました。 分析クエリは列ストア インデックスに対して実行され、OLTP 操作は OLTP B ツリー インデックスに対して継続的に実行されます。 OLTP ワークロードは引き続き実行されますが、列ストア インデックスを維持するために追加のオーバーヘッドが発生します。 次のセクションでパフォーマンスの最適化について説明します。

ブログの投稿

リアルタイム運用分析の詳細については、次のブログ記事を参照してください。 パフォーマンス ヒントのセクションが理解しやすくなるように、まずこちらのブログ記事を読むことをお勧めします。

• リアルタイム運用分析のビジネス ケース

• 非クラスター化列ストア インデックスを使用したリアルタイム運用分析

• 非クラスター化列ストア インデックスの使用方法に関する簡単な例

• トランザクション ワークロードで SQL Server が非クラスター化列ストア インデックスを管理する方法

• フィルター処理されたインデックスを使用して非クラスター化列ストア インデックスのメンテナンスの影響を最小限に抑える

• 圧縮遅延を使用して非クラスター化列ストア インデックスのメンテナンスの影響を最小限に抑える

• 圧縮遅延を使用して非クラスター化列ストア インデックスのメンテナンスの影響を最小限に抑える - パフォーマンス番号

• メモリ最適化テーブルによるリアルタイム運用分析

• 列ストア インデックスと行グループのマージ ポリシー

Videos

Data Exposed ビデオ シリーズでは、いくつかの機能と考慮事項について詳しく説明します。

• パート 1: Azure SQL でリアルタイムの運用分析 (HTAP) を有効にする方法
• パート 2: 運用分析を使用して既存のデータベースとアプリケーションを最適化する
• パート 3: Window Functions を使用して運用分析を作成する方法。

パフォーマンス ヒント 1: フィルター処理されたインデックスを使用したクエリ パフォーマンスの改善

リアルタイム分析の運用を実行すると、OLTP ワークロードのパフォーマンスに影響を及ぼすことがあります。 この影響は最小限に抑える必要があります。 例 A は、フィルター選択されたインデックスを使用して、非クラスター化列ストア インデックスがトランザクション ワークロードに与える影響を最小限に抑えながら、リアルタイムで分析を提供する方法を示しています。

運用ワークロード上で非クラスター化列ストア インデックスを管理するために必要なオーバーヘッドを最小限に抑えるには、フィルター処理条件を使用して ウォーム データ (緩やかに変化するデータ) 対してのみ非クラスター化列ストア インデックスを作成します。 たとえば、注文管理アプリケーションの場合、既に出荷されている注文に対して非クラスター化列ストア インデックスを作成できます。 注文が出荷された後はほとんど変化しないため、ウォーム データと捉えることができます。 フィルター選択されたインデックスを使用すると、非クラスター化列ストア インデックス内のデータに必要な更新が少なくなり、トランザクション ワークロードへの影響が軽減されます。

分析クエリは、ウォーム データおよび必要に応じてホット データに透過的にアクセスすることでリアルタイム分析を提供します。 運用ワークロードの重要な部分が "ホット" データに触れている場合、それらの操作では列ストア インデックスの追加のメンテナンスは必要ありません。 ベストプラクティスとしては、フィルターされたインデックス定義に使用される列に対して、行ストア形式のクラスタ化インデックスを持たせることです。 データベース エンジンは、クラスター化インデックスを使用して、フィルター処理された条件を満たしていない行をすばやくスキャンします。 このクラスター化インデックスがない場合、これらの行を検索するには行ストア テーブルの完全なテーブル スキャンが必要であり、分析クエリのパフォーマンスに悪影響を与える可能性があります。 クラスター化インデックスが存在しない場合は、補足的にフィルター処理された非クラスター化 B ツリー インデックスを作成してそのような行を特定できますが、非クラスター化 B ツリー インデックスを通じた広範囲の行に対するアクセスは高コストであるため、お勧めしません。

Note

フィルター処理された非クラスター化列ストア インデックスは、ディスク ベースのテーブルに対してのみサポートされます。 メモリ最適化テーブルではサポートされていません。

例 A: B-tree インデックスからホット データ、列ストア インデックスからウォーム データにアクセスする

この例では、フィルター処理された条件 (accountkey > 0) を使用して、列ストア インデックスに含まれる行を確立します。 目標は、フィルター処理条件と後続のクエリを設計し、頻繁に変化する B ツリー インデックスからの「ホット」データにアクセスすることと、より安定した列ストア インデックスからの「ウォーム」データにアクセスすることです。

Note

クエリ オプティマイザーは、クエリ プランの列ストア インデックスを考慮しますが、常に選択するとは限りません。 クエリ オプティマイザーがフィルター処理された列ストア インデックスを選択すると、列ストア インデックスからの行とフィルター処理条件を満たしていない行を透過的に結合され、リアルタイム分析を実現します。 これは、通常のフィルター処理された非クラスター インデックスとは異なり、インデックスに存在する行に限定されたクエリでのみ使用できます。

-- Use a filtered condition to separate hot data in a rowstore table
-- from "warm" data in a columnstore index.

-- create the table
CREATE TABLE orders (
AccountKey int not null,
CustomerName nvarchar (50),
OrderNumber bigint,
PurchasePrice decimal (9,2),
OrderStatus smallint not null,
OrderStatusDesc nvarchar (50)
);

-- OrderStatusDesc  
-- 0 => 'Order Started'  
-- 1 => 'Order Closed'  
-- 2 => 'Order Paid'  
-- 3 => 'Order Fulfillment Wait'  
-- 4 => 'Order Shipped'  
-- 5 => 'Order Received'  

CREATE CLUSTERED INDEX orders_ci ON orders(OrderStatus);

--Create the columnstore index with a filtered condition
CREATE NONCLUSTERED COLUMNSTORE INDEX orders_ncci ON orders  (accountkey, customername, purchaseprice, orderstatus)
WHERE OrderStatus = 5;

-- The following query returns the total purchase done by customers for items > $100 .00
-- This query will pick  rows both from NCCI and from 'hot' rows that are not part of NCCI
SELECT TOP (5) CustomerName, SUM(PurchasePrice)
FROM orders
WHERE PurchasePrice > 100.0
GROUP BY CustomerName;

分析クエリは、次のクエリ プランで実行されます。 フィルター処理条件を満たしていない行には、クラスター化 B ツリー インデックスを通じてアクセスされます。

詳細については、「 Blog: フィルター処理された非クラスター化列ストア インデックス」を参照してください。

パフォーマンス ヒント 2: AlwaysOn 読み取り可能セカンダリに対する分析の負荷を軽減する

フィルター処理された列ストア インデックスを使用して、列ストア インデックスのメンテナンスを最小限に抑えることはできますが、それでも分析クエリには多大なコンピューティング リソース (CPU、I/O、メモリ) が必要であり、運用ワークロードのパフォーマンスに影響します。 ほとんどのミッション クリティカルなワークロードについては、AlwaysOn 構成を使用することをお勧めします。 この構成では、負荷を読み取り可能セカンダリにオフロードすることで、実行中の分析の影響を除去できます。

パフォーマンスのヒント #3: デルタ行グループにホット データを保持してインデックスの断片化を減らす

ワークロードが圧縮された行を更新または削除すると、列ストア インデックスを持つテーブルが大幅に断片化 (つまり、削除された行) になる可能性があります。 断片化された列ストア インデックスは、メモリと記憶域の非効率的な使用につながります。 また、リソースの非効率的な使用に加え、余分な I/O があることと、結果セットから削除された行をフィルター処理する必要があるため、分析クエリのパフォーマンスにも悪影響を及ぼします。

削除された行は、REORGANIZE コマンドを使用してインデックスのデフラグを実行するか、テーブル全体または影響を受けているパーティションの列ストア インデックスを再構築するまで、物理的には削除されません。 REORGANIZE と REBUILD の両方のインデックスは、高コストな操作であり、リソースを取り除いたり、ワークロードに使用されたりすることもあります。 さらに、行の圧縮が早すぎる場合は、更新によって圧縮オーバーヘッドが無駄になる可能性があるため、複数回圧縮し直す必要があります。

インデックスの断片化は、COMPRESSION_DELAY オプションを使用して最小限に抑えることができます。

-- Create a sample table
CREATE TABLE t_colstor (
accountkey int not null,
accountdescription nvarchar (50) not null,
accounttype nvarchar(50),
accountCodeAlternatekey int
);

-- Creating nonclustered columnstore index with COMPRESSION_DELAY. 
-- The columnstore index will keep the rows in closed delta rowgroup 
-- for 100 minutes after it has been marked closed.
CREATE NONCLUSTERED COLUMNSTORE INDEX t_colstor_cci ON t_colstor
(accountkey, accountdescription, accounttype)
WITH (DATA_COMPRESSION = COLUMNSTORE, COMPRESSION_DELAY = 100);

詳細については、「 Blog: Compression delay」を参照してください。

推奨されているベスト プラクティスを次に示します。

• ワークロードの挿入/クエリ: ワークロードが主にデータを挿入してクエリを実行している場合は、既定の COMPRESSION_DELAY 0 が推奨されるオプションです。 単一の DELTA 行グループに 100 万行が挿入されると、新しく挿入された行が圧縮されます。 このようなワークロードの例としては、従来の DW ワークロードや、Web アプリケーションで選択パターンを分析する必要がある場合の選択ストリーム分析があります。

• OLTP ワークロード: ワークロードが DML の負荷が高い場合 (つまり、Update、Delete、Insert の混在が多い場合)、DMV sys.dm_db_column_store_row_group_physical_statsを調べることで列ストア インデックスの断片化が発生する可能性があります。 最近圧縮された行グループ > 10% 行が削除済みとしてマークされている場合は、 COMPRESSION_DELAY オプションを使用して、行が圧縮の対象になったときに時間の遅延を追加できます。 たとえば、ワークロードで新しく挿入された行が 60 分間 "ホット" な状態にある (複数回更新される) 場合は、COMPRESSION_DELAY を 60 にすることをお勧めします。

COMPRESSION_DELAY オプションの既定値は、ほとんどのお客様に適しています。

上級ユーザーの場合は、次のクエリを実行し、過去 7 日間に削除された行の % を収集することをお勧めします。

SELECT row_group_id,
       CAST(deleted_rows AS float)/CAST(total_rows AS float)*100 AS [% fragmented],
       created_time
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('FactOnlineSales2')
      AND state_desc = 'COMPRESSED'
      AND deleted_rows > 0
      AND created_time > DATEADD(day, -7, GETDATE())
ORDER BY created_time DESC;

圧縮された行グループで削除された行の数が 20% を超える場合、それより前の行グループを 5% 以下のバリエーション (コールド グループと呼ばれます) で平坦化することで、> = (youngest_rowgroup_created_time - current_time) に設定します。 このアプローチは、安定した比較的均一なワークロードに最適です。

関連コンテンツ

• 列ストア インデックス: 概要
• 列ストア インデックス - データ読み込みガイダンス
• 列ストア インデックス - クエリ パフォーマンス
• データウェアハウジングにおける列ストアインデックス
• クエリのパフォーマンスを向上させてリソースの消費を削減するためにインデックスのメンテナンスを最適化する