Columnstore 인덱스 - 쿼리 성능Columnstore indexes - query performance

이 항목 적용 대상: 예SQL Server예Azure SQL 데이터베이스예Azure SQL 데이터 웨어하우스 예 병렬 데이터 웨어하우스THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

디자인된 columnstore 인덱스에서 매우 빠른 쿼리 성능을 제공하기 위한 권장 사항에 대해 설명합니다.Recommendations for achieving the very fast query performance that columnstore indexes are designed to provide.

columnstore 인덱스는 일반적인 행 저장소 인덱스의 경우보다 분석 및 데이터 웨어하우징 작업에서는 최대 100배, 데이터 압축 작업에서는 최대 10배까지 성능을 향상시킬 수 있습니다.Columnstore indexes can achieve up to 100x better performance on analytics and data warehousing workloads and up to 10x better data compression than traditional rowstore indexes. 이 권장 사항은 쿼리에서 columnstore 인덱스가 제공하는 매우 빠른 성능을 경험하는 데 도움이 됩니다.These recommendations will help your queries achieve the very fast query performance that columnstore indexes are designed to provide. 끝에 있는 columnstore 성능에 대한 자세한 설명입니다.Further explanations about columnstore performance are at the end.

쿼리 성능 개선을 위한 권장 사항Recommendations for improving query performance

다음은 columnstore 인덱스에서 제공하는 고성능 달성을 위한 몇 가지 권장 사항입니다.Here are some recommendations for achieving the high performance columnstore indexes are designed to provide.

1. 전체 테이블 검색에서 더 많은 행 그룹을 제거할 수 있도록 데이터 구성1. Organize data to eliminate more rowgroups from a full table scan

  • 삽입 순서 활용Leverage insert order. 일반적인 데이터 웨어하우스에서 주로 데이터는 시간 순서에, 분석은 시간 차원에 삽입됩니다.In common case in traditional data warehouse, the data is indeed inserted in time order and analytics is done in time dimension. 예를 들어 분기별로 판매를 분석하는 경우For example, analyzing sales by quarter. 이런 종류의 작업에서는 행 그룹이 자동으로 제거됩니다.For this kind of workload, the rowgroup elimination happens automatically. SQL Server 2016에서 쿼리 처리의 일부로 건너뛴 숫자 행 그룹을 찾을 수 있습니다.In SQL Server 2016, you can find out number rowgroups skipped as part of query processing.

  • 클러스터형 rowstore 인덱스 활용Leverage the rowstore clustered index. 일반 쿼리 조건자가 행의 삽입 순서와 관련되지 않은 열(예: C1)에 있는 경우 열 C1에서 클러스터형 rowstore 인덱스를 만든 다음 클러스터형 rowstore 인덱스를 삭제하여 클러스터형 columnstore 인덱스를 만듭니다.If the common query predicate is on a column (e.g. C1) that is unrelated to insert order of the row, you can create a rowstore clustered index on columns C1 and then create clustered columstore index by dropping the rowstore clustered index. DOP(병렬 처리 수준)를 1로 사용하여 명시적으로 클러스터형 columnstore 인덱스를 만드는 경우에는 결과 클러스터형 columnstore 인덱스가 열 C1에서 완벽하게 정렬됩니다.if you create the clustered columnstore index explicitly using DOP (degree of parallelism) = 1, the resultant clustered columnstore index will be perfectly ordered on column C1. DOP를 8로 지정하면 8개의 행 그룹 전체에서 값 중복이 표시됩니다.If you specify DOP=8, then you will see overlap of values across 8 rowgroups. 큰 데이터 집합으로 columnstore 인덱스를 처음 만들 때 이 전략을 주로 사용합니다.A common case of this strategy when you initially create columnstore index with large set of data. NCCI(비클러스터형 columnstore 인덱스)의 경우 기본 rowstore 테이블에 클러스터형 인덱스가 있으면 행은 이미 정렬되어 있습니다.Note, for nonclustered columnstore index (NCCI), if the base rowstore table has a clustered index, the rows are already ordered. 이 경우 결과 비클러스터형 columnstore 인덱스는 자동으로 정렬됩니다.In this case, the resultant nonclustered columnstore index will automatically be ordered. 한 가지 중요한 점으로 columnstore 인덱스는 기본적으로 행 순서를 유지 관리하지 않음을 참고하세요.One important point to note is that columnstore index does not inherently maintain the order of rows. 새 행이 삽입되거나 더 오래된 행이 업데이트되면 분석 쿼리 성능이 악화될 수 있으므로 프로세스를 반복해야 할 수 있습니다.As new rows are inserted or older rows are updated, you may need to repeat the process as the analytics query performance may deteriorate

  • 테이블 분할 활용Leverage table partitioning. columnstore 인덱스를 분할한 다음 파티션 제거를 사용하여 검색할 행 그룹의 수를 줄일 수 있습니다.You can partition the columnstore index and then use partition elimination to reduce number of rowgroups to scan. 예를 들어 팩트 테이블에 고객 구매 정보가 저장되어 있고, 일반 쿼리 패턴이 특정 고객의 분기별 구매 정보 찾기일 경우 삽입 순서와 고객 열의 분할을 결합할 수 있습니다.For example, a fact table stores purchases made by customers and a common query pattern is to find quarterly purchases done by a specific customer, you can combine the insert order with partitioning on customer column. 각 파티션에는 특정 고객에 대한 행이 시간 순서로 포함됩니다.Each partition will contain rows in time order for specific customer.

