Производительность запросов по индексам columnstoreColumnstore indexes - Query performance

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure даAzure Synapse Analytics (хранилище данных SQL) даParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Рекомендации по достижению высокой производительности запросов, для обеспечения которой предназначены индексы columnstore.Recommendations for achieving the very fast query performance that columnstore indexes are designed to provide.

Индексы columnstore позволяют повысить производительность обработки аналитических рабочих нагрузок и запросов хранилища данных максимум в 100 раз и увеличить эффективность сжатия до 10 раз по сравнению с традиционными индексами rowstore.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 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 (13.x);SQL Server 2016 (13.x) группы строк пропускаются в ходе обработки запроса.In SQL Server 2016 (13.x);SQL Server 2016 (13.x), you can find out number rowgroups skipped as part of query processing.

  • Используйте кластеризованный индекс rowstore.Leverage the rowstore clustered index. Если общий предикат запроса находится в столбце (например, C1), который не связан с порядком вставки строки, можно создать кластеризованный индекс rowstore в столбцах C1, а затем создать кластеризованный индекс columnstore, удалив кластеризованный индекс rowstore.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 columnstore index by dropping the rowstore clustered index. Если кластеризованный индекс columnstore создается явным образом с использованием MAXDOP = 1, полученный кластеризованный индекс columnstore будет идеально упорядочен в столбце C1.if you create the clustered columnstore index explicitly using MAXDOP = 1, the resulting clustered columnstore index is perfectly ordered on column C1. Если задать значение MAXDOP = 8, возникнет наложение значений в 8 группах строк.If you specify MAXDOP = 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. Для некластеризованного индекса columnstore (NCCI) следует учесть, что если базовая таблица 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. Планирование достаточного объема памяти для параллельного создания индексов columnstore2. Plan for enough memory to create columnstore indexes in parallel

Создание индекса columnstore по умолчанию является параллельно выполняемой операцией, если ресурсы памяти неограниченны.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. При достаточном объеме памяти создание индекса columnstore выполняется в 1,5 раза дольше, чем создание сбалансированного дерева для тех же столбцов.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. Например, если в таблице имеется менее миллиона строк, то SQL ServerSQL Server будет использовать всего один поток для создания индекса columnstore.For example, if your table has fewer than one million rows, SQL ServerSQL Server will use only one thread to create the columnstore index.

Если в таблице более миллиона строк, но SQL ServerSQL Server не может получить объем памяти, достаточный для создания индекса с помощью MAXDOP, то SQL ServerSQL Server автоматически уменьшит MAXDOP в соответствии с наличием памяти.If your table has more than one million rows, but SQL ServerSQL Server cannot get a large enough memory grant to create the index using MAXDOP, SQL ServerSQL 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 (13.x);SQL Server 2016 (13.x), запрос будет всегда работать в пакетном режиме.Beginning with SQL Server 2016 (13.x);SQL Server 2016 (13.x), the query will always operate in batch mode. В предыдущих выпусках пакетное выполнение используется, только если значение DOP больше единицы.In previous releases, batch execution is only used when DOP is greater than one.

Пояснения о производительности columnstoreColumnstore Performance Explained

Индексы columnstore обеспечивают высокую производительность запросов, объединяя высокоскоростную пакетную обработку в памяти с методами, значительно сокращающими требования к операциям ввода-вывода.Columnstore indexes achieve high query performance by combining high-speed in-memory batch mode processing with techniques that greatly reduce I/O requirements. Так как аналитические запросы сканируют большое количество строк, они обычно связаны с операциями ввода-вывода. Поэтому уменьшение числа таких операций имеет важное значение для разработки индексов columnstore.Since analytics queries scan large numbers of rows, they are typically IO-bound, and therefore reducing I/O 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 сокращают число операций ввода-вывода и оптимизируют операции в памяти с помощью сжатия данных, пакетной обработки, исключения columnstore и исключения групп строк.Columnstore indexes reduce I/O and optimize in-memory operations through high data compression, columnstore elimination, rowgroup elimination, and batch processing.

Сжатие данныхData compression

