列ストアを使用したリアルタイム運用分析の概要Get started with Columnstore for real-time operational analytics

適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database

SQL Server 2016 (13.x)SQL Server 2016 (13.x) にはリアルタイム運用分析が導入されており、同じデータベース テーブル上で分析ワークロードと OLTP ワークロードの両方を同時に実行できます。introduces real-time operational analytics, the ability to run both analytics and OLTP workloads on the same database tables at the same time. 分析をリアルタイムで実行するだけでなく、ETL やデータ ウェアハウスも不要になります。Besides running analytics in real time, you can also eliminate the need for ETL and a data warehouse.

説明されているリアルタイム運用分析Real-Time Operational Analytics Explained

従来、企業は運用 (OLTP) ワークロードと分析ワークロード用に別個のシステムを保有していました。Traditionally, businesses have had separate systems for operational (that is, OLTP) and analytics workloads. このようなシステムでは、抽出、変換、読み込み (ETL) ジョブにより、データを運用ストアから分析ストアに定期的に移動します。For such systems, Extract, Transform, and Load (ETL) jobs regularly move the data from the operational store to an analytics store. 分析データは通常、分析クエリを専門的に実行するデータ ウェアハウスやデータ マートに格納されています。The analytics data is usually stored in a data warehouse or data mart dedicated to running analytics queries. 今まではこのソリューションが標準でしたが、主に 3 つの課題を抱えています。While this solution has been the standard, it has these three key challenges:

  • 複雑さ。Complexity. ETL の実装には、修正された行を読み込むだけでも大量のコーディングを必要とする場合があります。Implementing ETL can require considerable coding especially to load only the modified rows. 変更された行を識別するのが困難なこともあります。It can be complex to identify which rows have been modified.

  • コスト。Cost. ETL の実装には、追加のハードウェアやソフトウェアのライセンスを購入するためのコストが必要です。Implementing ETL requires the cost of purchasing additional hardware and software licenses.

  • データ待機時間。Data Latency. ETL の実装により、分析の実行に時間遅延が発生します。Implementing ETL adds a time delay for running the analytics. たとえば、ETL ジョブを各営業日の最後に実行する場合、分析クエリは少なくとも 1 日前のデータに対して実行されます。For example, if the ETL job runs at the end of each business day, the analytics queries will run on data that is at least a day old. 多くの企業にとって、ビジネスの基盤はリアルタイムでデータを分析することにあるため、この遅延は許容されません。For many businesses this delay is unacceptable because the business depends on analyzing data in real time. たとえば、不正行為の検出には運用データに対するリアルタイムの分析が必要です。For example, fraud-detection requires real-time analytics on operational data.

リアルタイム運用分析の概要real-time operational analytics overview

リアルタイム運用分析は、これらの課題を解決します。Real-time operational analytics offers a solution to these challenges.
分析ワークロードと OLTP ワークロードの実行の基になるテーブルが同じであるため、時間遅延は発生しません。There is no time delay when analytics and OLTP workloads run on the same underlying table. リアルタイム分析を使用できるシナリオでは、ETL が不要になり、別個のデータ ウェアハウスを購入して維持する必要がなくなるため、コストと複雑さが大幅に軽減されます。For scenarios that can use real-time analytics, the costs and complexity are greatly reduced by eliminating the need for ETL and the need to purchase and maintain a separate data warehouse.

注意

リアルタイム運用分析は、運用ワークロードと分析ワークロードの両方を実行できる、エンタープライズ リソース プランニング (ERP) アプリケーションなどの単一データソースのシナリオをターゲットにしています。Real-time operational analytics targets the scenario of a single data source such as an enterprise resource planning (ERP) application on which you can run both the operational and the analytics workload. 分析ワークロードを実行する前に複数のソースからのデータを統合する必要がある、またはキューブなどの事前集計データを使用して高度な分析パフォーマンスを必要とするときは、別個のデータ ウェアハウスが必要になることもあります。This does not replace the need for a separate data warehouse when you need to integrate data from multiple sources before running the analytics workload or when you require extreme analytics performance using pre-aggregated data such as cubes.

