Evaluación y corrección del estado del índice de almacén de columnas agrupado en un grupo de SQL dedicado
Se aplica a: Azure Synapse Analytics
En este artículo se presenta un enfoque ligeramente diferente para evaluar el estado del índice de almacén de columnas agrupado (CCI). Siga los pasos descritos en las secciones siguientes o ejecute los pasos del cuaderno a través de Azure Data Studio.
Nota:
Antes de intentar abrir este cuaderno, asegúrese de que Azure Data Studio está instalado en el equipo local. Para instalarlo, vaya a Aprenda a instalar Azure Data Studio.
En general, dos factores principales afectan a la calidad de un CCI:
Compactar grupos de filas y metadatos : el recuento real de grupos de filas está cerca del recuento ideal para el número de filas del grupo de filas.
Grupos de filas comprimidos : los grupos de filas usan compresión de almacén de columnas.
Otras condiciones, como tablas pequeñas, tablas con particiones excesivas o tablas con particiones inferiores, son posiblemente de mala calidad o estado. Sin embargo, estas condiciones se clasifican mejor como oportunidades de mejora de diseño que se pueden evaluar en el paso 4.
Paso 1: Analizar un resumen del estado de CCI
Use la consulta siguiente para obtener una sola fila de métricas.
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
A partir del resultado, puede obtener información general sobre el estado de CCI para el grupo de SQL dedicado. Esta información no es directamente accionable, pero le ayuda a comprender la importancia de las rutinas de mantenimiento para lograr un estado ideal.
| Nombre de la columna | Descripción |
|---|---|
tables_assessed_count |
Recuento de tablas de CCI |
partitions_assessed_count |
Recuento de particiones Nota: Las tablas sin particiones contarán como 1. |
actual_rowgroup_count |
Recuento físico de grupos de filas |
ideal_rowgroup_count |
Número calculado de grupos de filas que serían ideales para el número de filas |
uncompressed_rowgroup_count |
Número de grupos de filas que contienen datos sin comprimir. (También conocido como: filas OPEN) |
actual_size_in_mb |
Tamaño físico de los datos de CCI en MB |
uncompressed_size_in_mb |
Tamaño físico de los datos sin comprimir en MB |
excess_pct |
Porcentaje de grupos de filas que se podrían optimizar aún más |
excess_size_in_mb |
MB estimados de los grupos de filas no optimizados |
Paso 2: Analizar la información detallada de CCI
La consulta siguiente proporciona un informe detallado de qué particiones de tabla son candidatas para volver a generarse. Los detalles de CCI se proporcionan en tres métricas que ayudan a identificar y priorizar las tablas y particiones que se beneficiarían más del mantenimiento. Establezca los valores de umbral adecuados para estas métricas en la cláusula WHERE y, a continuación, en la cláusula ORDER BY, use las métricas que más le interesen. La información detallada también puede ser útil para determinar si el grupo de SQL dedicado se ve afectado por un gran número de tablas pequeñas y fragmentadas, lo que puede provocar retrasos en la compilación.
Nota:
La función comentada fnMs_GenerateIndexMaintenanceScript es una función con valores de tabla (TVF) que puede generar scripts comunes para mantener índices. Si desea obtener los scripts de mantenimiento en el resultado, quite la marca de comentario de las líneas 37 y 39. Y antes de ejecutar la consulta, use el script de la sección Generar scripts de mantenimiento de índices para crear la función. Al ejecutar el script de mantenimiento que obtiene del resultado, asegúrese de usar una clase de recursos de tamaño adecuado, como largerc o xlargerc.
| Nombre de la columna | Característica de calidad | Descripción |
|---|---|---|
excess_pct |
Compactación | Porcentaje de grupos de filas que se podrían compactar aún más |
excess_size_in_mb |
Compactación | MB estimados de los grupos de filas no optimizados |
OPEN_rowgroup_size_in_mb |
Compresión | MB reales de datos sin comprimir en el índice |
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;
Paso 3: Qué hacer cuando el mantenimiento no mejora el estado de CCI
Realizar el mantenimiento en una tabla o partición puede dar lugar a uno de los escenarios siguientes:
excess_pctoexcess_size_in_mbes mayor que antes del mantenimiento.- Se produce un error en la instrucción de mantenimiento con memoria insuficiente.
Causas típicas
- Recursos insuficientes.
- Nivel de servicio insuficiente (DWU).
- La tabla es grande y no tiene particiones.
Mitigaciones recomendadas
- Aumente los recursos de las instrucciones de mantenimiento cambiando la clase del recurso o el grupo de cargas de trabajo del usuario de la ejecución.
- Aumente temporalmente el nivel de DWU para realizar el mantenimiento.
- Implemente una estrategia de creación de particiones para la tabla problemática y, a continuación, realice el mantenimiento en las particiones.
Paso 4: Comprobar las oportunidades de mejora del diseño
Aunque no es completa, la siguiente consulta puede ayudarle a identificar las posibles oportunidades que se encuentran habitualmente como causa de los problemas de rendimiento o mantenimiento relacionados con los CCI.
| Título de la oportunidad | Descripción | Recomendaciones |
|---|---|---|
| Tabla pequeña | La tabla contiene menos de 15 millones de filas. | Considere la posibilidad de cambiar el índice de CCI a:
|
| Oportunidad de creación de particiones o tabla con particiones inferiores | El recuento de grupos de filas ideal calculado es mayor que 180M (o ~188M filas) | Implemente una estrategia de creación de particiones o cambie la estrategia de creación de particiones existente para reducir el número de filas por partición a menos de 188M (aproximadamente tres grupos de filas por partición por distribución). |
| Tabla con excesivas particiones | La tabla contiene menos de 15 millones de filas para la partición más grande. | Tenga en cuenta lo siguiente:
|
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
Generación de scripts de mantenimiento de índices
Ejecute la consulta siguiente para crear dbo.fnMs_GenerateIndexMaintenanceScript una función en el grupo de SQL dedicado. Esta función genera scripts para optimizar el CCI de tres maneras. Puede usar esta función para mantener no solo los ÍNDICES de CCIs, sino también los índices agrupados (almacén de filas).
Parámetros
| Nombre de parámetro | Obligatorio | Description |
|---|---|---|
@object_id |
Y | object_id de la tabla que se va a establecer como destino |
@partition_number |
Y | partition_number de sys.partitions a destino. Si la tabla no tiene particiones, especifique 1. |
Tabla de salida
| Nombre de la columna | Description |
|---|---|
rebuild_script |
Instrucción generada ALTER INDEX ALL ... REBUILD para la tabla o partición especificada. Los montones sin particiones devolverán NULL. |
reorganize_script |
Instrucción generada ALTER INDEX ALL ... REORGANIZE para la tabla o partición especificada. Los montones sin particiones devolverán NULL. |
partition_switch_script |
Solo se aplica a las tablas con particiones; será NULL si la tabla no tiene particiones o si se especifica un número de partición no válido. Si el CCI se creó con una ORDER cláusula , se representará. |
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
Más información
Para obtener una comprensión más detallada y adquirir herramientas de evaluación adicionales para CCI en el grupo de SQL dedicado, consulte:
- Cuadro de herramientas de Azure Synapse
- Indexaciones de tablas en un grupo de SQL dedicado en Azure Synapse Analytics
- Introducción a los índices de almacén de columnas
- Optimización del rendimiento con el índice de almacén de columnas agrupado ordenado
- Guía de diseño de índices de almacén de columnas