2. 병렬로 columnstore 인덱스를 만들기에 충분한 메모리 계획2. Plan for enough memory to create columnstore indexes in parallel

인덱스 만들기는 메모리가 제한되지 않는 한 기본적으로 병렬 작업입니다.Creating a columnstore index is by default a parallel operation unless memory is constrained. 병렬로 인덱스를 만들려면 직렬로 인덱스를 만들 때보다 많은 메모리가 필요합니다.Creating the index in parallel requires more memory than creating the index serially. 메모리가 충분하면 동일한 열에 B-트리를 작성할 때보다 1.5배 많은 메모리가 columnstore 인덱스를 만드는 데 사용됩니다.When there is ample memory, creating a columnstore index takes on the order of 1.5 times as long as building a B-tree on the same columns.

columnstore 인덱스를 만드는 데 필요한 메모리는 열 수, 문자열 열 수, DOP(병렬 처리 수준) 및 데이터의 특징에 따라 다릅니다.The memory required for creating a columnstore index depends on the number of columns, the number of string columns, the degree of parallelism (DOP), and the characteristics of the data. 예를 들어, 테이블의 행 수가 100만 개 미만일 경우 SQL Server는 한 스레드만 사용하여 columnstore 인덱스를 만듭니다.For example, if your table has fewer than one million rows, SQL Server will use only one thread to create the columnstore index.

테이블 행 수가 100만 개 이상이지만 SQL Server에서 MAXDOP를 사용하여 인덱스를 만들기에 충분한 메모리 부여를 얻을 수 없는 경우 SQL Server에서 사용 가능한 메모리 부여에 맞게 필요한 만큼 자동으로 MAXDOP를 줄입니다.If your table has more than one million rows, but SQL Server cannot get a large enough memory grant to create the index using MAXDOP, SQL Server will automatically decrease MAXDOP as needed to fit into the available memory grant. 일부 경우 제한된 메모리로 인덱스를 작성하도록 DOP를 줄여야 합니다.In some cases, DOP must be decreased to one in order to build the index under constrained memory.

SQL Server 2016 부터 쿼리는 항상 일괄 처리 모드로 작동합니다.Beginning with SQL Server 2016, the query will always operate in batch mode. 이전 릴리스에서 일괄 처리는 DOP가 1보다 클 경우에만 실행됩니다.In previous releases, batch execution is only used when DOP is greater than one.

Columnstore 성능 설명Columnstore Performance Explained

sColumnstore 인덱스에서는 고속 메모리 내 일괄 처리 모드를 IO 요구 사항을 크게 줄이는 기술과 결합하여 쿼리 성능이 크게 향상되었습니다.Columnstore indexes achieve high query performance by combining high-speed in-memory batch mode processing with techniques that greatly reduce IO requirements. 분석 쿼리에서는 많은 행이 검색되므로 일반적으로 IO 바인딩되며, 이로 인해 쿼리 실행 중 IO를 줄이는 작업은 columnstore 인덱스 디자인에 매우 중요합니다.Since analytics queries scan large numbers of rows, they are typically IO-bound, and therefore reducing IO during query execution is critical to the design of columnstore indexes. 데이터를 메모리로 읽어온 후에는 메모리 내 작업 수를 줄이는 것이 중요합니다.Once data has been read into memory, it is critical to reduce the number of in-memory operations.

