Verificar a precisão das estatísticas em um Pool de SQL dedicado
Aplica-se ao: Azure Synapse Analytics
As estatísticas atualizadas são críticas para a geração de um plano de execução ideal. Você deve avaliar duas perspectivas diferentes no que diz respeito à determinação da precisão das estatísticas:
Etapa 1: Verificar a precisão da contagem de linhas do nó de controle
No pool de SQL dedicado, o mecanismo principal para criar planos de consulta distribuídos precisa ser mantido atualizado sobre o número de linhas nos nós de computação. Execute a seguinte consulta para identificar tabelas que têm disparidades entre as contagens de linhas:
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
Etapa 2: Verifique se as estatísticas estão atualizadas
A atualização de dados pode afetar significativamente os histogramas estatísticos usados para gerar planos de execução eficazes. Execute a seguinte consulta para determinar se a data da última atualização de suas estatísticas está alinhada com os padrões de modificação da tabela:
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
Etapa 3: atualizar estatísticas em tabelas identificadas
Depois de identificar as tabelas candidatas nas etapas anteriores, execute as instruções geradas na update_stats_stmt coluna das consultas para atualizar as estatísticas.
Observação
Não recomendamos atualizar estatísticas individuais, mesmo quando criadas pelo usuário. Ao executar UPDATE STATISTICS sem especificar um nome de estatística, serão atualizadas todas as estatísticas associadas à tabela, bem como a contagem de linhas do nó de controle. Você pode considerar substituir a porcentagem de verificação padrão usando WITH FULLSCAN ou WITH SAMPLE <SamplePercent> PERCENT para obter a precisão apropriada para tabelas grandes. Confira UPDATE STATISTICS (Transact-SQL) para obter a sintaxe completa.
Por exemplo:
UPDATE STATISTICS [dbo].[MyAwesomeTable];
Depois de atualizar as estatísticas, execute novamente a consulta de problema para determinar se as atualizações de estatísticas melhoraram a duração da execução.