リアルタイム分析は、行ストア テーブル上の更新可能な列ストア インデックスを使用します。Real-time analytics uses an updateable columnstore index on a rowstore table. 列ストア インデックスは、OLTP ワークロードと分析ワークロードが同じデータの別のコピーに対して実行されるように、データのコピーを管理します。The columnstore index maintains a copy of the data, so the OLTP and analytics workloads run against separate copies of the data. これにより、両方のワークロードを同時に実行してもパフォーマンス上の影響を最小限に抑えます。This minimizes the performance impact of both workloads running at the same time. SQL ServerSQL Server では、インデックスの変更が自動的に管理されるため、OLTP の変更は分析に向けて常に最新の状態に保たれます。automatically maintains index changes so OLTP changes are always up-to-date for analytics. この設計により、最新のデータに対するリアルタイム分析を実現しています。With this design, it is possible and practical to run analytics in real time on up-to-date data. これは、ディスク ベース テーブルとメモリ最適化テーブルの両方で機能します。This works for both disk-based and memory-optimized tables.

作業開始の例Get Started Example

リアルタイム分析を開始するには、次の手順に従います。To get started with real-time analytics:

  1. 分析に必要なデータを含む、運用スキーマ内のテーブルを識別します。Identify the tables in your operational schema that contain data required for analytics.

  2. 各テーブルに、主に OLTP ワークロードでの既存の分析を高速化するために設計された BTree インデックスをすべてドロップします。For each table, drop all btree indexes that are primarily designed to speed up existing analytics on your OLTP workload. それらを単一の列ストア インデックスに置き換えます。Replace them with a single columnstore index. これにより、管理するインデックスの数が少なくなるため、OLTP ワークロードの全体的なパフォーマンスが向上します。This can improve the overall performance of your OLTP workload since there will be fewer indexes to maintain.

    --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 ワークロードと分析ワークロードのパフォーマンスが向上しています。The columnstore index on an in-memory table allows operational analytics by integrating in-memory OLTP and in-memory columnstore technologies to deliver high performance for both OLTP and analytics workloads. インメモリ テーブル上の列ストア インデックスには、すべての列が含まれている必要があります。The columnstore index on an in-memory table must include all the columns.

    -- 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 );  
    GO  
    
    
  3. 必要な操作はこれだけです。This is all you need to do!

アプリケーションに変更を加えることがなく、リアルタイム運用分析を実行する準備が整いました。You are now ready to run real-time operational analytics without making any changes to your application. 分析クエリは列ストア インデックスに対して実行され、OLTP 操作は OLTP BTree インデックスに対して継続的に実行されます。Analytics queries will run against the columnstore index and OLTP operations will keep running against your OLTP btree indexes. OLTP ワークロードは引き続き実行されますが、列ストア インデックスの管理に追加のオーバーヘッドが発生します。The OLTP workloads will continue to perform, but will incur some additional overhead to maintain the columnstore index. 次のセクションでパフォーマンスの最適化について説明します。See the performance optimizations in the next section.

ブログ記事Blog Posts

リアルタイム運用分析の詳細については、次のブログ記事を参照してください。Read the following blog posts to learn more about real-time operational analytics. パフォーマンス ヒントのセクションが理解しやすくなるように、まずこちらのブログ記事を読むことをお勧めします。It might be easier to understand the performance tips sections if you look at the blog posts first.

パフォーマンス ヒント 1:フィルター処理されたインデックスを使用したクエリ パフォーマンスの改善Performance tip #1: Use filtered indexes to improve query performance

リアルタイム分析の運用を実行すると、OLTP ワークロードのパフォーマンスに影響を及ぼすことがあります。Running real-time operational analytics can impact the performance of the OLTP workload. この影響は最小限に抑える必要があります。This impact should be minimal. 次の例では、分析をリアルタイムで実行しつつ、フィルター処理されたインデックスを使用してトランザクション ワークロード上の非クラスター化列ストア インデックスの影響を最小限に抑える方法を示します。The example below shows how to use filtered indexes to minimize impact of nonclustered columnstore index on transactional workload while still delivering analytics in real time.