Columnstore 인덱스는 IO는 줄이고, 높은 데이터 압축, columnstore 제거, 행 그룹 제거 및 일괄 처리를 통해 메모리 내 작업을 최적화합니다.Columnstore indexes reduce IO and optimize in-memory operations through high data compression, columnstore elimination, rowgroup elimination, and batch processing.

데이터 압축Data compression

Columnstore 인덱스는 rowstore 인덱스보다 최고 10배 높은 데이터 압축률을 보여 줍니다.Columnstore indexes achieve up to 10x greater data compression than rowstore indexes. 이를 통해 분석 쿼리를 실행하는 데 필요한 IO는 크게 줄이고 그에 따라 쿼리 성능이 향상됩니다.This greatly reduces the IO required to execute analytics queries and therefore improves query performance.

  • Columnstore 인덱스는 디스크에서 압축된 데이터를 읽습니다. 즉, 메모리로 읽어야 하는 데이터 바이트가 적어집니다.Columnstore indexes read compressed data from disk, which means fewer bytes of data need to be read into memory.

  • Columnstore 인덱스는 데이터를 압축된 형태로 메모리에 저장합니다. 이렇게 하면 동일한 데이터를 메모리로 읽는 횟수가 줄어 IO도 줄어듭니다.Columnstore indexes store data in compressed form in memory which reduces IO by reducing the number of times the same data is read into memory. 예를 들어 압축률이 10배인 columnstore 인덱스에서는 데이터를 압축되지 않은 형태로 저장하는 작업과 비교해 볼 때 메모리에 10배 이상의 데이터를 보관할 수 있습니다.For example, with 10x compression, columnstore indexes can keep 10x more data in memory compared to storing the data in uncompressed form. 메모리에서 더 많은 데이터를 사용하면 columnstore 인덱스에서는 디스크에서 추가 읽기를 유도하여 메모리에 필요한 데이터를 찾을 가능성이 높아집니다.With more data in memory, it is more likely that the columnstore index will find the data it needs in memory with incurring additional reads from disk.

  • Columnstore 인덱스 높은 압축률을 보여 주는 데이터를 행 대신 열을 기준으로 압축하여 디스크에 저장되는 데이터 크기를 줄입니다.Columnstore indexes compress data by columns instead of by rows which achieves high compression rates and reduces the size of the data stored on disk. 각 열은 개별적으로 압축되어 저장됩니다.Each column is compressed and stored independently. 한 열에 있는 데이터는 항상 형식이 동일하며, 유사한 값을 포함하려는 경향이 있습니다.Data within a column always has the same data type and tends to have similar values. 데이터 압축 기술은 값이 비슷할 경우 더 높은 압축률을 달성할 수 있습니다.Data compression techniques are very good at achieving higher compression rates when values are similar.

  • 예를 들어 팩트 테이블에 고객 주소가 저장되어 있고 국가에 대한 열이 하나 있는 경우 가능한 총 값 수는 200개 미만입니다.For example, if a fact table stores customer addresses and has a column for country, the total number of possible values is fewer than 200. 이러한 값 중 일부는 여러 번 반복됩니다.Some of those values will be repeated many times. 팩트 테이블에 1억 개의 행이 있는 경우에는 국가 열이 쉽게 압축되므로 요구되는 저장소 크기는 매우 작습니다.If the fact table has 100 million rows, the country column will compress easily and require very little storage. 행 단위 압축은 이 방식으로 열 값의 유사성을 활용할 수 없어 국가 열의 값을 압축하는 데 더 많은 바이트를 사용합니다.Row-by-row compression is not able to capitalize on the similarity of column values in this way and will use more bytes to compress the values in the country column.

열 제거Column elimination

