Индексы columnstore. Руководство по загрузке данныхColumnstore indexes - Data loading guidance

Применимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed Instance даAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics даПараллельное хранилище данныхParallel Data WarehouseyesПараллельное хранилище данныхParallel Data WarehouseПрименимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed Instance даAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics даПараллельное хранилище данныхParallel Data WarehouseyesПараллельное хранилище данныхParallel Data Warehouse

Рекомендации и параметры для загрузки данных в индексы columnstore с помощью стандартных методов массовой загрузки и тонкой вставки SQL.Options and recommendations for loading data into a columnstore index by using the standard SQL bulk loading and trickle insert methods. Загрузка данных в индекс columnstore является неотъемлемой частью любого процесса хранения данных, так как данные перемещаются в индекс при подготовке к анализу.Loading data into a columnstore index is an essential part of any data warehousing process because it moves data into the index in preparation for analytics.

Незнакомы с индексами columnstore?New to columnstore indexes? См. статьи, посвященные обзору индексов columnstore и их архитектуре.See Columnstore indexes - overview and Columnstore Index Architecture.

Что такое массовая загрузка?What is bulk loading?

Массовая загрузка — это тип добавления большого числа строк в хранилище данных.Bulk loading refers to the way large numbers of rows are added to a data store. Это самый эффективный подход к перемещению данных в индекс columnstore, так как перемещаются пакеты строк.It is the most performant way to move data into a columnstore index because it operates on batches of rows. При массовой загрузке группы строк максимально заполняются, а затем сжимаются непосредственно в columnstore.Bulk loading fills rowgroups to maximum capacity and compresses them directly into the columnstore. В deltastore отправляются только те строки, которые по завершении загрузки не собирают даже минимальное число строк (102 400) на группу строк.Only rows at the end of a load that don't meet the minimum of 102,400 rows per rowgroup go to the deltastore.

Чтобы выполнить массовую загрузку, вы можете использовать программу bcp, службы Integration Services или выбрать строки из промежуточной таблицы.To perform a bulk load, you can use bcp Utility, Integration Services, or select rows from a staging table.

Загрузка в кластеризованный индекс columnstoreLoading into a clustered columnstore index

Как видно на диаграмме, массовая загрузка:As the diagram suggests, a bulk load:

  • Не выполняет предварительную сортировку данных.Does not pre-sort the data. Данные вставляются в группы строк в порядке их получения.Data is inserted into rowgroups in the order it is received.
  • Если размер пакета > = 102 400, строки вставляются непосредственно в сжатые группы строк.If the batch size is >= 102400, the rows are directly into the compressed rowgroups. Для обеспечения эффективного массового импорта рекомендуется выбирать размер пакета > = 102400, так как можно избежать перемещения строк данных в разностные группы строк до того, как в конечном счете фоновый поток, а именно задача переноса кортежей (TM), выполнит перемещение строк в сжатые группы строк.It is recommended that you choose a batch size >=102400 for efficient bulk import because you can avoid moving data rows to a delta rowgroups before the rows are eventually moved to compressed rowgroups by a background thread, Tuple mover (TM).
  • Если размер пакета < 102 400 или количество оставшихся строк < 102 400, строки будут загружены в разностные группы строк.If the batch size < 102,400 or if the remaining rows are < 102,400, the rows are loaded into delta rowgroups.

Примечание

В таблице rowstore с данными некластеризованного индекса columnstore SQL ServerSQL Server всегда вставляет данные в базовую таблицу.On a rowstore table with a nonclustered columnstore index data, SQL ServerSQL Server always inserts data into the base table. Данные никогда не вставляются непосредственно в индекс columnstore.The data is never inserted directly into the columnstore index.

