Максимальное качество группы строк для производительности индекса columnstore

  • Статья

Качество группы строк определяется количеством строк в группе строк. Увеличение объема доступной памяти позволяет максимизировать число строк, сжимаемых индексом columnstore в каждую группу строк. Эти способы можно использовать, чтобы оптимизировать степень сжатия и производительность запросов для индексов columnstore.

На что влияет размер группы строк

Так как индекс columnstore обрабатывает таблицу, сканируя сегменты столбцов отдельных групп строк, увеличение максимального числа строк в каждой группе строк повышает производительность запросов. Если группы включают большое число строк, сжатие данных также оптимизируется, так как с диска считывается меньше данных.

Дополнительные сведения о группах строк см. в руководстве по индексам сolumnstore.

Целевой размер для групп строк

Чтобы повысить производительность запросов, нужно максимально увеличить число строк в каждой группе строк в индексе columnstore. Группа строк может включать до 1 048 576 строк. При этом использовать максимальное значение необязательно. Индексы columnstore обеспечивают высокую производительность, если группы строк включают хотя бы 100 000 строк.

Группы строк при сжатии можно обрезать

Во время массовой загрузки или повторном создании индекса columnstore иногда не хватает доступной памяти для сжатия всех строк, назначенных каждой группе строк. Из-за нехватки памяти индексы columnstore обрезают размеры групп строк, обеспечивая выполнение сжатия в columnstore.

Если памяти не хватает для сжатия хотя бы 10 000 строк в каждую группу строк, возникает ошибка.

Дополнительные сведения о выполнении массовой загрузки см. в статье Загрузка данных индексов ColumnStore.

Мониторинг качества групп строк

Динамическое административное представление (sys.dm_db_column_store_row_group_physical_stats содержит определение представления для базы данных SQL) предоставляет полезные сведения, например число строк в группах строк и причину уменьшения размера, если оно выполнялась. Вы можете создать следующее представление для удобства выполнения запросов к этому динамическому административному представлению, чтобы получить сведения об усечении групп строк.

CREATE VIEW dbo.vCS_rg_physical_stats
AS
WITH cte
AS
(
select   tb.[name]                    AS [logical_table_name]
,        rg.[row_group_id]            AS [row_group_id]
,        rg.[state]                   AS [state]
,        rg.[state_desc]              AS [state_desc]
,        rg.[total_rows]              AS [total_rows]
,        rg.[trim_reason_desc]        AS trim_reason_desc
,        mp.[physical_name]           AS physical_name
FROM    sys.[schemas] sm
JOIN    sys.[tables] tb               ON  sm.[schema_id]          = tb.[schema_id]
JOIN    sys.[pdw_table_mappings] mp   ON  tb.[object_id]          = mp.[object_id]
JOIN    sys.[pdw_nodes_tables] nt     ON  nt.[name]               = mp.[physical_name]
JOIN    sys.[dm_pdw_nodes_db_column_store_row_group_physical_stats] rg      ON  rg.[object_id]     = nt.[object_id]
                                                                            AND rg.[pdw_node_id]   = nt.[pdw_node_id]
                                        AND rg.[distribution_id]    = nt.[distribution_id]
)
SELECT *
FROM cte;

Столбец trim_reason_desc указывает, была ли усечена группа строк (trim_reason_desc = NO_TRIM означает, что усечения не было и качество группы строк является оптимальным). Ниже перечислены причины, указывающие на преждевременное усечение групп строк.

  • BULKLOAD. Эта причина усечения используется, когда входящий пакет строк для загрузки содержит менее 1 миллиона строк. При вставке более 100 000 строк обработчик создает сжатые группы строк (в отличие от вставки в разностное хранилище), но задает причину усечения BULKLOAD. В этом случае постарайтесь увеличить объем пакетной загрузки, чтобы она содержала больше строк. Также пересмотрите свою схему секционирования и убедитесь, что она не слишком детализирована, так как группы строк не могут охватывать границы секций.
  • MEMORY_LIMITATION. Для создания групп строк, содержащих 1 миллион строк, обработчику требуется определенный объем рабочей памяти. Если доступный объем памяти сеанса загрузки меньше необходимого объема рабочей памяти, то группы строк преждевременно усекаются. В следующих разделах описано, как оценить необходимый объем памяти и выделить дополнительные ресурсы.
  • DICTIONARY_SIZE. Эта причина усечения указывает на то, что усечение групп строк вызвано наличием по крайней мере одного столбца строки с широкими строками и (или) со строками, имеющими большое количество элементов. Размер словаря ограничен объемом памяти 16 МБ, поэтому когда этот предел достигается, группа строк сжимается. Если складывается такая ситуация, рекомендуется поместить проблемный столбец в отдельную таблицу.