Индексы columnstore позволяют повысить эффективность сжатия данных в 10 раз по сравнению с индексами rowstore.Columnstore indexes achieve up to 10x greater data compression than rowstore indexes. Это значительно уменьшает число операций ввода-вывода, необходимых для выполнения запросов аналитики, и, следовательно, повышает производительность запросов.This greatly reduces the I/O 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 сохраняют данные в памяти в сжатом виде, что позволяет сократить число операций ввода-вывода путем уменьшения количества раз считывания одних и тех же данных в память.Columnstore indexes store data in compressed form in memory which reduces I/O by reducing the number of times the same data is read into memory. Например, за счет десятикратного сжатия индексы columnstore могут хранить в десять раз больше данных в памяти по сравнению с хранением данных в несжатом виде.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 without 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. Если таблица фактов состоит из 100 миллионов строк, столбец страны можно будет легко сжать, а для его хранения потребуется очень мало места.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. Эта возможность, называемая исключением столбцов, дополнительно уменьшает число операций ввода-вывода для выполнения запроса и, соответственно, повышает производительность запросов.This ability, called column elimination, further reduces I/O 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. Чтобы извлечь конкретные значения столбцов, обработчику запросов требуется считать всю строку, что приводит к росту операций ввода-вывода из-за ненужного считывания в память дополнительных данных.The query processor needs to read in an entire row to retrieve specific column values, which increases I/O 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 %, так как предполагается, что все столбцы имеют одинаковый размер.This reduces I/O 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 может пропускать чтение в группах строк, которые не содержат данных, необходимых для получения результата запроса, без фактического выполнения операций ввода-вывода.By using metadata, the columnstore index is able to skip reading in the rowgroups that do not contain data required for the query result, all without actual I/O. Эта возможность, называемая исключением групп строк, уменьшает число операций ввода-вывода для полных сканирований таблиц и, соответственно, повышает производительность запросов.This ability, called rowgroup elimination, reduces I/O 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 (13.x);SQL Server 2016 (13.x), в кластеризованном индексе columnstore можно создавать один или несколько обычных некластеризованных индексов в виде сбалансированного дерева так же, как в куче rowstore.Starting with SQL Server 2016 (13.x);SQL Server 2016 (13.x), you can create one or more regular nonclustered B-tree indexes on a clustered columnstore index just like you can on a rowstore heap. Некластеризованные индексы в виде сбалансированного дерева могут ускорить выполнение запроса, содержащего предикат равенства или предикат с небольшим диапазоном значений.The nonclustered B-tree 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 modeled their sales data using a fact table with clustered columnstore index. При каждой новой операции продажи происходит сохранение различных атрибутов транзакции, включая дату продажи.Each new sale stores various attributes of the transaction including the date a product was sold. Что интересно, несмотря на то, что индексы columnstore не гарантируют поддержку сортировки, строки в эту таблицу будут загружаться с сортировкой по дате.Interestingly, even though columnstore indexes do not guarantee a sorted order, the rows in this table will be 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. Если условию предиката запроса не соответствует ни один диапазон сегментов столбца, пропускаются все группы строк без выполнения каких-либо операций ввода-вывода.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.