Ниже приведены встроенные механизмы оптимизации производительности в операции массовой загрузки.Bulk loading has these built-in performance optimizations:

  • Параллельные загрузки. У вас может быть несколько параллельных массовых загрузок (с командой bcp или инструкцией BULK INSERT). Каждая их них загружает отдельный файл данных.Parallel loads: You can have multiple concurrent bulk loads (bcp or bulk insert) that are each loading a separate data file. В отличие от массовых загрузок rowstore в SQL ServerSQL Server, TABLOCK задавать не нужно, так как каждый поток массового импорта будет загружать данные только в отдельные группы строк (сжатые или разностные) с монопольной блокировкой.Unlike rowstore bulk loads into SQL ServerSQL Server, you don't need to specify TABLOCK because each bulk import thread will load data exclusively into separate rowgroups (compressed or delta rowgroups) with exclusive lock on it.

  • Ограниченное протоколирование. Прямая загрузка данных в сжатые группы строк приводит к существенному уменьшению размера журнала.Reduced Logging: The data that is directly loaded into compressed row groups leads to significant reduction in the size of the log. Например, если данные были сжаты в 10 раз, соответствующий журнал транзакций будет приблизительно в 10 раз меньше без применения TABLOCK, простой модели восстановления или модели восстановления с неполным протоколированием.For example, if data was compressed 10x, the corresponding transaction log will be roughly 10x smaller without requiring TABLOCK or Bulk-logged/Simple recovery model. Все данные, которые попадают в разностную группу строк, подвергаются полному протоколированию.Any data that goes to a delta rowgroup is fully logged. Сюда входят все пакеты с размером менее 102 400 строк.This includes any batch sizes that are less than 102,400 rows. Рекомендуемый размер пакета — не менее 102 400.Best practice is to use batchsize >= 102400. Так как TABLOCK не требуется, данные можно загружать параллельно.Since there is no TABLOCK required, you can load the data in parallel.

  • Минимальное протоколирование. Сократить протоколирование можно еще больше, если выполнить требования для минимального протоколирования.Minimal logging: You can get further reduction in logging if you follow the prerequisites for minimal logging. Однако, в отличие от загрузки данных в rowstore, параметр TABLOCK приводит к применению к таблице блокировки X, а не BU (массовое обновление). Поэтому параллельная загрузка данных невозможна.However, unlike loading data into a rowstore, TABLOCK leads to an X lock on the table rather than a BU (Bulk Update) lock and therefore parallel data load cannot be done. Дополнительные сведения о блокировке см. в разделе Блокировки и управление версиями строк.For more information on locking, see Locking and row versioning.

  • Оптимизация блокировки. Блокировка X автоматически применяется к группе строк при загрузке данных в сжатую группу строк.Locking Optimization: The X lock on a row group is automatically acquired when loading data into a compressed row group. Однако при массовой загрузке в разностную группу строк устанавливается блокировка X для группы строк, но SQL ServerSQL Server по-прежнему блокирует СТРАНИЦУ или ЭКСТЕНТ, так как блокировка X группы строк не является частью иерархии блокировки.However, when bulk loading into a delta rowgroup, an X lock is acquired at rowgroup but SQL ServerSQL Server still locks the PAGE/EXTENT because X rowgroup lock is not part of locking hierarchy.

Если имеется некластеризованный индекс сбалансированного дерева для индекса columnstore, оптимизация блокировки или ведения журнала для самого индекса невозможна, но оптимизация кластеризованного индекса columnstore, как описано выше, возможна.If you have a nonclustered B-tree index on a columnstore index, there is no locking or logging optimization for the index itself but the optimizations on clustered columnstore index as described above are applicable.

Обратите внимание, что операции DML (вставка, удаление, обновление) не являются операциями пакетного режима, так как они не выполняются параллельно.Please note, DML (insert, delete, update) is not a batch mode operation because it is not parallel.

Планирование размеров массовых загрузок для уменьшения числа разностных групп строкPlan bulk load sizes to minimize delta rowgroups

Индексы columnstore наиболее эффективны, когда большинство строк не находятся в разностных группах строк, а сжаты в columnstore.Columnstore indexes perform best when most of the rows are compressed into the columnstore and not sitting in delta rowgroups. Лучше всего изменить размер загрузок, чтобы строки сразу отправлялись в columnstore, обходя deltastore.It's best to size your loads so that rows go directly to the columnstore and bypass the deltastore as much as possible.

В следующих сценариях описано, когда загруженные строки перейдут непосредственно в columnstore, а когда — в deltastore.These scenarios describe when loaded rows go directly to the columnstore or when they go to the deltastore. В примере каждая rowgroup может иметь 102 400-1 048 576 строк на rowgroup.In the example, each rowgroup can have 102,400-1,048,576 rows per rowgroup. На практике, если не хватает памяти, максимальный размер группы строк может быть меньше 1 048 576 строк.In practice, the maximum size of a rowgroup can be smaller than 1,048,576 rows when there is memory pressure.

Строки для массовой загрузкиRows to Bulk Load Строки, добавленные в сжатую группу строкRows Added to the Compressed Rowgroup Строки, добавленные в разностную группу строкRows Added to the Delta Rowgroup
102 000102,000 00 102 000102,000
145,000145,000 145,000145,000

