Поделиться через

Оптимизация качества групп строк для колонночных индексов в выделенном пуле SQL

  • Статья

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

Почему размер группы строк имеет значение

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

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

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

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

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

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

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

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

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

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

Как мониторить качество группы строк

Динамическое представление управления 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. Этот запрос показывает размер предоставления памяти, необходимый для сжатия группы строк в колонковое хранилище.

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

  • 72 МБ +
  • #строки × #столбцы × 8 байт +
  • #rows * #short-string-columns * 32 байт +
  • #long-длинные строковые столбцы * 16 МБ для словаря сжатия

Примечание.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Упрощение запроса загрузки

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

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

Подсказка

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

Настройка MAXDOP

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

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

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

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

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

Размер DWU и класс ресурсов пользователя вместе определяют, сколько памяти доступно для запроса пользователя.

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

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

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