Как рассчитать требования к памяти

Максимальный объем памяти, необходимый для сжатия одной группы строк, составляет приблизительно:

  • 72 МБ +
  • количество строк * количество столбцов * 8 байт +
  • количество строк * количество столбцов коротких строк * 32 байта +
  • количество столбцов длинных строк * 16 МБ для словаря сжатия

Примечание

Где столбцы коротких строк используют типы строковых данных размером <= 32 байта, а столбцы длинных строк используют типы строковых данных размером > 32 байта.

Используемый метод сжатия длинных строк предназначен для сжатия текста. Этот метод сжатия использует словари для хранения текстовых шаблонов. Максимальный размер словаря составляет 16 МБ. Для каждого столбца длинной строки в группе строк используется только один словарь.

Способы снижения требований к памяти

Используйте следующие методы, чтобы снизить требования к памяти для сжатия групп строк в индексах columnstore.

Используйте меньшее число столбцов

По возможности проектируйте таблицы с меньшим числом столбцов. Когда группа строк сжимается в columnstore, индекс columnstore сжимает каждый сегмент столбца отдельно. Следовательно, при сжатии группы строк с увеличением числа столбцов повышаются и требования к памяти.

Используйте меньшее число строковых столбцов

Столбцы строковых типов данных требуют больше памяти, чем числовые типы и типы даты. Чтобы снизить требования к памяти, можно удалить строковые столбцы из таблиц фактов и поместить их в меньшие таблицы измерений.

Дополнительные требования к памяти при сжатии строк:

  • для строковых типов данных до 32 символов может дополнительно потребоваться 32 байта на каждое значение;
  • строковые типы данных свыше 32 символов сжимаются с помощью методов словаря, а для каждого столбца в группе строк может дополнительно потребоваться 16 МБ для создания словаря.

Избегайте избыточного секционирования

Индексы columnstore создают одну или несколько групп строк в каждой секции. Для организации хранилища данных в Azure Synapse Analytics количество секций быстро растет, так как данные распределяются и каждое распределение секционируется. Если в таблице слишком много разделов, существующих строк может не хватить для заполнения групп строк. Недостаток строк не вызывает нехватку памяти при сжатии, но это приводит к снижению производительности запросов columnstore для групп строк.

Также рекомендуется избегать чрезмерного секционирования из-за перерасхода памяти при загрузке строк в индекс columnstore в секционированной таблице. При загрузке многие секции могут принимать входящие строки, которые хранятся в памяти, пока каждая секция не получит достаточное количество строк для сжатия. Слишком большое число секций приводит к избыточному потреблению памяти.

Упрощайте запросы загрузки

База данных распределяет память, выделенную для запросов, между всеми операторами запроса. Если запрос загрузки включает сложные сортировки и соединения, объем доступной для сжатия памяти снижается.

Разработайте такой запрос нагрузки, который позволит использовать ресурсы только для его загрузки. Если необходимо выполнить преобразование данных, выполните эту операцию отдельно от выполнения запроса загрузки. Например, запустите промежуточную обработку данных в таблице кучи, выполните преобразование и только потом загружайте промежуточную таблицу в индексе.

Настройка MAXDOP

В рамках каждого распределения группы строк сжимаются в columnstore параллельно, если для распределения доступно более одного ядра ЦП. Параллелизм потребляет дополнительную память, что может привести к ее нехватке и усечению строк.

Чтобы избежать этого, можно использовать указание запроса MAXDOP для принудительного выполнения загрузки в последовательном режиме в рамках каждого распределения.

CREATE TABLE MyFactSalesQuota
WITH (DISTRIBUTION = ROUND_ROBIN)
AS SELECT * FROM FactSalesQuota
OPTION (MAXDOP 1);

Способы выделения дополнительной памяти

Объем доступной для выполнения пользовательских запросов памяти определяется размером DWU и классом ресурсов пользователя. Чтобы увеличить объем выделенной для запросов загрузки памяти, можно увеличить число DWU или повысить класс ресурсов.

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

Дополнительные способы повышения производительности в Synapse SQL см. в статье Обзор производительности.