Оценка и исправление работоспособности кластеризованного индекса columnstore в выделенном пуле SQL
Область применения: Azure Synapse Analytics
В этой статье представлен немного другой подход к оценке работоспособности кластеризованного индекса columnstore (CCI). Выполните действия, описанные в следующих разделах, или выполните действия в записной книжке с помощью Azure Data Studio.
Примечание.
Прежде чем пытаться открыть эту записную книжку, убедитесь, что Azure Data Studio установлена на локальном компьютере. Чтобы установить его, перейдите к разделу "Узнайте, как установить Azure Data Studio".
В общем, два основных фактора влияют на качество CCI:
Компактные группы строк и метаданные . Фактическое число групп строк близко к идеальному количеству строк в группе строк.
Сжатые группы строк — группы строк используют сжатие columnstore.
Другие условия, такие как небольшие таблицы, чрезмерно секционированные таблицы или несекционированные таблицы, возможно, имеют плохое качество или работоспособность. Однако эти условия лучше классифицируются как возможности улучшения дизайна, которые можно оценить на шаге 4.
Шаг 1. Анализ сводки по работоспособности CCI
Получите одну строку метрик с помощью следующего запроса.
WITH cci_detail AS (
SELECT t.object_id,
rg.partition_number,
COUNT(*) AS total_rowgroup_count,
SUM(CASE WHEN rg.state = 1 THEN 1 END) AS open_rowgroup_count,
CEILING((SUM(rg.[total_rows]) - SUM(rg.deleted_rows))/COUNT(DISTINCT rg.distribution_id)/1048576.) * COUNT(DISTINCT rg.distribution_id) AS [ideal_rowgroup_count],
SUM(rg.size_in_bytes/1024/1024.) AS size_in_mb,
SUM(CASE WHEN rg.state = 1 THEN rg.size_in_bytes END /1024/1024.) AS open_size_in_mb
FROM sys.pdw_nodes_column_store_row_groups rg
JOIN sys.pdw_nodes_tables nt ON rg.object_id = nt.object_id
AND rg.pdw_node_id = nt.pdw_node_id
AND rg.distribution_id = nt.distribution_id
JOIN sys.pdw_table_mappings mp ON nt.name = mp.physical_name
JOIN sys.tables t ON mp.object_id = t.object_id
GROUP BY t.object_id,
rg.partition_number
)
SELECT COUNT(DISTINCT object_id) AS tables_assessed_count,
COUNT(*) AS partitions_assessed_count,
SUM(total_rowgroup_count) AS actual_rowgroup_count,
SUM(ideal_rowgroup_count) AS ideal_rowgroup_count,
SUM(open_rowgroup_count) AS uncompressed_rowgroup_count,
CAST(SUM(size_in_mb) AS DECIMAL(19, 4)) AS actual_size_in_mb,
CAST(SUM(open_size_in_mb) AS DECIMAL(19, 4)) AS uncompressed_size_in_mb,
CAST(((SUM(total_rowgroup_count) - SUM(ideal_rowgroup_count)) / SUM(total_rowgroup_count)) * 100. AS DECIMAL(9, 4)) AS excess_pct,
CAST(((SUM(total_rowgroup_count) - SUM(ideal_rowgroup_count)) / SUM(total_rowgroup_count)) * 1. AS DECIMAL(9, 4)) * SUM(size_in_mb) AS excess_size_in_mb
FROM cci_detail
В результате вы можете получить общие сведения о работоспособности CCI для выделенного пула SQL. Эта информация не является непосредственно практической, но помогает понять важность подпрограмм обслуживания для достижения идеального состояния.
| Имя столбца | Описание |
|---|---|
tables_assessed_count |
Количество таблиц CCI |
partitions_assessed_count |
Количество секций Примечание. Несекционированные таблицы будут учитываться как 1. |
actual_rowgroup_count |
Физическое число групп строк |
ideal_rowgroup_count |
Вычисляемое число групп строк, идеально подходящее для количества строк |
uncompressed_rowgroup_count |
Количество групп строк, содержащих несжатые данные. (Также называются строками OPEN) |
actual_size_in_mb |
Физический размер данных CCI в МБ |
uncompressed_size_in_mb |
Физический размер несжатых данных CCI в МБ |
excess_pct |
Процент групп строк, которые можно дополнительно оптимизировать |
excess_size_in_mb |
Предполагаемый объем в МБ из неоптимизированных групп строк |
Шаг 2. Анализ подробных сведений о CCI
Следующий запрос предоставляет подробный отчет о том, какие секции таблицы являются кандидатами для перестроения. Сведения о CCI предоставляются в трех метриках, которые помогают определить таблицы и секции, которые будут получать наибольшую пользу от обслуживания, а так же назначить им приоритет. Задайте соответствующие пороги для этих метрик в предложении WHERE, а затем в предложении ORDER BY используйте наиболее интересующие вас метрики. Кроме того, могут быть полезными подробные сведения, которые позволят определить, влияет ли на выделенный пул SQL большое количество небольших фрагментированных таблиц, что может привести к задержкам в компиляции.
Примечание.
Закомментированная fnMs_GenerateIndexMaintenanceScript функция — это функция с табличным значением (TVF), которая может создавать общие скрипты для поддержания индексов. Если вы хотите получить скрипты обслуживания в результате, раскомментируйте строки 37 и 39. Перед выполнением запроса используйте скрипт в разделе "Создание скриптов обслуживания индекса" для создания функции. При выполнении скрипта обслуживания, полученного из результата, обязательно используйте соответствующий класс ресурсов, например largerc или xlargerc.
| Имя столбца | Характеристика качества | Описание |
|---|---|---|
excess_pct |
Компактность | Процент групп строк, которые можно дополнительно сжать |
excess_size_in_mb |
Компактность | Предполагаемый объем в МБ из неоптимизированных групп строк |
OPEN_rowgroup_size_in_mb |
Сжатие | Фактический объем несжатых данных в индексе в МБ |
WITH cci_info AS(
SELECT t.object_id AS [object_id],
MAX(schema_name(t.schema_id)) AS [schema_name],
MAX(t.name) AS [table_name],
rg.partition_number AS [partition_number],
COUNT(DISTINCT rg.distribution_id) AS [distribution_count],
SUM(rg.size_in_bytes/1024/1024) AS [size_in_mb],
SUM(rg.[total_rows]) AS [row_count_total],
COUNT(*) AS [total_rowgroup_count],
CEILING((SUM(rg.[total_rows]) - SUM(rg.[deleted_rows]))/COUNT(DISTINCT rg.distribution_id)/1048576.) * COUNT(DISTINCT rg.distribution_id) AS [ideal_rowgroup_count],
SUM(CASE WHEN rg.[State] = 1 THEN 1 ELSE 0 END) AS [OPEN_rowgroup_count],
SUM(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] ELSE 0 END) AS [OPEN_rowgroup_rows],
CAST(SUM(CASE WHEN rg.[State] = 1 THEN rg.[size_in_bytes]/1024./1024. ELSE 0 END) AS DECIMAL(19, 4)) AS [OPEN_rowgroup_size_in_mb],
SUM(CASE WHEN rg.[State] = 2 THEN 1 ELSE 0 END) AS [CLOSED_rowgroup_count],
SUM(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] ELSE 0 END) AS [CLOSED_rowgroup_rows],
CAST(SUM(CASE WHEN rg.[State] = 2 THEN rg.[size_in_bytes]/1024./1024. ELSE 0 END) AS DECIMAL(19, 4)) AS [CLOSED_size_in_mb],
SUM(CASE WHEN rg.[State] = 3 THEN 1 ELSE 0 END) AS [COMPRESSED_rowgroup_count],
SUM(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] ELSE 0 END) AS [COMPRESSED_rowgroup_rows],
CAST(SUM(CASE WHEN rg.[State] = 3 THEN rg.[size_in_bytes]/1024./1024. ELSE 0 END) AS DECIMAL(19, 4)) AS [COMPRESSED_size_in_mb],
SUM(CASE WHEN rg.[State] = 3 THEN rg.[deleted_rows] ELSE 0 END) AS [COMPRESSED_rowgroup_rows_DELETED]
FROM sys.[pdw_nodes_column_store_row_groups] rg
JOIN sys.[pdw_nodes_tables] nt ON rg.[object_id] = nt.[object_id]
AND rg.[pdw_node_id] = nt.[pdw_node_id]
AND rg.[distribution_id] = nt.[distribution_id]
JOIN sys.[pdw_table_mappings] mp ON nt.[name] = mp.[physical_name]
JOIN sys.[tables] t ON mp.[object_id] = t.[object_id]
GROUP BY t.object_id,
rg.partition_number
)
, calc_excess AS(
SELECT *,
CAST(((total_rowgroup_count - ideal_rowgroup_count) / total_rowgroup_count) * 100. AS DECIMAL(9, 4)) AS [excess_pct],
CAST(((total_rowgroup_count - ideal_rowgroup_count) / total_rowgroup_count) * 1. AS DECIMAL(9, 4)) * size_in_mb AS [excess_size_in_mb]
FROM cci_info
)
SELECT calc_excess.*
-- , script.*
FROM calc_excess
-- CROSS APPLY dbo.fnMs_GenerateIndexMaintenanceScript(object_id, partition_number) AS script
WHERE -- set your own threshold(s) for the following; 0 is the ideal, but usually not practical
calc_excess.[excess_size_in_mb] > 300
OR calc_excess.excess_pct > 0.1
OR calc_excess.OPEN_rowgroup_size_in_mb > 100
ORDER BY calc_excess.[excess_size_in_mb] DESC;
Шаг 3. Что делать, если обслуживание не улучшает работоспособность CCI
Выполнение обслуживания в таблице или секции может привести к одному из следующих сценариев:
- Значение
excess_pctилиexcess_size_in_mbбольше, чем до обслуживания. - Инструкция обслуживания завершается сбоем, вызванным нехваткой памяти.
Типичные причины
- Нехватка ресурсов.
- Недостаточно уровня обслуживания (DWU).
- Большая или несекционированная таблица.
Рекомендуемые меры по устранению проблем
- Увеличьте объем ресурсов для инструкций обслуживания, изменив класс ресурсов или группу рабочей нагрузки пользователя, выполняющего задачу.
- Временно увеличьте уровень DWU для обслуживания.
- Реализуйте стратегию секционирования для проблемной таблицы, а затем выполните обслуживание секций.
Шаг 4. Проверка возможностей для улучшения проектирования
Приведенный ниже запрос не является исчерпывающим, но он поможет определить возможные сценарии, которые обычно вызывают проблемы с производительностью или обслуживанием, связанными с индексами CCI.
| Название возможности | Описание | Рекомендации |
|---|---|---|
| Небольшая таблица | Таблица содержит менее 15 млн строк | Попробуйте изменить индекс с CCI на:
|
| Возможность секционирования или разделимая таблица | Вычисляемое идеальное число групп строк превышает 180M (или около 188M строк) | Реализуйте стратегию секционирования или измените существующую стратегию секционирования, чтобы уменьшить количество строк на секцию до менее 188 МЛН (примерно три группы строк на секцию на распределение). |
| Таблица с избыточным секционированием | Таблица содержит менее 15 млн строк для самой большой секции | Рассматривать:
|
WITH cci_info AS (
SELECT t.object_id AS [object_id],
MAX(SCHEMA_NAME(t.schema_id)) AS [schema_name],
MAX(t.name) AS [table_name],
rg.partition_number AS [partition_number],
SUM(rg.[total_rows]) AS [row_count_total],
CEILING((SUM(rg.[total_rows]) - SUM(rg.[deleted_rows]))/COUNT(DISTINCT rg.distribution_id)/1048576.) * COUNT(DISTINCT rg.distribution_id) AS [ideal_rowgroup_count]
FROM sys.[pdw_nodes_column_store_row_groups] rg
JOIN sys.[pdw_nodes_tables] nt ON rg.[object_id] = nt.[object_id]
AND rg.[pdw_node_id] = nt.[pdw_node_id]
AND rg.[distribution_id] = nt.[distribution_id]
JOIN sys.[pdw_table_mappings] mp ON nt.[name] = mp.[physical_name]
JOIN sys.[tables] t ON mp.[object_id] = t.[object_id]
GROUP BY t.object_id,
rg.partition_number
)
SELECT object_id,
MAX(SCHEMA_NAME),
MAX(TABLE_NAME),
COUNT(*) AS number_of_partitions,
MAX(row_count_total) AS max_partition_row_count,
MAX(ideal_rowgroup_count) partition_ideal_row_count,
CASE
-- non-partitioned tables
WHEN COUNT(*) = 1 AND MAX(row_count_total) < 15000000 THEN 'Small table'
WHEN COUNT(*) = 1 AND MAX(ideal_rowgroup_count) > 180 THEN 'Partitioning opportunity'
-- partitioned tables
WHEN COUNT(*) > 1 AND MAX(row_count_total) < 15000000 THEN 'Over-partitioned table'
WHEN COUNT(*) > 1 AND MAX(ideal_rowgroup_count) > 180 THEN 'Under-partitioned table'
END AS warning_category
FROM cci_info
GROUP BY object_id
Создание скриптов обслуживания индекса
Выполните следующий запрос, чтобы создать dbo.fnMs_GenerateIndexMaintenanceScript функцию в выделенном пуле SQL. Эта функция создает сценарии для оптимизации CCI тремя способами. Эту функцию можно использовать для поддержания не только индексов CCIs, но и кластеризованных (rowstore).
Параметры
| Наименование параметра | Обязательное поле | Описание |
|---|---|---|
@object_id |
Y | object_id целевой таблицы |
@partition_number |
Y | partition_number от sys.partitions целевого объекта. Если таблица не секционирована, укажите 1. |
Выходная таблица
| Имя столбца | Description |
|---|---|
rebuild_script |
Созданная ALTER INDEX ALL ... REBUILD инструкция для заданной таблицы или секции. Несекционированные кучы возвращаются NULL. |
reorganize_script |
Созданная ALTER INDEX ALL ... REORGANIZE инструкция для заданной таблицы или секции. Несекционированные кучы возвращаются NULL. |
partition_switch_script |
Применяется только к секционированных таблицам; Значение , NULL если таблица не секционирована или указан недопустимый номер секции. Если CCI был создан с предложением ORDER , он будет отображаться. |
CREATE FUNCTION dbo.fnMs_GenerateIndexMaintenanceScript (@object_id INT, @partition_number INT = 1)
RETURNS TABLE
AS
RETURN(
WITH base_info AS (
SELECT
t.object_id
, SCHEMA_NAME(t.schema_id) AS [schema_name]
, t.name AS table_name
, i.index_type
, i.index_cols
, i.index_type_desc
, tdp.distribution_policy_desc
, c.name hash_distribution_column_name
FROM sys.tables t
JOIN (
SELECT
i.object_id
, i.index_id
, MAX(i.type) AS index_type
, MAX(CASE WHEN i.type = 5 AND ic.column_store_order_ordinal != 0 THEN ' ORDER ' ELSE '' END)
+ '(' + STRING_AGG(
CASE
WHEN i.type IN (1, 5)
AND (ic.key_ordinal != 0 OR ic.column_store_order_ordinal != 0)
THEN c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END
END
, ',') WITHIN GROUP(ORDER BY ic.column_store_order_ordinal, ic.key_ordinal) + ')' AS index_cols
, MAX(i.type_desc)
+ CASE
WHEN MAX(i.type) IN (1, 5) THEN ' INDEX'
ELSE ''
END COLLATE SQL_Latin1_General_CP1_CI_AS AS index_type_desc
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.index_id <= 1
GROUP BY i.object_id, i.index_id
) AS i
ON t.object_id = i.object_id
JOIN sys.pdw_table_distribution_properties tdp ON t.object_id = tdp.object_id
LEFT JOIN sys.pdw_column_distribution_properties cdp ON t.object_id = cdp.object_id AND cdp.distribution_ordinal = 1
LEFT JOIN sys.columns c ON cdp.object_id = c.object_id AND cdp.column_id = c.column_id
WHERE t.object_id = @object_id
)
, param_data_type AS (
SELECT
pp.function_id
, typ.name AS data_type_name
, CAST(CASE
WHEN typ.collation_name IS NOT NULL THEN 1
WHEN typ.name LIKE '%date%' THEN 1
WHEN typ.name = 'uniqueidentifier' THEN 1
ELSE 0
END AS BIT) AS use_quotes_on_values_flag
FROM sys.partition_parameters pp
JOIN sys.types typ ON pp.user_type_id = typ.user_type_id
)
, boundary AS (
SELECT
t.object_id
, c.name AS partition_column_name
, pf.boundary_value_on_right
, prv.boundary_id
, prv.boundary_id + CASE WHEN pf.boundary_value_on_right = 1 THEN 1 ELSE 0 END AS [partition_number]
, CASE
WHEN pdt.use_quotes_on_values_flag = 1 THEN '''' + CAST(
CASE pdt.data_type_name
WHEN 'date' THEN CONVERT(char(10), prv.value, 120)
WHEN 'smalldatetime' THEN CONVERT(VARCHAR, prv.value, 120)
WHEN 'datetime' THEN CONVERT(VARCHAR, prv.value, 121)
WHEN 'datetime2' THEN CONVERT(VARCHAR, prv.value, 121)
ELSE prv.value
END
AS VARCHAR(32)) + ''''
ELSE CAST(prv.value AS VARCHAR(32))
END AS boundary_value
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id AND i.index_id <= 1
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id AND ic.partition_ordinal = 1
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
JOIN param_data_type pdt ON pf.function_id = pdt.function_id
JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
WHERE t.object_id = @object_id
)
, partition_clause AS (
SELECT
object_id
, COUNT(*) - 1 -- should always be the 2nd to last partition in stage table
+ CASE WHEN MAX([partition_number]) = @partition_number THEN 1 ELSE 0 END -- except when last partition
AS [source_partition_number]
, 'WHERE ' + MAX(partition_column_name)
+ CASE WHEN MAX(CAST(boundary_value_on_right AS TINYINT)) = 1 THEN
' >= ' + MIN(CASE WHEN [partition_number] = @partition_number THEN boundary_value END)
ELSE
' <= ' + MAX(CASE WHEN [partition_number] = @partition_number THEN boundary_value END)
END
+ ' AND ' + MAX(partition_column_name)
+ CASE WHEN MAX(CAST(boundary_value_on_right AS TINYINT)) = 1 THEN
' < ' + MAX(boundary_value)
ELSE
' > ' + MIN(boundary_value)
END AS filter_clause
, ', PARTITION (' + MAX(partition_column_name) + ' RANGE '
+ CASE WHEN MAX(CAST(boundary_value_on_right AS TINYINT)) = 1 THEN 'RIGHT' ELSE 'LEFT' END
+ ' FOR VALUES(' + STRING_AGG(boundary_value, ',') + '))' AS [partition_clause]
FROM boundary
WHERE [partition_number] BETWEEN @partition_number - 1 AND @partition_number + 1
GROUP BY object_id
)
SELECT
CASE WHEN index_type IN (1, 5) THEN 'ALTER INDEX ALL ON [' + [schema_name] + '].[' + [table_name] + '] REBUILD'
+ CASE WHEN partition_clause.[object_id] IS NOT NULL THEN ' PARTITION = ' + CAST(@partition_number AS VARCHAR(16)) ELSE '' END + ';' END AS [rebuild_script]
, CASE WHEN index_type IN (1, 5) THEN 'ALTER INDEX ALL ON [' + [schema_name] + '].[' + [table_name] + '] REORGANIZE'
+ CASE WHEN partition_clause.[object_id] IS NOT NULL THEN ' PARTITION = ' + CAST(@partition_number AS VARCHAR(16)) ELSE '' END
+ CASE WHEN index_type = 5 THEN ' WITH (COMPRESS_ALL_ROW_GROUPS = ON)' ELSE '' END + ';' END AS [reorganize_script]
, 'CREATE TABLE [' + schema_name + '].[' + table_name + '_p' + CAST(@partition_number AS VARCHAR(16)) + '_tmp] WITH(' + index_type_desc + ISNULL(index_cols, '')
+ ', DISTRIBUTION = ' + distribution_policy_desc + CASE WHEN distribution_policy_desc = 'HASH' THEN '(' + hash_distribution_column_name + ')' ELSE '' END
+ partition_clause.partition_clause + ') AS SELECT * FROM [' + [schema_name] + '].[' + [table_name] + '] ' + filter_clause + CASE WHEN index_type = 5 AND index_cols IS NOT NULL THEN ' OPTION(MAXDOP 1)' ELSE '' END + ';'
+ ' ALTER TABLE [' + schema_name + '].[' + table_name + '_p' + CAST(@partition_number AS VARCHAR(16)) + '_tmp] SWITCH PARTITION ' + CAST(source_partition_number AS VARCHAR(16))
+ ' TO [' + [schema_name] + '].[' + [table_name] + '] PARTITION ' + CAST(@partition_number AS VARCHAR(16))
+ ' WITH (TRUNCATE_TARGET = ON);'
+ ' DROP TABLE [' + schema_name + '].[' + table_name + '_p' + CAST(@partition_number AS VARCHAR(16)) + '_tmp];' AS [partition_switch_script]
FROM base_info
LEFT JOIN partition_clause
ON base_info.object_id = partition_clause.object_id
);
GO
Дополнительная информация
Чтобы получить более подробное представление и получить дополнительные средства оценки для CCI в выделенном пуле SQL, см. следующие сведения:
- Панель элементов Azure Synapse
- Индексы в таблицах выделенного пула SQL в Azure Synapse Analytics
- Общие сведения об индексах columnstore
- Performance tuning with result set caching (Настройка производительности с помощью упорядоченного кластеризованного индекса columnstore)
- Руководство по проектированию индексов columnstore