運用ワークロード上で非クラスター化列ストア インデックスを管理するために必要なオーバーヘッドを最小限に抑えるには、フィルター処理条件を使用して ウォーム データ (緩やかに変化するデータ) 対してのみ非クラスター化列ストア インデックスを作成します。To minimize the overhead of maintaining a nonclustered columnstore index on an operational workload, you can use a filtered condition to create a nonclustered columnstore index only on the warm or slowly changing data. たとえば、注文管理アプリケーションの場合、既に出荷されている注文に対して非クラスター化列ストア インデックスを作成できます。For example, in an order management application, you can create a nonclustered columnstore index on the orders that have already been shipped. 注文が出荷された後はほとんど変化しないため、ウォーム データと捉えることができます。Once the order has shipped, it rarely changes and therefore can be considered warm data. フィルター処理されたインデックスを使用すると、非クラスター化列ストア インデックスのデータに必要な更新プログラムの数が少なくなるため、トランザクション ワークロードへの影響が少なくなります。With Filtered index, the data in nonclustered columnstore index requires fewer updates thereby lowering the impact on transactional workload.

分析クエリは、ウォーム データおよび必要に応じてホット データに透過的にアクセスすることでリアルタイム分析を提供します。Analytics queries transparently access both warm and hot data as needed to provide real-time analytics. 運用ワークロードの大部分が "ホット" データと接触している場合、それらの操作に対して列ストア インデックスの追加メンテナンスは不要です。If a significant part of the operational workload is touching the 'hot' data, those operations will not require additional maintenance of the columnstore index. ベスト プラクティスは、列の行ストア クラスター化インデックスをフィルター処理されたインデックス定義に使用することです。A best practice is to have a rowstore clustered index on the column(s) used in the filtered index definition. SQL ServerSQL Server は、クラスター化インデックスを使用してフィルター処理条件を満たしていない行を迅速にスキャンします。uses the clustered index to quickly scan the rows that did not meet the filtered condition. このクラスター化インデックスがない場合、これらの行を探すには行ストア テーブルのフル テーブル スキャンが必要になり、分析クエリのパフォーマンスに多大な悪影響を及ぼすことがあります。Without this clustered index, a full table scan of the rowstore table will be required to find these rows, which can negatively impact the performance of analytics query significantly. クラスター化インデックスが存在しない場合は、補足的にフィルター処理された非クラスター化 BTree インデックスを作成してそのような行を特定できますが、非クラスター化 BTree インデックスを通じた広範囲の行に対するアクセスは高コストであるため、お勧めしません。In the absence of clustered index, you could create a complementary filtered nonclustered btree index to identify such rows but it is not recommended because accessing large range of rows through nonclustered btree indexes is expensive.

注意

フィルター処理された非クラスター化列ストア インデックスは、ディスク ベースのテーブルに対してのみサポートされます。A filtered nonclustered columnstore index is only supported on disk-based tables. メモリ最適化テーブルではサポートされていません。It is not supported on memory-optimized tables

例 A:BTree インデックスからホット データ、列ストア インデックスからウォーム データにアクセスするExample A: Access hot data from btree index, warm data from columnstore index

この例では、フィルター処理条件 (accountkey > 0) を使用して、列ストア インデックスに含まれる行を確立します。This example uses a filtered condition (accountkey > 0) to establish which rows will be in the columnstore index. 目標は、フィルター処理条件と後続のクエリを設計し、頻繁に変化する BTree インデックスからの "ホット" データにアクセスする、およびより安定した列ストア インデックスからの "ウォーム" データにアクセスすることです。The goal is to design the filtered condition and subsequent queries to access frequently changing "hot" data from the btree index, and to access the more stable "warm" data from the columnstore index.

ウォーム データとホット データの結合インデックスCombined indexes for warm and hot data