Columnstore 인덱스는 쿼리 결과에 필요 없는 열은 읽기를 건너뜁니다.Columnstore indexes skip reading in columns that are not required for the query result. 열 제거라고 하는 이 기능은 또한 쿼리 실행에 필요한 IO를 줄이므로 쿼리 성능이 향상됩니다.This ability, called column elimination, further reduces IO for query execution and therefore improves query performance.

  • 열 제거는 데이터가 열 단위로 구성되고 압축되기 때문에 가능합니다.Column elimination is possible because the data is organized and compressed column by column. 반면, 데이터가 행별로 저장된 경우 각 행의 열 값은 물리적으로 함께 저장되어 쉽게 구분할 수 없습니다.In contrast, when data is stored row-by-row, the column values in each row are physically stored together and cannot be easily separated. 쿼리 프로세서에서 특정 열 값을 검색하려면 전체 행에서 읽어야 합니다. 이럴 경우 추가 데이터를 불필요하게 메모리로 읽어오게 되어 IO가 늘어납니다.The query processor needs to read in an entire row to retrieve specific column values, which increases IO because extra data is unnecessarily read into memory.

  • 예를 들어 테이블에 열이 50개 있고, 쿼리에서 그중 5개만 사용할 경우 columnstore 인덱스는 디스크에서 해당 5개 열만 가져옵니다.For example, if a table has 50 columns and the query only uses 5 of those columns, the columnstore index only fetches the 5 columns from disk. 나머지 45개 열은 읽기를 건너뜁니다.It skips reading in the other 45 columns. 이렇게 하면 열 크기가 모두 비슷하다고 가정할 때 90%까지 IO가 줄어듭니다.This reduces IO by another 90% assuming all columns are of similar size. 동일한 데이터가 rowstore에 저장되면 쿼리 프로세서는 추가로 45개 열을 모두 읽어야 합니다.If the same data are stored in a rowstore, the query processor needs to read the additional 45 columns.

행 그룹 제거Rowgroup elimination

전체 테이블 검색의 경우 대부분의 데이터가 쿼리 조건자의 조건에 맞지 않습니다.For full table scans, a large percentage of the data usually does not match the query predicate criteria. 메타데이터를 사용하면 columnstore 인덱스는 쿼리 결과에 필요한 데이터가 없는 rowgroup의 읽기를 실제 IO없이 모두 건너뛸 수 있습니다.By using metadata, the the columnstore index is able to skip reading in the rowgroups that do not contain data required for the query result, all without actual IO. 행 그룹 제거라고 하는 이 기능은 전체 테이블 검색에 필요한 IO를 줄이므로 쿼리 성능이 향상됩니다.This ability, called rowgroup elimination, reduces IO for full table scans and therefore improves query performance.

columnstore 인덱스는 전체 테이블 검색을 언제 수행해야 하나요?When does a columnstore index need to perform a full table scan?

SQL Server 2016부터는 rowstore 힙에서와 같이 클러스터형 columnstore 인덱스에서 하나 이상의 일반 비클러스터형 btree 인덱스를 만들 수 있습니다.Starting with SQL Server 2016, you can create one or more regular nonclustered btree indexes on a clustered columnstore index just like you can on a rowstore heap. 비클러스터형 btree 인덱스는 같음 조건자 또는 작은 값 범위를 가진 조건자가 있는 쿼리의 속도를 높일 수 있습니다.The nonclustered btree indexes can speed up a query that has an equality predicate or a predicate with a small range of values. 더 복잡한 조건자의 경우 쿼리 최적화 프로그램에서 전체 테이블 검색을 선택할 수도 있습니다.For more complicated predicates, the query optimizer might choose a full table scan. 행 그룹을 건너뛸 수 없다면 전체 테이블 검색은 특히 큰 테이블의 경우 시간이 많이 소요됩니다.Without the ability to skip rowgroups, a full table scan would be very time-consuming, especially for large tables.

분석 쿼리에서 전체 테이블 검색 시 행 그룹 제거를 언제 활용하나요?When does an analytics query benefit from rowgroup elimination for a full-table scan?