Размер группы строк: 145,000Rowgroup size: 145,000
00
1,048,5771,048,577 1 048 5761,048,576

Размер группы строк: 1 048 576.Rowgroup size: 1,048,576.
11
2,252,1522,252,152 2,252,1522,252,152

Размеры групп строк: 1 048 576, 1 048 576, 155 000.Rowgroup sizes: 1,048,576, 1,048,576, 155,000.
00
     

В следующем примере показаны результаты загрузки 1 048 577 строк в таблицу.The following example shows the results of loading 1,048,577 rows into a table. Результаты показывают наличие одной СЖАТОЙ rowgroup в columnstore (в виде сжатых сегментов столбцов) и 1 строки в deltastore.The results show that one COMPRESSED rowgroup in the columnstore (as compressed column segments), and 1 row in the deltastore.

SELECT object_id, index_id, partition_number, row_group_id, delta_store_hobt_id, 
  state, state_desc, total_rows, deleted_rows, size_in_bytes   
FROM sys.dm_db_column_store_row_group_physical_stats  

Элементы rowgroup и deltastore для загрузки пакетаRowgroup and deltastore for a batch load

Использование промежуточной таблицы для улучшения производительностиUse a staging table to improve performance

Если вы загружаете данные только для промежуточного хранения перед их последующими преобразованиями, загрузка таблицы в таблицу кучи будет происходить значительно быстрее по сравнению с загрузкой данных в кластеризованную таблицу columnstore.If you are loading data only to stage it before running more transformations, loading the table to heap table will be much faster than loading the data to a clustered columnstore table. Кроме того, загрузка данных во [временную таблицу] также будет происходить значительно быстрее, чем загрузка таблицы в постоянное хранилище.In addition, loading data to a [temporary table][Temporary] will also load much faster than loading a table to permanent storage.

Стандартным вариантом является загрузка данных в промежуточную таблицу, выполнение определенного преобразования и их загрузка в целевую таблицу с помощью следующей команды.A common pattern for data load is to load the data into a staging table, do some transformation and then load it into the target table using the following command

INSERT INTO <columnstore index>  
SELECT <list of columns> FROM <Staging Table>  

Эта команда загружает данные в индекс columnstore примерно так же, как и с помощью инструкций BCP или Bulk Insert, но в виде одного пакета.This command loads the data into the columnstore index in similar ways to BCP or Bulk Insert but in a single batch. Если количество строк в промежуточной таблице < 102 400, строки загружаются в разностную группу строк; в противном случае строки загружаются непосредственно в сжатую группу строк.If the number of rows in the staging table < 102400, the rows are loaded into a delta rowgroup otherwise the rows are directly loaded into compressed rowgroup. Одно из основных ограничений заключалось в том, что эта операция INSERT была однопотоковой.One key limitation was that this INSERT operation was single threaded. Чтобы загрузить данные в параллельном режиме, можно создать несколько промежуточных таблиц или выполнить инструкцию INSERT/SELECT с неперекрывающимися диапазонами строк из промежуточной таблицы.To load data in parallel, you could create multiple staging table or issue INSERT/SELECT with non-overlapping ranges of rows from the staging table. В SQL Server 2016 (13.x);SQL Server 2016 (13.x) это ограничение отсутствует.This limitation goes away with SQL Server 2016 (13.x);SQL Server 2016 (13.x). Команда, приведенная ниже, загружает данные из промежуточной таблицы в параллельном режиме, но необходимо задать TABLOCK.The command below loads the data from staging table in parallel but you will need to specify TABLOCK. Вам может показаться, что это противоречит тому, что говорилось ранее о массовой загрузке. Однако ключевое различие заключается в том, что параллельная загрузка данных из промежуточной таблицы осуществляется в рамках одной транзакции.You may find this contradictory to what was said earlier with bulkload but the key difference is the parallel data load from the staging table is executed under the same transaction.

INSERT INTO <columnstore index> WITH (TABLOCK) 
SELECT <list of columns> FROM <Staging Table>  

