Überprüfen der Genauigkeit der Statistiken in einem dedizierten SQL Pool
Gilt für: Azure Synapse Analytics
Aktualisierte Statistiken sind entscheidend für die Generierung eines optimalen Ausführungsplans. Es gibt zwei unterschiedliche Perspektiven, die Sie bei der Bestimmung der Genauigkeit von Statistiken auswerten sollten:
Schritt 1: Überprüfen der Genauigkeit der Zeilenanzahl auf Steuerungsknoten
Im dedizierten SQL-Pool muss das primäre Modul zum Erstellen verteilter Abfragepläne aktualisiert werden, um die Anzahl der Zeilen auf den Computeknoten zu aktualisieren. Führen Sie die folgende Abfrage aus, um Tabellen zu identifizieren, die Abweichungen zwischen den Zeilenanzahlen aufweisen:
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
Schritt 2: Sicherstellen, dass die Statistiken auf dem neuesten Stand sind
Das Aktualisieren von Daten kann sich erheblich auf die Statistik histogramme auswirken, die zum Generieren effektiver Ausführungspläne verwendet werden. Führen Sie die folgende Abfrage aus, um zu ermitteln, ob das Datum der letzten Aktualisierung Ihrer Statistiken mit den Änderungsmustern der Tabelle übereinstimmt:
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
Schritt 3: Aktualisieren von Statistiken in identifizierten Tabellen
Führen Sie nach dem Identifizieren von Kandidatentabellen in den vorherigen Schritten die in der update_stats_stmt Spalte der Abfragen generierten Anweisungen aus, um die Statistiken zu aktualisieren.
Notiz
Es wird nicht empfohlen, einzelne Statistiken zu aktualisieren, auch wenn der Benutzer erstellt wurde. Durch Ausführen von UPDATE STATISTICS ohne Angabe eines Statistiknamens werden alle der Tabelle zugeordneten Statistiken sowie die Zeilenanzahl auf dem Steuerungsknoten aktualisiert. Sie können erwägen, den Standardmäßigen Scanprozentsatz zu überschreiben, indem WITH FULLSCAN Sie die richtige Genauigkeit für große Tabellen verwenden oder WITH SAMPLE <SamplePercent> PERCENT erzielen. Die vollständige Syntax finden Sie unter UPDATE STATISTICS (Transact-SQL).
Zum Beispiel:
UPDATE STATISTICS [dbo].[MyAwesomeTable];
Führen Sie nach dem Aktualisieren der Statistiken die Problemabfrage erneut aus, um festzustellen, ob die Ausführungsdauer durch die Aktualisierung der Statistiken verbessert wurde.