예를 들어 소매점에서 클러스터형 columnstore 인덱스가 포함된 팩트 테이블을 사용하여 판매 데이터를 모델링했을 경우For example, a retail business has modelled their sales data using a fact table with clustered columnstore index. 새로운 상점은 각각 판매 날짜를 비롯하여 거래에 대한 다양한 특성을 저장합니다.Each new sale stores various attributes of the transaction including the date is was sold. 흥미롭게도 columnstore 인덱스는 정렬된 순서를 보장하지 않지만 이 테이블의 행은 날짜로 정렬된 순서대로 로드됩니다.Interestingly, even though columnstore indexes do not guarantee a sorted order, the rows in this table will loaded in a date-sorted order. 시간이 지남에 따라 이 테이블은 증가합니다.Over time this table will grow. 소매업은 지난 10년에 대한 판매 데이터를 보관할 수는 있지만 분석 쿼리에서는 마지막 분기에 대한 집계를 계산하기만 하면 됩니다.Although the retail business might keep sales data for the last 10 years, an analytics query might only need to compute an aggregate for last quarter. Columnstore 인덱스는 날짜 열에 대한 메타데이터만 살펴보고 이전 39개 분기에 대한 데이터에 대한 액세스를 제거할 수 있습니다.Columnstore indexes can eliminate accessing the data for the previous 39 quarters by just looking at the metadata for the date column. 이 작업은 메모리로 읽어와 처리되는 데이터의 양을 추가로 97% 절감하였습니다.This is an additional 97% reduction in the amount of data that is read into memory and processed.

전체 테이블 검색에서는 어떤 행 그룹을 건너뛰나요?Which rowgroups are skipped in a full table scan?

제거할 행 그룹을 결정하려면 columnstore 인덱스는 메타데이터를 사용하여 각 행 그룹에 대한 각 열 세그먼트의 최소값 및 최대값을 저장합니다.To determine which rows groups to eliminate, the columnstore index uses metadata to store the minimum and maximum values of each column segment for each rowgroup. 열 세그먼트 범위 중 어떤 것도 쿼리 조건자의 조건에 맞지 않을 경우 실제 IO를 수행하지 않고 행 그룹 전체를 건너뜁니다.When none of the column segment ranges meet the query predicate criteria, the entire rowgroup is skipped without doing any actual IO. 데이터가 주로 정렬된 순서대로 로드되고, 행 정렬이 보장되지 않더라도 유사한 데이터 값이 보통 동일한 행 그룹 또는 인접한 행 그룹에 위치하므로 이 작업이 가능합니다.This works because the data is usually loaded in a sorted order and although rows are not guaranteed to be sorted, similar data values are often located within the same rowgroup or a neighboring rowgroup.

행 그룹에 대한 자세한 내용은 Columnstore 인덱스 가이드를 참조하세요.For more details about rowgroups, see Columnstore Indexes Guide

일괄 처리 모드 실행Batch Mode Execution

일괄 처리 모드 실행이란 실행 효율성을 위해 일반적으로 최대 900개의 행을 함께 행 집합으로 처리하는 것을 말합니다.Batch mode execution refers to processing a set of rows, typically up to 900 rows, together for execution efficiency. 예를 들어 쿼리 Select SUM (Sales)from SalesData 가 SalesData 테이블에서의 총 판매액을 집계합니다.For example, the query Select SUM (Sales)from SalesData aggregates the total sales from the table SalesData. 일괄 처리 모드 실행에서 쿼리 실행 엔진은 집계를 900개 값으로 이루어진 그룹으로 계산합니다.In batch mode execution, the query execution engine computes the aggregate in group of 900 values. 이렇게 하면 각 행에 대한 비용을 부담하지 않고 모든 행에서 메타데이터 액세스 비용과 기타 오버헤드 유형을 일괄 처리로 분산하여 코드 경로가 상당히 줄어듭니다.This spreads metadata the access costs and other types of overhead over all the rows in a batch, rather than paying the cost for each row thereby significantly reducing the code path. 일괄 처리 모드는 가능한 경우 압축된 데이터에서 작동하고 행 모드 처리에서 사용하는 교환 연산자 중 일부를 제거합니다.Batch mode processing operates on compressed data when possible and eliminates some of the exchange operators used by row mode processing. 이렇게 하면 크기 순서대로 정렬되어 분석 쿼리 실행 속도가 향상됩니다.This speeds up execution of analytics queries by orders of magnitude.

