Comprobación de la precisión de las estadísticas en un grupo de SQL dedicado
Se aplica a: Azure Synapse Analytics
Las estadísticas actualizadas son críticas para la generación de un plan de ejecución óptimo. Hay dos perspectivas diferentes que debe evaluar, ya que hacen referencia a determinar la precisión de las estadísticas:
Paso 1: Comprobar la precisión del recuento de filas del nodo de control
En el grupo de SQL dedicado, el motor principal para crear planes de consulta distribuidos debe mantenerse actualizado sobre el número de filas en los nodos de proceso. Ejecute la consulta siguiente para identificar las tablas que tienen diferencias entre los recuentos de filas:
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
Paso 2: Asegurarse de que las estadísticas estén actualizadas
La actualización de datos puede afectar significativamente a los histogramas de estadísticas usados para generar planes de ejecución efectivos. Ejecute la consulta siguiente para determinar si la fecha de última actualización de las estadísticas se alinea con los patrones de modificación de la tabla:
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
Paso 3: Actualizar las estadísticas sobre las tablas identificadas
Después de identificar las tablas candidatas en los pasos anteriores, ejecute las instrucciones generadas en la update_stats_stmt columna de las consultas para actualizar las estadísticas.
Nota:
No se recomienda actualizar estadísticas individuales, incluso cuando se crea el usuario. Al ejecutar UPDATE STATISTICS sin especificar un nombre de estadística, se actualizarán todas las estadísticas asociadas a la tabla, así como el número de filas del nodo de control. Puede considerar la posibilidad de invalidar el porcentaje de examen predeterminado mediante WITH FULLSCAN o WITH SAMPLE <SamplePercent> PERCENT para lograr una precisión adecuada para tablas grandes. Consulte UPDATE STATISTICS (Transact-SQL) para conocer la sintaxis completa.
Por ejemplo:
UPDATE STATISTICS [dbo].[MyAwesomeTable];
Después de actualizar las estadísticas, vuelva a ejecutar la consulta con problemas para determinar si las actualizaciones de las estadísticas han mejorado la duración de la ejecución.