실시간 운영 분석을 위한 Columnstore 시작Get started with Columnstore for real time operational analytics

이 항목은 다음에 적용됩니다. 예SQL Server(2016부터)예Azure SQL Database아니요Azure SQL Data Warehouse아니요병렬 데이터 웨어하우스 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server 2016에는 분석 워크로드와 OLTP 워크로드를 같은 데이터베이스 테이블에서 동시에 실행하는 기능인 실시간 운영 분석이 도입되었습니다.SQL Server 2016 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 (i.e. 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. 이 솔루션은 표준이었지만 다음 세 가지 주요 문제가 있었습니다.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 작업이 각 업무일 종료 시간에 실행되는 경우 분석 쿼리는 최소한 하루 이전의 데이터에서 실행됩니다.For example, if the ETL job runs at the at 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.

실시간 분석에서는 rowstore 테이블에서 업데이트 가능한 columnstore 인덱스를 사용합니다.Real-time analytics uses an updateable columnstore index on a rowstore table. Columnstore 인덱스는 데이터의 복사본을 유지하므로 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 Server는 인덱스 변경 내용을 자동으로 유지 관리하므로 OLTP 변경 내용이 분석을 위해 항상 최신 상태로 유지됩니다.SQL Server 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. 이를 단일 columnstore 인덱스로 바꿉니다.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  
    ON t_account (accountkey, accountdescription, unitsold)   

    메모리 내 테이블의 columnstore 인덱스는 메모리 내 OLTP 및 메모리 내 columnstore 기술을 통합하여 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. 메모리 내 테이블의 columnstore 인덱스는 모든 열을 포함해야 합니다.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  
  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. 분석 쿼리는 columnstore 인덱스에 대해 실행되고 OLTP 작업은 OLTP btree 인덱스에 대해 계속 실행됩니다.Analytics queries will run against the columnstore index and OLTP operations will keep running against your OLTP btree indexes. OLTP 워크로드는 계속 수행되지만 columnstore 인덱스를 유지하기 위해 약간의 추가 오버헤드가 발생합니다.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

실시간 운영 분석에 대해 자세히 알아보려면 Sunil Agarwal의 블로그 게시물을 읽어 보세요.Read Sunil Agarwal's 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. 아래 예제에서는 실시간 분석을 제공하면서 필터링된 인덱스를 사용하여 비클러스터형 columnstore 인덱스가 트랜잭션 워크로드에 미치는 영향을 최소화하는 방법을 보여 줍니다.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.

운영 워크로드에서 비클러스터형 columnstore 인덱스 유지 관리 오버헤드를 최소화하려면 필터링된 조건을 사용하여 또는 느린 변경 데이터에만 비클러스터형 columnstore 인덱스를 만들면 됩니다.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. 예를 들어 주문 관리 응용 프로그램에서 이미 배송된 주문에 대한 비클러스터형 columnstore 인덱스를 만들 수 있습니다.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. 필터링된 인덱스를 사용하는 경우 비클러스터형 columnstore 인덱스의 데이터에 필요한 업데이트가 적으므로 트랜잭션 워크로드에 대한 영향을 감소합니다.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. 운영 워크로드의 중요한 부분이 '핫' 데이터에 연결되어 있는 경우 이러한 작업에는 columnstore 인덱스의 추가 유지 관리가 필요하지 않습니다.If a significant part of the operational workload is touching the 'hot' data, those operations will not require additional maintenance of the columnstore index. 필터링된 인덱스 정의에 사용된 열에서 rowstore 클러스터형 인덱스를 유지하는 것이 가장 좋습니다.A best practice is to have a rowstore clustered index on the column(s) used in the filtered index definition. SQL Server는 클러스터형 인덱스를 사용하여 필터링된 조건을 충족하지 않는 행을 신속하게 검색합니다.SQL Server uses the clustered index to quickly scan the rows that did not meet the filtered condition. 이 클러스터형 인덱스가 없으면 분석 쿼리의 성능을 크게 저하시킬 수 있는 이러한 행을 찾기 위해 rowstore 테이블의 전체 검색을 수행해야 합니다.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.


필터링된 비클러스터형 columnstore 인덱스는 디스크 기반 테이블에서만 지원됩니다.A filtered nonclustered columnstore index is only supported on disk-based tables. 메모리 액세스에 최적화된 테이블에서는 지원되지 않습니다.It is not supported on memory-optimized tables

예제 A: btree 인덱스에서 핫 데이터에 액세스하고, columnstore 인덱스에서 웜 데이터에 액세스Example A: Access hot data from btree index, warm data from columnstore index

이 예제에서는 필터링된 조건(accountkey > 0)을 사용하여 columnstore 인덱스에 포함할 행을 설정합니다.This example uses a filtered condition (accountkey > 0) to establish which rows will be in the columnstore index. 이는 자주 변경되는 “핫” 데이터는 btree 인덱스에서 액세스하고 보다 안정적인 “웜” 데이터는 columnstore 인덱스에서 액세스하도록 필터링된 조건 및 후속 쿼리를 디자인하기 위한 것입니다.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


쿼리 최적화 프로그램에서는 경우에 따라 쿼리 계획에 대해 columnstore 인덱스를 선택할 수 있습니다.The query optimizer will consider, but not always choose, the columnstore index for the query plan. 필터링된 columnstore 인덱스를 선택한 경우 쿼리 최적화 프로그램은 실시간 분석을 허용하기 위해 columnstore 인덱스의 행과 필터링된 조건을 충족하지 않는 행을 모두 투명하게 통합합니다.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

필터링된 비클러스터형 columnstore 인덱스에 대한 자세한 내용은 블로그를 참조하세요.Please refer to the blog for details on filtered nonclustered columnstore index.

성능 팁 2: 분석을 Always On 읽기 가능한 보조로 오프로드Performance tip #2: Offload analytics to Always On readable secondary

필터링된 columnstore 인덱스를 사용하여 columnstore 인덱스 유지 관리를 최소화할 수 있지만 분석 쿼리에는 여전히 운영 워크로드 성능에 영향을 주는 많은 컴퓨팅 리소스(CPU, IO, 메모리)가 필요할 수 있습니다.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, IO, memory) which impact the operational workload performance. 업무에 중요한 워크로드에는 대부분 Always On 구성을 사용하는 것이 좋습니다.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: 델타 행 그룹에서 핫 데이터를 유지하여 인덱스 조각화 줄이기Performance Tip #3: Reducing Index fragmentation by keeping hot data in delta rowgroups