일부 쿼리 실행 연산자는 일괄 처리 모드에서 실행할 수 없습니다.Not all query execution operators can be executed in batch mode. 예를 들어 Insert, Delete 또는 Update와 같은 DML 작업은 한 번에 행에서 실행됩니다.For example, DML operations such as Insert, Delete or Update are executed row at a time. 일괄 처리 모드 연산자는 쿼리 성능 속도 향상을 위해 Scan, Join, Aggregate, Sort 등과 같은 연산자를 대상으로 합니다.Batch mode operators target operators for speeding up query performance such as Scan, Join, Aggregate, sort and so on. Columnstore 인덱스는 SQL Server 2012에서 처음으로 사용되었으므로 일괄 처리 모드로 실행할 수 있는 연산자를 지속적으로 확장하려고 노력합니다.Since the columnstore index was introduced in SQL Server 2012, there is a sustained effort to expand the operators that can be executed int the batch mode. 다음 표에서 제품 버전에 따라 일괄 처리 모드로 실행되는 연산자를 보여 줍니다.The table below shows the operators that run in batch mode according to the product version.

일괄 처리 모드 연산자Batch Mode Operators 언제 사용하나요?When is this used? SQL Server 2012SQL Server 2012 SQL Server 2014SQL Server 2014 SQL Server 2016 및 SQL 데이터베이스¹SQL Server 2016 and SQL Database¹ 설명Comments
DML 작업(insert, delete, update, merge)DML operations (insert, delete, update, merge) 아니요no 아니요no 아니요no DML 병렬이 아니므로 일괄 처리 모드 작업이 아닙니다.DML is not a batch mode operation because it is not parallel. 직렬 모드 일괄 처리를 사용하도록 설정하더라도 DML을 일괄 처리 모드로 처리함으로써 크게 향상되는 것은 없습니다.Even when we enable serial mode batch processing, we don't see significant gains by allowing DML to be processed in batch mode.
Columnstore 인덱스 검색columnstore index scan SCANSCAN NANA yes yes Columnstore 인덱스에서 조건자를 SCAN 노드로 푸시할 수 있습니다.For columnstore indexes, we can push the predicate to the SCAN node.
columnstore 인덱스 Scan(비클러스터형)columnstore Index Scan (nonclustered) SCANSCAN yes yes yes yes
index seekindex seek NANA NANA 아니요no rowmode에서 비클러스터형 btree 인덱스를 통해 seek 작업을 수행합니다.We perform a seek operation through a nonclustered btree index in rowmode.
compute scalarcompute scalar 스칼라 값으로 평가되는 식입니다.Expression that evaluates to a scalar value. yes yes yes 데이터 형식에 몇 가지 제한 사항이 있습니다.There are some restrictions on data type. 모든 일괄 처리 모드 연산자에 적용됩니다.This is true for all batch mode operators.
연결(concatenation)concatenation UNION 및 UNION ALLUNION and UNION ALL 아니요no yes yes
filterfilter 조건자 적용Applying predicates yes yes yes
hash matchhash match 해시 기반 집계 함수, 외부 해시 조인, 오른쪽 해시 조인, 왼쪽 해시 조인, 오른쪽 내부 조인, 왼쪽 내부 조인Hash-based aggregate functions, outer hash join, right hash join, left hash join, right inner join, left inner join yes yes yes 집계에 대한 제한 사항: 문자열에 min/max가 없습니다.Restrictions for aggregation: no min/max for strings. 사용할 수 있는 집계 함수는 sum/count/avg/min/max입니다.Aggregation functions available are sum/count/avg/min/max.
조인에 대한 제한 사항: 일치하지 않는 형식은 정수가 아닌 형식에 조인되지 않습니다.Restrictions for join: no mismatched type joins on non-integer types.
merge joinmerge join 아니요no 아니요no 아니요no
다중 스레드 쿼리multi-threaded queries yes yes yes
중첩 루프nested loops 아니요no 아니요no 아니요no
MAXDOP 1에서 실행되는 단일 스레드 쿼리single-threaded queries running under MAXDOP 1 아니요no 아니요no yes
직렬 쿼리 계획을 사용하는 단일 스레드 쿼리single-threaded queries with a serial query plan 아니요no 아니요no yes
sortsort columnstore 인덱스를 사용하여 SCAN 시 절을 기준으로 정렬합니다.Order by clause on SCAN with columnstore index. 아니요no 아니요no yes
위쪽 정렬top sort 아니요no 아니요no yes
창 집계window aggregates NANA NANA yes SQL Server 2016의 새로운 연산자입니다.New operator in SQL Server 2016.

¹SQL Server 2016, SQL 데이터베이스 V12 Premium Edition 및 SQL 데이터 웨어하우스에 적용¹Applies to SQL Server 2016, SQL Database V12 Premium Edition, and SQL Data Warehouse