Дополнительные сведения о группах строк см. в руководстве по индексам ColumnstoreFor 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. Например, операции 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 (11.x)SQL Server 2012 (11.x), набор операторов, которые могут выполняться в пакетном режиме, постоянно расширяется.Since the columnstore index was introduced in SQL Server 2012 (11.x)SQL Server 2012 (11.x), there is a sustained effort to expand the operators that can be executed in 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 2012 (11.x)SQL Server 2012 (11.x) SQL Server 2014 (12.x)SQL Server 2014 (12.x) SQL Server 2016 (13.x);SQL Server 2016 (13.x) и База данных SQLSQL Database1and База данных SQLSQL Database1 КомментарииComments
Операции DML (вставка, удаление, обновление, объединение)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 index scancolumnstore index scan SCANSCAN Н/ДNA даyes даyes Для индексов columnstore можно передать предикат на узел SCAN.For columnstore indexes, we can push the predicate to the SCAN node.
columnstore Index Scan (nonclustered)columnstore Index Scan (nonclustered) SCANSCAN даyes даyes даyes даyes
index seekindex seek Н/ДNA Н/ДNA нетno Операции поиска выполняются с помощью некластеризованного индекса в виде сбалансированного дерева в режиме строк.We perform a seek operation through a nonclustered B-tree 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 UNION и UNION ALLUNION and UNION ALL нетno даyes даyes
фильтрfilter Применение предикатов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 1single-threaded queries running under MAXDOP 1 нетno нетno даyes
однопоточные запросы с планом последовательных запросовsingle-threaded queries with a serial query plan нетno нетno даyes
sortsort Упорядочение по предложению в SCAN с индексом columnstore.Order by clause on SCAN with columnstore index. нетno нетno даyes
top sorttop sort нетno нетno даyes
window aggregateswindow aggregates Н/ДNA Н/ДNA даyes Новый оператор в SQL Server 2016 (13.x);SQL Server 2016 (13.x).New operator in SQL Server 2016 (13.x);SQL Server 2016 (13.x).

1Применяется к SQL Server 2016 (13.x);SQL Server 2016 (13.x), уровням База данных SQLSQL Database "Премиум", "Стандартный" — S3 и выше, всем уровням виртуальных ядер, а также Параллельное хранилище данныхParallel Data Warehouse.1 Applies to SQL Server 2016 (13.x);SQL Server 2016 (13.x), База данных SQLSQL Database Premium tiers, Standard tiers - S3 and above, and all vCore tiers, and Параллельное хранилище данныхParallel 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 (13.x);SQL Server 2016 (13.x) операцию статистического вычисления можно включить на узле SCAN, чтобы значительно повысить производительность статистических вычислений на основе пакетного режима. При этом должны соблюдаться указанные далее условия.While this delivers good performance, but with SQL Server 2016 (13.x);SQL Server 2016 (13.x), 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 с предложением GROUP BY.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.
    • tinyint, int, bigint, smallint, bittinyint, int, bigint, smallint, bit
    • smallmoney, money, decimal и numeric с точностью <= 18smallmoney, money, decimal and numeric with precision <= 18
    • smalldate, date, datetime, datetime2, timesmalldate, 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

При разработке схемы хранилища данных рекомендуется использовать схему типа "звезда" или "снежинка", состоящую из одной или нескольких таблиц фактов и нескольких таблиц измерений.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 a primary/foreign key relationship. Наиболее часто используемые аналитические запросы присоединяются к одной или нескольким таблицам измерений с помощью таблицы фактов.Most commonly used analytics queries join one or more dimension tables with the fact table.

Рассмотрим таблицу измерения Products.Let us consider a dimension table Products. Стандартным первичным ключом будет ProductCode, который обычно представлен как тип данных string.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 (13.x);SQL Server 2016 (13.x) существенно повышает производительность аналитических запросов со столбцами на основе строк, включая предикаты со строковыми столбцами на узле SCAN.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:

  1. Возвращается только соответствующая строка, что приводит к сокращению числа строк, которые должны передаваться из узла SCAN.Only the qualified row is returned reducing number of the rows that need to flow out of SCAN node.

  2. Значительно уменьшается число сравнений строк.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 КБ.No string predicate pushdown if dictionary exceeds 64 KB entries.
    • Выражения, значением вычисления которых является NULL, не поддерживаются.Expression evaluating NULLs are not supported.

См. также:See Also

Руководство по проектированию индексов columnstore Columnstore Indexes Design Guidance
Индексы columnstore. Руководство по загрузке данных Columnstore Indexes Data Loading Guidance
Начало работы с Columnstore для получения операционной аналитики в реальном времени Get started with Columnstore for real time operational analytics
Индексы сolumnstore для хранилищ данных Columnstore Indexes for Data Warehousing
Реорганизация и перестроение индексов Reorganize and Rebuild Indexes
Архитектура индексов columnstore Columnstore Index Architecture
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL)ALTER INDEX (Transact-SQL)