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

Статья
06/01/2023

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

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

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

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

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

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

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

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

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

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

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

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

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

Параметр DMV sys.dm_pdw_nodes_db_column_store_row_group_physical_stats (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 МБ, поэтому когда этот предел достигается, группа строк сжимается. Если складывается такая ситуация, рекомендуется поместить проблемный столбец в отдельную таблицу.

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

Чтобы просмотреть оценку требований к памяти для сжатия группы строк максимального размера в индекс columnstore, вы можете создать образец представления dbo.vCS_mon_mem_grant. Этот запрос показывает размер временно предоставляемого буфера памяти, который требуется группе строк для сжатия в columnstore.

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

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

Примечание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Совет

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

Настройка MAXDOP

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

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

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

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

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

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

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

Сведения об увеличении числа DWU см.в разделе Масштабирование производительности.
Сведения об изменении класса ресурсов для запросов см. в разделе Пример изменения класса ресурсов пользователя.

Дальнейшие шаги

Дополнительные возможности по повышению производительности выделенного пула SQL см. в этой статье.