집계 푸시 다운Aggregate Pushdown

SCAN 노드에서 조건에 맞는 행을 가져와 일괄 처리 모드에서 값을 집계하는 집계 계산을 위한 일반 실행 경로입니다.A normal execution path for aggregate computation to fetch the qualifying rows from the SCAN node and aggregate the values in Batch Mode. 이러한 실행으로 좋은 성능이 제공되긴 하지만 SQL Server 2016에서 집계 작업은 SCAN 노드로 푸시되어 다음 조건이 충족되면 일괄 처리 모드 실행 시 크기 순서대로 정렬되므로 집계 계산 성능을 향상시킬 수 있습니다.While this delivers good performance, but with SQL Server 2016, the aggregate operation can be pushed to the SCAN node to improve the performance of aggregate computation by orders of magnitude on top of Batch Mode execution provided the following conditions are met

  • 집계는 MIN, MAX, SUM, COUNT 및 COUNT()입니다.The aggregates are MIN, MAX, SUM, COUNT and COUNT().
  • 집계 연산자는 SCAN 노드 또는 그룹화된 SCAN 노드 맨 위에 있어야 합니다.Aggregate operator must be on top of SCAN node or SCAN node with group by.
  • 이 집계는 고유한 집계가 아닙니다.This aggregate is not a distinct aggregate.
  • 집계 열은 문자열 열이 아닙니다.The aggregate column is not a string column.
  • 집계 열은 가상의 열이 아닙니다.The aggregate column is not a virtual column.
  • 입력 및 출력 데이터 형식은 다음 중 하나여야 하며 64비트 이내여야 합니다.The input and output datatype must be one of the following and must fit within 64 bits.

    • tiny int, int, big int, small int, bitTiny int, int, big int, small int, bit
    • 정밀도가 18 이하인 small money, money, decimal 및 numericSmall money, money, decimal and numeric which has precision <= 18
    • small date, date, datetime, datetime2, timeSmall date, date, datetime, datetime2, time

    집계 푸시다운은 캐시에서 사용하는 실행에서 압축되거나 인코딩된 데이터를 효율적으로 집계하고 SIMD를 활용하여 좀 더 가속화됩니다.Aggregate push down is further accelerated by efficient Aggregation on compressed/encoded data in cache-friendly execution and by leveraging SIMD

    aggregate pushdownaggregate pushdown

    예를 들어 집계 푸시다운은 아래 쿼리 모두에서 수행됩니다.For example, aggregate pushdown is done in both of the queries below


SELECT  productkey, SUM(TotalProductCost)    
FROM FactResellerSalesXL_CCI    
GROUP BY productkey    

SELECT  SUM(TotalProductCost)    
FROM FactResellerSalesXL_CCI    

문자열 조건자 푸시다운String predicate pushdown

동기: 데이터 웨어하우스 스키마를 디자인할 때 하나 이상의 팩트 테이블과 많은 차원 테이블로 구성된 별모양 스키마 또는 눈송이 스키마를 스키마 모델링으로 사용하는 것이 권장됩니다.Motivation: When designing a data warehouse schema, the recommended schema modeling is to use star-schema or snowflake schema consisting of one or more fact tables and many dimension tables. 팩트 테이블 에는 비즈니스 측정값 또는 트랜잭션을 저장하고, 차원 테이블 에는 분석해야 하는 팩트 전체에 대한 차원을 저장합니다.The fact table stores the business measurements or transactions and dimension table store the dimensions across which facts need to be analyzed.

예를 들어 팩트는 특정 지역에서 특정 제품의 판매를 나타내는 레코드일 수 있으며, 차원은 지역, 제품 등의 집합을 나타냅니다.For example, a fact can be a record representing a sale of a particular product in a specific region while the dimension represents a set of regions, products and so on. 팩트 테이블과 차원 테이블은 기본/외래 키 관계를 통해 연결됩니다.The fact and dimension tables are connected through the a primary/foreign key relationship. 가장 일반적으로 사용되는 분석 쿼리는 하나 이상의 차원 테이블을 팩트 테이블에 조인하는 것입니다.Most commonly used analytics queries join one or more dimension tables with the fact table.

