Condividi tramite

Controllare l'accuratezza delle statistiche in un pool SQL dedicato

Si applica a: Azure Synapse Analytics

Le statistiche aggiornate sono fondamentali per la generazione di un piano di esecuzione ottimale. Esistono due prospettive diverse da valutare, in quanto riguarda la determinazione dell'accuratezza delle statistiche:

Passaggio 1: Verificare l'accuratezza del conteggio delle righe del nodo di controllo

Nel pool SQL dedicato, il motore primario per la creazione di piani di query distribuiti deve essere mantenuto aggiornato sul numero di righe nei nodi di calcolo. Eseguire la query seguente per identificare le tabelle con differenze tra i conteggi delle righe:

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

Passaggio 2: Assicurarsi che le statistiche siano aggiornate

L'aggiornamento dei dati può influire significativamente sugli istogrammi delle statistiche usati per generare piani di esecuzione efficaci. Eseguire la query seguente per determinare se la data dell'ultimo aggiornamento delle statistiche è in linea con i modelli di modifica della tabella:

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

Passaggio 3: Aggiornare le statistiche sulle tabelle identificate

Dopo aver identificato le tabelle candidate nei passaggi precedenti, eseguire le istruzioni generate nella update_stats_stmt colonna delle query per aggiornare le statistiche.

Note

Non è consigliabile aggiornare le singole statistiche, anche quando viene creato l'utente. Eseguendo UPDATE STATISTICS senza specificare un nome di statistiche, tutte le statistiche associate alla tabella, nonché il conteggio delle righe del nodo di controllo, verranno aggiornate. È possibile considerare l'override della percentuale di analisi predefinita usando WITH FULLSCAN o WITH SAMPLE <SamplePercent> PERCENT per ottenere l'accuratezza appropriata per le tabelle di grandi dimensioni. Per la sintassi completa, vedereUPDATE STATISTICS (Transact-SQL).

Ad esempio:

UPDATE STATISTICS [dbo].[MyAwesomeTable];

Dopo l'aggiornamento delle statistiche, eseguire nuovamente la query del problema per determinare se gli aggiornamenti delle statistiche hanno migliorato la durata dell'esecuzione.

Altre risorse per la manutenzione delle statistiche

  • Last updated on