评估和更正专用 SQL 池中的聚集列存储索引运行状况
适用于：Azure Synapse Analytics
本文介绍一种稍微不同的方法来评估聚集列存储索引 (CCI) 运行状况。 按照以下部分中的步骤或通过 Azure Data Studio 执行笔记本中的步骤。
注意
在尝试打开此笔记本之前，请确保在本地计算机上安装了 Azure Data Studio。 若要安装它，请转到 了解如何安装 Azure Data Studio。
通常，有两个主要因素会影响 CCI 的质量：
压缩行组和元数据 - 实际行组计数接近行组中行数的理想计数。
压缩行组 - 行组使用列存储压缩。
其他条件（例如小表、过度分区的表或分区不足的表）质量或运行状况不佳。 但是，这些条件更好地归类为可在 步骤 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
从结果中，可以大致了解专用 SQL 池的 CCI 运行状况。 此信息不直接可操作，但可帮助你了解维护例程对实现理想状态的重要性。
| 列名称 | 说明 |
|---|---|
tables_assessed_count |
CCI 表计数 |
partitions_assessed_count |
分区计数 注意： 非分区表将计为 1。 |
actual_rowgroup_count |
行组的物理计数 |
ideal_rowgroup_count |
适合行数的计算行组数 |
uncompressed_rowgroup_count |
包含未压缩数据的行组数。 (也称为：打开行) |
actual_size_in_mb |
CCI 数据的物理大小（以 MB 为单位） |
uncompressed_size_in_mb |
未压缩数据的物理大小（以 MB 为单位） |
excess_pct |
可进一步优化的行组的百分比 |
excess_size_in_mb |
来自未优化行组的估计 MB |
步骤 2：分析详细的 CCI 信息
以下查询提供了一个详细报告，其中哪些表分区适合重新生成。 CCI 详细信息在三个指标中提供，这些指标有助于确定表/分区的优先级，这些表/分区将从维护中获益匪浅。 在 子句中 WHERE 为这些指标设置适当的阈值，然后在 子句中使用 ORDER BY 你最感兴趣的指标。 详细信息还可用于确定专用 SQL 池是否受到大量小型碎片表的影响，这可能导致 编译延迟。
注意
注释的 fnMs_GenerateIndexMaintenanceScript 函数是一个表值函数， (TVF) ，可以生成用于维护索引的通用脚本。 如果要在结果中获取维护脚本，请取消注释第 37 行和第 39 行。 在运行查询之前，请使用 生成索引维护脚本 部分中的脚本来创建函数。 运行从结果获取的维护脚本时，请务必使用适当大小 的资源类，例如 largerc 或 xlargerc。
| 列名称 | 质量特征 | 说明 |
|---|---|---|
excess_pct |
压 实 度 | 可进一步压缩的行组的百分比 |
excess_size_in_mb |
压 实 度 | 来自未优化行组的估计 MB |
OPEN_rowgroup_size_in_mb |
压缩 | 索引中未压缩数据的实际 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 相关的性能或维护问题。
| 商机标题 | 说明 | 建议 |
|---|---|---|
| 小型表 | 表包含的行数少于 1500 万行 | 请考虑将索引从 CCI 更改为：
|
| 分区机会或分区不足的表 | 计算出的理想行组计数大于 1.8 亿 (行或大约 1.88 亿行) | 实现分区策略或更改现有分区策略，将每个分区的行数减少到 1.88 亿 (每个分区大约三个行组，每个分布) |
| 过度分区的表 | 表包含的最大分区的行数少于 1500 万行 | 建议改为：
|
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
生成索引维护脚本
运行以下查询，在专用 SQL 池上创建 dbo.fnMs_GenerateIndexMaintenanceScript 函数。 此函数通过三种方式生成脚本来优化 CCI。 可以使用此函数来维护 CCI，还可以维护聚集 (行存储) 索引。
参数
| 参数名称 | 必需 | 说明 |
|---|---|---|
@object_id |
Y |
object_id 要面向的表的 |
@partition_number |
Y |
partition_number 从 sys.partitions 到目标。 如果表未分区，请指定 1。 |
输出表
| 列名称 | 说明 |
|---|---|
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
更多信息
若要更深入地了解和获取专用 SQL 池上的 CCI 的额外评估工具，请参阅：