전용 SQL 풀에서 클러스터형 columnstore 인덱스 상태 평가 및 수정
적용 대상: Azure Synapse Analytics
이 문서에서는 CCI(클러스터형 columnstore 인덱스) 상태를 평가하는 약간 다른 방법을 소개합니다. 다음 섹션의 단계를 따르거나 Azure Data Studio를 통해 Notebook의 단계를 실행합니다.
참고 항목
이 Notebook을 열기 전에 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
결과에서 전용 SQL 풀의 CCI 상태에 대한 개요를 볼 수 있습니다. 이 정보는 직접 실행 가능하지는 않지만 이상적인 상태를 달성하기 위한 유지 관리 루틴의 중요성을 이해하는 데 도움이 됩니다.
| 열 이름 | Description |
|---|---|
tables_assessed_count |
CCI 테이블 수 |
partitions_assessed_count |
파티션 수 참고: 분할되지 않은 테이블은 1로 계수됩니다. |
actual_rowgroup_count |
행 그룹의 실제 개수 |
ideal_rowgroup_count |
행 수에 적합한 계산된 행 그룹 수 |
uncompressed_rowgroup_count |
압축되지 않은 데이터를 포함하는 행 그룹의 수입니다. (다른 명칭: OPEN 행) |
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와 같은 적절한 크기의 리소스 클래스를 사용해야 합니다.
| 열 이름 | 품질 특성 | Description |
|---|---|---|
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와 관련된 성능 또는 유지 관리 이슈를 일으킬 수 있는 잠재적인 기회를 식별하는 데 도움이 될 수 있습니다.
| 기회 제목 | Description | 권장 사항 |
|---|---|---|
| 작은 테이블 | 15M 미만의 행이 포함된 테이블 | 인덱스 변경은 CCI에서 다음으로 변경하는 것이 좋습니다.
|
| 분할 기회 또는 분할되지 않은 테이블 | 계산된 이상적인 행 그룹 수가 180M(또는 ~188M 행)보다 큽니다. | 파티션당 행 수를 188M 미만으로 줄이기 위해 분할 전략을 구현하거나 기존 분할 전략을 변경합니다(배포당 파티션당 약 3개의 행 그룹). |
| 과대 분할된 테이블 | 가장 큰 파티션에 15M 미만의 행이 포함된 테이블 | 고려할 사항은 다음과 같습니다.
|
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뿐만 아니라 클러스터형(rowstore) 인덱스도 유지할 수 있습니다.
매개 변수
| 매개 변수 이름 | Required | 설명 |
|---|---|---|
@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에 대한 추가 평가 도구를 얻으려면 다음을 참조하세요.