При загрузке в кластеризованный индекс columnstore из промежуточной таблицы можно выполнять указанные ниже операции оптимизации.There are following optimizations available when loading into clustered columnstore index from staging table:

  • Оптимизация журнала. При загрузке данных в сжатую группу строк загрузка будет протоколироваться в ограниченном объеме.Log Optimization: Reduced logging when the data is loaded into compressed rowgroup.
  • Оптимизация блокировки. При загрузке в сжатую группу строк требуется блокировка X на группе строк.Locking Optimization: When loading into compressed rowgroup, the X lock on rowgroup is acquired. Однако для разностной группы строк требуется блокировка X для группы строк, но SQL ServerSQL Server по-прежнему блокирует СТРАНИЦУ или ЭКСТЕНТ, так как блокировка X группы строк не является частью иерархии блокировки.However, with delta rowgroup, an X lock is acquired at rowgroup but SQL ServerSQL Server still locks the locks PAGE/EXTENT because X rowgroup lock is not part of locking hierarchy.

Если у вас есть один или несколько некластеризованных индексов, для самого индекса оптимизировать блокировку или ведение журнала невозможно, но оптимизация кластеризованного индекса columnstore, как описано выше, по-прежнему возможна.If you have one or more nonclustered indexes, there is no locking or logging optimization for the index itself, but the optimizations on the clustered columnstore index as described above are still there.

Что такое тонкая вставка?What is trickle insert?

Тонкая вставка — это способ перемещения отдельных строк в индекс columnstore.Trickle insert refers to the way individual rows move into the columnstore index. Для тонких вставок используется инструкция INSERT INTO.Trickle inserts use the INSERT INTO statement. При их использовании все строки попадают в deltastore.With trickle insert, all of the rows go to the deltastore. Это эффективно для небольшого числа строк и совсем непрактично для больших загрузок.This is useful for small numbers of rows, but not practical for large loads.

INSERT INTO <table-name> VALUES (<set of values>)  

Примечание

Следует отметить, что параллельные потоки, выполняемые с помощью инструкции INSERT INTO для вставки значений в кластеризованный индекс columnstore, могут вставлять строки в одну и ту же группу строк deltastore.Concurrent threads using INSERT INTO to insert values into a clustered columnstore index can insert rows into the same deltastore rowgroup.

Как только в группе окажется 1 048 576 строк, разностная группа строк будет отмечена как закрытая, но она по-прежнему будет доступна для запросов и операций обновления или удаления, хотя новые вставленные строки будут добавляться в существующую или новую группу строк deltastore.Once the rowgroup contains 1,048,576 rows, the delta rowgroup us marked closed but it is still available for queries and update/delete operations but the newly inserted rows go into an existing or newly created deltastore rowgroup. Существует фоновый поток, называемый задачей переноса кортежей (TM) , который периодически сжимает закрытые разностные группы строк примерно через каждые 5 минут.There is a background thread Tuple Mover (TM) that compresses the closed delta rowgroups periodically every 5 minutes or so. Для сжатия закрытой разностной группы строк можно явным образом вызвать следующую команду.You can explicitly invoke the following command to compress the closed delta rowgroup

ALTER INDEX <index-name> on <table-name> REORGANIZE  

Если необходимо принудительно закрыть и сжать разностную группу строк, можно выполнить следующую команду.If you want force a delta rowgroup closed and compressed, you can execute the following command. Выполнение этой команды может потребоваться, если вы загрузили строки и никакие новые строки не нужны.You may want run this command if you are done loading the rows and don't expect any new rows. Благодаря явному закрытию и сжатию разностной группы строк можно экономить место в хранилище и повышать производительность аналитических запросов.By explicitly closing and compressing the delta rowgroup, you can save storage further and improve the analytics query performance. Эту команду рекомендуется использовать, если вставка новых строк не нужна.A best practice is to invoke this command if you don't expect new rows to be inserted.

ALTER INDEX <index-name> on <table-name> REORGANIZE with (COMPRESS_ALL_ROW_GROUPS = ON)  

Как работает загрузка в секционированную таблицуHow loading into a partitioned table works

Для секционированных данных SQL ServerSQL Server сначала назначает каждую строку секции, а затем выполняет операции columnstore для данных в секции.For partitioned data, SQL ServerSQL Server first assigns each row to a partition, and then performs columnstore operations on the data within the partition. Каждая секция содержит собственные группы строк, а также как минимум одну разностную группу строк.Each partition has its own rowgroups and at least one delta rowgroup.

Дальнейшие действияNext steps

Теперь запись блога размещена на сайте techcommunity. Запись датируется 11.03.2015: Data Loading performance considerations with Clustered Columnstore indexes (Вопросы производительности загрузки данных с использованием кластеризованных индексов columnstore).Blog post now hosted on techcommunity, written 2015-03-11: Data Loading performance considerations with Clustered Columnstore indexes.