워크로드에서 압축된 행을 업데이트/삭제하는 경우 columnstore 인덱스가 있는 테이블이 크게 조각화(즉, 삭제된 행)될 수 있습니다.Tables with columnstore index may get significantly fragmented (i.e. deleted rows) if the workload updates/deletes rows that have been compressed. 조각화된 columnstore 인덱스는 메모리/저장소의 비효율적인 사용률을 초래합니다.A fragmented columnstore index leads to inefficient utilization of memory/storage. 리소스의 비효율적인 사용 외에 분석 쿼리 성능도 저하됩니다. 이는 결과 집합에서 삭제된 행을 필터링해야 하고 추가 IO가 필요하기 때문입니다.Besides inefficient use of resources, it also negatively impacts the analytics query performance because of extra IO and the need to filter the deleted rows from the result set.

삭제된 행은 REORGANIZE 명령을 사용하여 인덱스 조각 모음을 실행하거나 전체 테이블 또는 영향을 받는 패턴에서 columnstore 인덱스를 다시 작성할 때까지 물리적으로 제거되지 않습니다.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). REORGANIZE와 인덱스 REBUILD 둘 다 워크로드에 사용될 수 있는 리소스를 차지하는 부담이 큰 작업입니다.Both REORGANIZE and Index 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)   


압축 지연에 대한 자세한 내용은 블로그를 참조하세요.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. 100만 개의 행이 단일 델타 행 그룹에 삽입되면 새로 삽입된 행이 압축됩니다.The newly inserted rows will get compressed once 1 million rows have been inserted into a single delta rowgroup.
    이러한 워크로드의 예로는 (a) 기존 DW 워크로드 (b) 웹 응용 프로그램에서 클릭 패턴을 분석해야 하는 경우의 클릭 스트림 분석 등이 있습니다.Some example 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 워크로드: 워크로드가 DMV에 집중된 경우(즉, 주로 업데이트, 삭제 및 삽입 수행) DMV sys를 검사하면 columnstore 인덱스 조각화를 볼 수 있습니다.OLTP workload: If the workload is DML heavy (i.e. 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’ (i.e. gets updated multiple times) for say 60 minutes, you should choose COMPRESSION_DELAY to be 60.

    대부분의 고객은 아무 작업도 수행하지 않아도 됩니다.We expect most customers do not need to 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 인덱스 가이드 Columnstore Indexes Guide
Columnstore 인덱스 데이터 로드 Columnstore Indexes Data Loading
Columnstore 인덱스 쿼리 성능 Columnstore Indexes Query Performance
데이터 웨어하우스용 Columnstore 인덱스 Columnstore Indexes for Data Warehousing
Columnstore 인덱스 조각 모음 Columnstore Indexes Defragmentation