전용 SQL 풀에서 통계 정확도 확인
적용 대상: Azure Synapse Analytics
업데이트된 통계는 최적의 실행 계획을 생성하는 데 중요합니다. 통계 정확도를 확인할 때 사용하는 두 가지 평가 기준은 다음과 같습니다.
1단계: 제어 노드 행 개수 정확도 확인
전용 SQL 풀에서 분산 쿼리 계획을 만들기 위한 기본 엔진은 컴퓨팅 노드의 행 수에 대해 계속 업데이트되어야 합니다. 다음 쿼리를 실행하여 행 개수 간에 차이가 있는 테이블을 식별합니다.
SELECT objIdsWithStats.[object_id]
,actualRowCounts.[schema]
,actualRowCounts.logical_table_name
,statsRowCounts.stats_row_count
,actualRowCounts.actual_row_count
,row_count_difference = CASE
WHEN actualRowCounts.actual_row_count >= statsRowCounts.stats_row_count
THEN actualRowCounts.actual_row_count - statsRowCounts.stats_row_count
ELSE statsRowCounts.stats_row_count - actualRowCounts.actual_row_count
END
,percent_deviation_from_actual = CASE
WHEN actualRowCounts.actual_row_count = 0
THEN statsRowCounts.stats_row_count
WHEN statsRowCounts.stats_row_count = 0
THEN actualRowCounts.actual_row_count
WHEN actualRowCounts.actual_row_count >= statsRowCounts.stats_row_count
THEN CONVERT(NUMERIC(18, 0), CONVERT(NUMERIC(18, 2), (actualRowCounts.actual_row_count - statsRowCounts.stats_row_count)) / CONVERT(NUMERIC(18, 2), actualRowCounts.actual_row_count) * 100)
ELSE CONVERT(NUMERIC(18, 0), CONVERT(NUMERIC(18, 2), (statsRowCounts.stats_row_count - actualRowCounts.actual_row_count)) / CONVERT(NUMERIC(18, 2), actualRowCounts.actual_row_count) * 100)
END
,'UPDATE STATISTICS ' + quotename(actualRowCounts.[schema]) + '.' + quotename(actualRowCounts.logical_table_name) + ';' as update_stats_stmt
FROM (
SELECT DISTINCT object_id
FROM sys.stats
WHERE stats_id > 1
) objIdsWithStats
LEFT JOIN (
SELECT object_id
,sum(rows) AS stats_row_count
FROM sys.partitions
GROUP BY object_id
) statsRowCounts ON objIdsWithStats.object_id = statsRowCounts.object_id
LEFT JOIN (
SELECT sm.name [schema]
,tb.name logical_table_name
,tb.object_id object_id
,SUM(rg.row_count) actual_row_count
FROM sys.schemas sm
INNER JOIN sys.tables tb ON sm.schema_id = tb.schema_id
INNER JOIN sys.pdw_table_mappings mp ON tb.object_id = mp.object_id
INNER JOIN sys.pdw_nodes_tables nt ON nt.name = mp.physical_name
INNER JOIN sys.dm_pdw_nodes_db_partition_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
INNER JOIN sys.indexes ind on tb.object_id = ind.object_id
WHERE rg.index_id < 2 -- In case this condition removed the number of rows will gets duplicated based on the number of index.
AND ind.type_desc IN ('CLUSTERED COLUMNSTORE', 'HEAP') -- Switch between the CCI (Column store) and HEAP, You should at least keep one value or else the total number of rows will gets duplicated based on the number of indexes.
GROUP BY sm.name
,tb.name
,tb.object_id
) actualRowCounts ON objIdsWithStats.object_id = actualRowCounts.object_id
2단계: 통계가 최신 상태인지 확인
데이터를 업데이트하면 효과적인 실행 계획을 생성하는 데 사용되는 통계 히스토그램에 큰 영향을 줄 수 있습니다. 다음 쿼리를 실행하여 통계의 마지막 업데이트 날짜가 테이블의 수정 패턴과 일치하는지 확인합니다.
SELECT ob.[object_id],max(sm.[name]) AS [schema_name]
,max(tb.[name]) AS [table_name]
,st.[stats_id]
,max(st.[name]) AS [stats_name]
,string_agg(co.[name], ',') AS [stats_column_names]
,STATS_DATE(ob.[object_id], st.[stats_id]) AS [stats_last_updated_date]
,'UPDATE STATISTICS ' + quotename(max(sm.[name])) + '.' + quotename(max(tb.[name])) + ';' as [update_stats_stmt]
FROM sys.objects ob
JOIN sys.stats st ON ob.[object_id] = st.[object_id]
JOIN sys.stats_columns sc ON st.[stats_id] = sc.[stats_id]
AND st.[object_id] = sc.[object_id]
JOIN sys.columns co ON sc.[column_id] = co.[column_id]
AND sc.[object_id] = co.[object_id]
JOIN sys.types ty ON co.[user_type_id] = ty.[user_type_id]
JOIN sys.tables tb ON co.[object_id] = tb.[object_id]
JOIN sys.schemas sm ON tb.[schema_id] = sm.[schema_id]
WHERE st.[stats_id] > 1
GROUP BY ob.[object_id], st.[stats_id]
ORDER BY stats_last_updated_date
3단계: 식별된 테이블에 대한 통계 업데이트
이전 단계에서 후보 테이블을 식별한 후 쿼리 열에서 update_stats_stmt 생성된 문을 실행하여 통계를 업데이트합니다.
참고 항목
사용자가 만든 경우에도 개별 통계를 업데이트하지 않는 것이 좋습니다. 통계 이름을 지정하지 않고 UPDATE STATISTICS를 실행하면 제어 노드 행 개수뿐만 아니라 테이블과 관련된 모든 통계도 업데이트됩니다. 큰 테이블에 적절한 정확도를 사용 WITH FULLSCAN 하거나 WITH SAMPLE <SamplePercent> PERCENT 달성하여 기본 검사 비율을 재정의하는 것이 좋습니다. 전체 구문은 UPDATE STATISTICS(Transact-SQL)를 참조하세요.
예시:
UPDATE STATISTICS [dbo].[MyAwesomeTable];
통계를 업데이트한 후 문제 쿼리를 다시 실행하여 통계 업데이트 후 실행 기간이 개선되었는지 확인합니다.