注意

クエリ オプティマイザーでは、クエリ プランの列ストア インデックスが考慮されますが、常に選択されるわけではありません。The Query Optimizer will consider, but not always choose, the columnstore index for the query plan. クエリ オプティマイザーがフィルター処理された列ストア インデックスを選択すると、列ストア インデックスからの行とフィルター処理条件を満たしていない行を透過的に結合され、リアルタイム分析を実現します。When the query optimizer chooses the filtered columnstore index, it transparently combines the rows both from columnstore index as well as the rows that do not meet the filtered condition to allow real-time analytics. これは、通常のフィルター処理された非クラスター インデックスとは異なり、インデックスに存在する行に限定されたクエリでのみ使用できます。This is different from a regular nonclustered filtered index which can be used only in queries that restrict themselves to the rows present in the index.

--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 Fullfillment 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  

分析クエリは、次のクエリ プランで実行されます。The analytics query will execute with the following query plan. フィルター処理条件を満たしていない行には、クラスター化 BTree インデックスを通じてアクセスされます。You can see that the rows not meeting the filtered condition are accessed through clustered btree index.

クエリ プランQuery plan

フィルター処理された非クラスター化列ストア インデックスの詳細については、ブログを参照してください。Please refer to the blog for details on filtered nonclustered columnstore index.

パフォーマンス ヒント 2:AlwaysOn 読み取り可能セカンダリに対する分析の負荷を軽減するPerformance tip #2: Offload analytics to Always On readable secondary

フィルター処理された列ストア インデックスを使用して、列ストア インデックスのメンテナンスを最小限に抑えることはできますが、それでも分析クエリには多大なコンピューティング リソース (CPU、I/O、メモリ) が必要であり、運用ワークロードのパフォーマンスに影響します。Even though you can minimize the columnstore index maintenance by using a filtered columnstore index, the analytics queries can still require significant computing resources (CPU, I/O, memory) which impact the operational workload performance. ほとんどのミッション クリティカルなワークロードについては、AlwaysOn 構成を使用することをお勧めします。For most mission critical workloads, our recommendation is to use the Always On configuration. この構成では、負荷を読み取り可能セカンダリにオフロードすることで、実行中の分析の影響を除去できます。In this configuration, you can eliminate the impact of running analytics by offloading it to a readable secondary.

パフォーマンス ヒント 3:ホット データを DELTA 行グループに保持することでインデックスの断片化を削減するPerformance Tip #3: Reducing Index fragmentation by keeping hot data in delta rowgroups

圧縮された行がワークロードによって更新/削除された場合、列ストア インデックスのあるテーブルは (削除された行によって) 大幅に断片化されることがあります。Tables with columnstore index may get significantly fragmented (that is, deleted rows) if the workload updates/deletes rows that have been compressed. 断片化された列ストア インデックスは、メモリと記憶域の非効率的な使用につながります。A fragmented columnstore index leads to inefficient utilization of memory/storage. また、リソースの非効率的な使用に加え、余分な I/O があることと、結果セットから削除された行をフィルター処理する必要があるため、分析クエリのパフォーマンスにも悪影響を及ぼします。Besides inefficient use of resources, it also negatively impacts the analytics query performance because of extra I/O and the need to filter the deleted rows from the result set.

削除された行は、REORGANIZE コマンドを使用してインデックスのデフラグを実行するか、テーブル全体または影響を受けているパーティションの列ストア インデックスを再構築するまで、物理的には削除されません。The deleted rows are not physically removed until you run index defragmentation with REORGANIZE command or rebuild the columnstore index on the entire table or the affected partition(s). REORGANIZEREBUILD の両方のインデックスは、高コストな操作であり、リソースを取り除いたり、ワークロードに使用されたりすることもあります。Both index REORGANIZE and REBUILD are expensive operations taking resources away which otherwise could be used for the workload. さらに、行の圧縮が早すぎる場合は、更新により何度も再圧縮する必要性が出てくるため、無駄な圧縮によるオーバーヘッドにつながる可能性があります。Additionally, if rows compressed too early, it may need to be re-compressed multiple times due to updates leading to wasted compression overhead.
インデックスの断片化は、COMPRESSION_DELAY オプションを使用して最小限に抑えることができます。You can minimize index fragmentation using COMPRESSION_DELAY option.