차원 테이블을 제품으로 간주하겠습니다.Let us consider a dimension table products. 일반적인 기본 키는 주로 string 데이터 형식으로 표현되는 productcode가 됩니다.a typical primary key will be productcode which is commonly represented as string data type. 쿼리 성능을 위해 일반적으로 정수 열인 대리 키를 만들어 팩트 테이블에서 차원 테이블의 행을 참조하는 것이 가장 좋습니다.For performance of queries, it is a best practice to create surrogate key, typically an integer column, to refer to the row in the dimension table from the fact table.

columnstore 인덱스는 숫자 또는 정수를 기반으로 하는 키가 포함된 조인/조건자를 사용하여 분석 쿼리를 매우 효율적으로 실행합니다.The columnstore index runs analytics queries with joins/predicates involving numeric or integer based keys very efficiently. 그러나 많은 고객 작업에서 팩트/차원 테이블을 연결하는 문자열 기반 열이 사용되고 있고 그 결과 columnstore 인덱스를 사용하는 쿼리 성능은 직접 수행할 때와는 다릅니다.However, in many customer workloads, we find the use to string based columns linking fact/dimension tables and with the result the query performance with columnstore index was not as performing. SQL Server 2016은 문자열 열이 있는 조건자를 SCAN 노드로 푸시다운하여 문자열 기반의 열을 사용하는 분석 쿼리의 성능을 크게 향상시켰습니다.SQL Server 2016 improves the performance of analytics queries with string based columns significantly by pushing down the predicates with string columns to the SCAN node

문자열 조건자 푸시다운을 통해 열에서 만든 주/보조 사전을 활용하여 쿼리 성능을 개선합니다.String predicate pushdown leverages the primary/secondary dictionary created for column(s) to improve the query performance. 예를 들어 100개의 개별 문자열 값으로 구성된 행 그룹에서 문자열 열 세그먼트가 있다고 간주해 보겠습니다.For example, let us consider string column segment within a rowgroup consisting of 100 distinct string values. 즉, 1백만 개의 행이라고 가정하면 각 개별 문자열 값은 평균 10,000번 참조됩니다.This means each distinct string value is referenced 10,000 times on average assuming 1 million rows .

문자열 조건자 푸시다운을 사용하면 쿼리 실행 시 사전에 있는 값으로 조건자를 계산하고 조건을 충족할 경우 사전 값을 참조하는 모든 행이 자동으로 조건이 충족됩니다.With string predicate pushdown, the query execution computes the predicate against the values in the dictionary and if it qualifies, all rows referring to the dictionary value are automatically qualified. 이렇게 하면 두 가지 방법으로 성능이 향상됩니다.This improves the performance in two ways. 먼저 정규화된 행만 반환되어 SCAN 노드에서 받아야 하는 행 수를 줄입니다.First, only the qualified row are returned reducing number of the rows that need to flow out of SCAN node. 다음으로 문자열 비교 수가 상당히 줄어듭니다.Second, the number of string comparisons are significantly reduced. 이 예제에서는 1백만 개의 비교에 대해 100개의 문자열 비교만 필요합니다.In this example, only 100 string comparisons are required as against 1 million comparisons. 아래 설명된 대로 몇 가지 제한 사항이 있습니다.There are some limitations as described below

  • 델타 행 그룹에 대한 문자열 조건자 푸시다운이 없습니다.No string predicate pushdown for delta rowgroups. 델타 행 그룹의 열에 대한 사전이 없습니다.There is no dictionary for columns in delta rowgroups

  • 사전 항목이 64,000개 항목을 초과하면 문자열 조건자 푸시다운이 없습니다.No string predicate pushdown if dictionary exceeds 64k entries

  • Null을 평가하는 식이 지원되지 않습니다.Expression evaluating NULLs are not not supported

참고 항목See Also

Columnstore 인덱스 가이드Columnstore Indexes Guide
Columnstore 인덱스 데이터 로드Columnstore Indexes Data Loading
버전이 지정된 Columnstore 인덱스 기능 요약Columnstore Indexes Versioned Feature Summary
Columnstore 인덱스 쿼리 성능 Columnstore Indexes Query Performance
실시간 운영 분석을 위한 Columnstore 시작 Get started with Columnstore for real time operational analytics
데이터 웨어하우스용 Columnstore 인덱스Columnstore Indexes for Data Warehousing
Columnstore 인덱스 조각 모음Columnstore Indexes Defragmentation