-- 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);  
  
;  

圧縮遅延の詳細については、ブログを参照してください。Please refer to the blog for details on compression delay.

推奨されているベスト プラクティスを次に示します。Here are the recommended best practices:

  • 挿入/クエリ ワークロード: ワークロードが主にデータの挿入とクエリである場合は、COMPRESSION_DELAY を既定で 0 にするオプションをお勧めします。Insert/Query workload: If your workload is primarily inserting data and querying it, the default COMPRESSION_DELAY of 0 is the recommended option. 単一の DELTA 行グループに 100 万行が挿入されると、新しく挿入された行が圧縮されます。The newly inserted rows will get compressed once 1 million rows have been inserted into a single delta rowgroup.
    このようなワークロードの例としては、(a) 従来の DW ワークロード (b) クリック ストリーム分析 (Web アプリケーションのクリック パターンの分析) が挙げられます。Some examples of such workload are (a) traditional DW workload (b) click-stream analysis when you need to analyze the click pattern in a web application.

  • OLTP ワークロード: ワークロードに大量の DML (更新、削除、挿入が混在) がある場合、DMV の sys.OLTP workload: If the workload is DML heavy (that is, heavy mix of Update, Delete and Insert), you may see columnstore index fragmentation by examining the DMV sys. dm_db_column_store_row_group_physical_stats を分析することで列ストア インデックスの断片化が見られることがあります。dm_db_column_store_row_group_physical_stats. 10% を上回る行が最近圧縮された行グループで削除済みとマークされている場合、行を圧縮できるタイミングで COMPRESSION_DELAY オプションを使用して時間遅延を追加できます。If you see that > 10% rows are marked deleted in recently compressed rowgroups, you can use COMPRESSION_DELAY option to add time delay when rows become eligible for compression. たとえば、ワークロードで新しく挿入された行が 60 分間 "ホット" な状態にある (複数回更新される) 場合は、COMPRESSION_DELAY を 60 にすることをお勧めします。For example, if for your workload, the newly inserted stays 'hot' (that is, gets updated multiple times) for say 60 minutes, you should choose COMPRESSION_DELAY to be 60.

ほとんどのお客様は何もする必要がないことを想定しています。We expect most customers do not need to do anything. COMPRESSION_DELAY オプションの既定値で問題なく動作するはずです。The default value of COMPRESSION_DELAY option should work for them.
上級ユーザーの場合は、以下のクエリを実行して過去 7 日間で削除された行の割合を収集することをお勧めします。For advance users, we recommend running the query below and collect % of deleted rows over the last 7 days.

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 > GETDATE() - 7  
ORDER BY created_time DESC;  

圧縮された行グループで削除された行の数が 20% を超える場合、それより前の行グループを 5% 以下のバリエーション (コールド グループと呼ばれます) で平坦化することで、COMPRESSION_DELAY = (youngest_rowgroup_created_time - current_time) に設定します。If the number of deleted rows in compressed rowgroups > 20%, plateauing in older rowgroups with < 5% variation (referred to as cold rowgroups) set COMPRESSION_DELAY = (youngest_rowgroup_created_time - current_time). このアプローチは、安定性が高く比較的同種のワークロードに最適です。Note that this approach works best with a stable and relatively homogeneous workload.

参照See Also

列ストア インデックス ガイド Columnstore Indexes Guide
列ストア インデックス データの読み込み Columnstore Indexes Data Loading
列ストア インデックスのクエリ パフォーマンス Columnstore Indexes Query Performance
データ ウェアハウスの列ストア インデックス Columnstore Indexes for Data Warehousing
インデックスの再編成と再構築Reorganize and Rebuild Indexes