Hinweis
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, sich anzumelden oder das Verzeichnis zu wechseln.
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, das Verzeichnis zu wechseln.
In diesem Artikel finden Sie Empfehlungen und Beispiele zum Erstellen und Aktualisieren von Abfrageoptimierungsstatistiken mithilfe der Synapse SQL-Ressourcen: dedizierter SQL-Pool und serverloser SQL-Pool.
Statistiken im dedizierten SQL-Pool
Gründe für die Verwendung von Statistiken
Je dedizierter SQL-Pool über Ihre Daten weiß, desto schneller können Abfragen ausgeführt werden. Nach dem Laden von Daten in einen dedizierten SQL-Pool gehört das Sammeln von Statistiken zu Ihren Daten zu den wichtigsten Dingen, die Sie für die Abfrageoptimierung ausführen können.
Der Abfrageoptimierer im dedizierten SQL-Pool arbeitet kostenorientiert. Die Kosten der verschiedenen Abfragepläne werden verglichen, und dann wird der Plan mit den geringsten Kosten gewählt. In den meisten Fällen wählt er den Plan aus, der am schnellsten ausgeführt wird.
Wenn der Optimierer beispielsweise schätzt, dass das Datum, nach dem Ihre Abfrage gefiltert wird, eine Zeile zurückgibt, wird ein Plan ausgewählt. Wenn es schätzt, dass das ausgewählte Datum 1 Millionen Zeilen zurückgibt, wird ein anderer Plan zurückgegeben.
Automatische Erstellung von Statistiken
Das dedizierte SQL-Poolmodul analysiert eingehende Benutzerabfragen nach fehlenden Statistiken, wenn die Datenbankoption AUTO_CREATE_STATISTICS auf ON festgelegt ist. Wenn Statistiken fehlen, erstellt der Abfrageoptimierer Statistiken für einzelne Spalten im Abfrageprädikat oder der Verknüpfungsbedingung.
Diese Funktion wird verwendet, um Kardinalitätsschätzungen für den Abfrageplan zu verbessern.
Von Bedeutung
Die automatische Erstellung von Statistiken ist zurzeit standardmäßig aktiviert.
Sie können überprüfen, ob AUTO_CREATE_STATISTICS bei Ihrem Data Warehouse konfiguriert ist, indem Sie den folgenden Befehl ausführen:
SELECT name, is_auto_create_stats_on
FROM sys.databases
Wenn Ihr Data Warehouse nicht AUTO_CREATE_STATISTICS aktiviert hat, empfehlen wir, diese Eigenschaft zu aktivieren, indem Sie den folgenden Befehl ausführen:
ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON
Diese Anweisungen lösen die automatische Erstellung von Statistiken aus:
- AUSWÄHLEN
- INSERT-SELECT
- CTAS
- Aktualisierung
- Löschen
- EXPLAIN, wenn eine Verknüpfung enthalten ist oder das Vorhandensein eines Prädikats erkannt wird.
Hinweis
Die automatische Erstellung von Statistiken erfolgt nicht für temporäre oder externe Tabellen.
Die automatische Erstellung von Statistiken erfolgt synchron. Es kann also zu einer etwas beeinträchtigten Abfrageleistung kommen, wenn Statistiken für Ihre Spalten fehlen. Die Zeit zum Erstellen von Statistiken für eine einzelne Spalte hängt von der Größe der Tabelle ab.
Um messbare Leistungseinbußen zu verhindern, sollten Sie zuerst durch Ausführen der Benchmarkworkload sicherstellen, dass Statistiken erstellt wurden, bevor Sie Profile für das System erstellen.
Hinweis
Die Erstellung von Statistiken wird in sys.dm_pdw_exec_requests in einem anderen Benutzerkontext protokolliert.
Wenn automatische Statistiken erstellt werden, nehmen sie die Form: WA_Sys<8-stellige Spalten-ID in Hex_>8-Zifferntabellen-ID in Hex<>. Sie können bereits erstellte Statistiken anzeigen, indem Sie den Befehl "DBCC SHOW_STATISTICS " ausführen:
DBCC SHOW_STATISTICS (<table_name>, <target>)
Der table_name ist der Name der Tabelle, die die anzuzeigenden Statistiken enthält, was keine externe Tabelle sein kann. „Target“ ist der Name des Zielindex, der Statistik oder der Spalte, für den bzw. für die Statistikinformationen angezeigt werden sollen.
Statistikaktualisierung
Eine bewährte Methode ist es, die Statistiken für Datenspalten im Zuge des Hinzufügens neuer Daten täglich zu aktualisieren. Jedes Mal, wenn neue Zeilen in das Data Warehouse geladen werden, werden neue Ladedaten oder Transaktionsdaten hinzugefügt. Durch diese Ergänzungen wird die Datenverteilung geändert, und die Statistiken sind nicht mehr aktuell.
Statistiken zu einer Länder- oder Regionsspalte in einer Kundentabelle müssen möglicherweise nie aktualisiert werden, da sich die Verteilung von Werten normalerweise nicht ändert. Wenn davon auszugehen ist, dass die Verteilung zwischen Kunden konstant ist, bewirkt das Hinzufügen neuer Zeilen zur Tabellenvariante keine Änderung der Datenverteilung.
Wenn Ihr Data Warehouse jedoch nur ein Land oder eine Region enthält und Sie Daten aus einem neuen Land oder einer neuen Region mitbringen, müssen Sie Statistiken über das Land oder die Regionsspalte aktualisieren.
Im Folgenden finden Sie Empfehlungen für Updates für Statistiken:
| Typ | Empfehlung |
|---|---|
| Aktualisierungshäufigkeit für Statistiken | Konservativ: Täglich Nach dem Laden oder Transformieren von Daten |
| Stichproben | Bei weniger als 1 Milliarde Zeilen Standard-Stichprobenentnahme verwenden (20 Prozent).
Bei mehr als 1 Milliarde Zeilen Stichprobenentnahme von 2 % verwenden. |
Ermittlung der letzten Statistikaktualisierung
Eine der ersten Fragen, die Sie bei der Problembehandlung einer Abfrage stellen müssen, lautet " Sind die Statistiken auf dem neuesten Stand?"
Diese Frage ist nicht eine Frage, die mit dem Alter der Daten beantwortet werden kann. Ein Statistikobjekt auf dem aktuellen Stand ist ggf. alt, falls sich die zugrunde liegenden Daten nicht wesentlich geändert haben. Wenn sich die Anzahl der Zeilen erheblich geändert hat oder eine wesentliche Änderung der Verteilung von Werten für eine Spalte auftritt, ist es an der Zeit, Statistiken zu aktualisieren.
Es ist keine dynamische Verwaltungsansicht verfügbar, um festzustellen, ob sich Daten innerhalb der Tabelle seit der letzten Aktualisierung von Statistiken geändert haben. Wenn Sie das Alter Ihrer Statistiken kennen, können Sie ein Teil des Bildes verstehen.
Mit der folgenden Abfrage können Sie ermitteln, wie lange Ihre Statistiken zuletzt in jeder Tabelle aktualisiert wurden.
Hinweis
Wenn es eine wesentliche Änderung der Verteilung von Werten für eine Spalte gibt, sollten Sie Statistiken unabhängig vom zeitpunkt der letzten Aktualisierung aktualisieren.
SELECT
sm.[name] AS [schema_name],
tb.[name] AS [table_name],
co.[name] AS [stats_column_name],
st.[name] AS [stats_name],
STATS_DATE(st.[object_id],st.[stats_id]) AS [stats_last_updated_date]
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.[user_created] = 1;
Datumsspalten in einem Data Warehouse z. B. benötigen in der Regel häufige Statistikenaktualisierungen. Jedes Mal, wenn neue Zeilen in das Data Warehouse geladen werden, werden neue Ladedaten oder Transaktionsdaten hinzugefügt. Durch diese Ergänzungen wird die Datenverteilung geändert, und die Statistiken sind nicht mehr aktuell.
Statistiken zu einer Geschlechtsspalte in einer Kundentabelle müssen möglicherweise nie aktualisiert werden. Wenn davon auszugehen ist, dass die Verteilung zwischen Kunden konstant ist, bewirkt das Hinzufügen neuer Zeilen zur Tabellenvariante keine Änderung der Datenverteilung.
Wenn Ihr Data Warehouse jedoch nur ein Geschlecht enthält und eine neue Anforderung zu mehreren Geschlechtern führt, müssen Sie statistiken über die Spalte "Geschlecht" aktualisieren.
Weitere Informationen finden Sie im Artikel "Statistik" .
Implementieren der Statistikverwaltung
Häufig ist es ratsam, den Datenladeprozess zu erweitern, um sicherzustellen, dass die Statistiken am Ende des Ladevorgangs aktualisiert werden. Das Laden von Daten ist der Zeitpunkt, zu dem Tabellen am häufigsten ihre Größe, die Verteilung der Werte oder beides ändern. Daher ist der Ladevorgang ein logischer Ansatzpunkt zum Implementieren einiger Verwaltungsprozesse.
Hier sind einige Richtlinien zur Aktualisierung von Statistiken während des Ladeprozesses angegeben:
- Stellen Sie sicher, dass jede geladene Tabelle mindestens über ein aktualisiertes Statistikobjekt verfügt. Im Rahmen der Statistikaktualisierung werden dann die Informationen zur Tabellengröße (Zeilen- und Seitenanzahl) aktualisiert.
- Konzentrieren Sie sich auf Spalten mit JOIN-, GROUP BY-, ORDER BY- und DISTINCT-Klauseln.
- Erwägen Sie, Spalten vom Typ „aufsteigender Schlüssel“, z. B. Transaktionsdaten, häufiger zu aktualisieren, da diese Werte nicht in das Statistikhistogramm einbezogen werden.
- Erwägen Sie, Spalten mit statischer Verteilung weniger häufig zu aktualisieren.
- Bedenken Sie, dass jedes statistische Objekt der Reihe nach aktualisiert wird. Es ist nicht ideal, einfach
UPDATE STATISTICS <TABLE_NAME>zu implementieren. Dies gilt besonders für breite Tabellen mit vielen Statistikobjekten.
Weitere Informationen finden Sie unter Kardinalitätsschätzung (SQL Server).
Beispiele: Erstellen von Statistiken
In diesen Beispielen wird veranschaulicht, wie Sie verschiedene Optionen zum Erstellen von Statistiken verwenden. Die Optionen, die Sie für jede Spalte verwenden, hängen von den Merkmalen Ihrer Daten und der Verwendung der Spalte in Abfragen ab.
Erstellen von Einspaltenstatistiken mit Standardoptionen
Zum Erstellen von Statistiken für eine Spalte geben Sie einen Namen für das Statistikobjekt und den Namen der Spalte an. Bei dieser Syntax werden alle Standardoptionen verwendet. Standardmäßig wird in dedizierten SQL-Pools beim Erstellen von Statistiken eine Stichprobenrate von 20 % der Tabelle verwendet.
CREATE STATISTICS [statistics_name]
ON [schema_name].[table_name]([column_name]);
Beispiel:
CREATE STATISTICS col1_stats
ON dbo.table1 (col1);
Erstellen von Einspaltenstatistiken per Untersuchung jeder Zeile
Die standardmäßige Stichprobenrate von 20 % ist in den meisten Fällen ausreichend. Sie können die Stichprobenrate aber auch anpassen. Verwenden Sie die folgende Syntax, um die gesamte Tabelle zu verwenden:
CREATE STATISTICS [statistics_name]
ON [schema_name].[table_name]([column_name])
WITH FULLSCAN;
Beispiel:
CREATE STATISTICS col1_stats
ON dbo.table1 (col1)
WITH FULLSCAN;
Erstellen von Einspaltenstatistiken durch Angeben der Stichprobengröße
Eine weitere Möglichkeit besteht darin, die Beispielgröße als Prozent anzugeben:
CREATE STATISTICS col1_stats
ON dbo.table1 (col1)
WITH SAMPLE 50 PERCENT;
Erstellen von Einspaltenstatistiken für einen Teil der Zeilen
Sie können auch Statistiken für einen Teil der Zeilen in der Tabelle erstellen, der als gefilterte Statistik bezeichnet wird.
Sie können gefilterte Statistiken beispielsweise verwenden, wenn Sie planen, eine bestimmte Partition einer großen partitionierten Tabelle abzufragen. Durch die Erstellung von Statistiken zu nur den Partitionswerten wird die Genauigkeit der Statistiken verbessert. Außerdem werden Sie eine Verbesserung der Abfrageleistung erleben.
In diesem Beispiel werden Statistiken für einen Bereich von Werten erstellt. Die Werte können leicht so definiert werden, dass sie den Werten in einer Partition entsprechen.
CREATE STATISTICS stats_col1
ON table1(col1)
WHERE col1 > '2000101' AND col1 < '20001231';
Hinweis
Damit der Abfrageoptimierer beim Auswählen des Plans für die verteilte Abfrage die Verwendung von gefilterten Statistiken berücksichtigt, muss die Abfrage in die Definition des Statistikobjekts passen. Im vorherigen Beispiel müssen für die WHERE-Klausel der Abfrage col1-Werte zwischen 2000101 und 20001231 angegeben werden.
Erstellen von Einspaltenstatistiken mit allen Optionen
Sie können die Optionen auch kombinieren. Im folgenden Beispiel wird ein Objekt vom Typ „gefilterte Statistik“ mit einer benutzerdefinierten Stichprobengröße erstellt:
CREATE STATISTICS stats_col1
ON table1 (col1)
WHERE col1 > '2000101' AND col1 < '20001231'
WITH SAMPLE 50 PERCENT;
Die gesamte Referenz finden Sie unter CREATE STATISTICS (Transact-SQL).
Erstellen von Mehrspaltenstatistiken
Verwenden Sie zum Erstellen eines mehrspaltigen Statistikobjekts die vorherigen Beispiele, aber geben Sie mehr Spalten an.
Hinweis
Das Histogramm, das zum Schätzen der Zeilenanzahl im Abfrageergebnis verwendet wird, ist nur für die erste Spalte verfügbar, die in der Definition des Statistikobjekts aufgelistet ist.
In diesem Beispiel ist das Histogramm auf product_category. Spaltenübergreifende Statistiken werden für product_category und product_sub_category berechnet:
CREATE STATISTICS stats_2cols
ON table1 (product_category, product_sub_category)
WHERE product_category > '2000101' AND product_category < '20001231'
WITH SAMPLE 50 PERCENT;
Da zwischen product_category und product_sub_category eine Korrelation besteht, kann ein mehrspaltiges Statistikobjekt nützlich sein, wenn gleichzeitig auf diese Spalten zugegriffen wird. Bei der Abfrage dieser Tabelle werden die mehrspaltigen Statistiken die Kardinalitätsschätzungen für Verknüpfungen, GROUP BY-Aggregationen, unterschiedliche Anzahlen und WHERE-Filter verbessern (solange die primäre Statistikspalte Teil des Filters ist).
Erstellen von Statistiken für alle Spalten einer Tabelle
Eine Möglichkeit zum Erstellen von Statistiken ist das Ausgeben von CREATE STATISTICS-Befehlen nach dem Erstellen der Tabelle:
CREATE TABLE dbo.table1
(
col1 int
, col2 int
, col3 int
)
WITH
(
CLUSTERED COLUMNSTORE INDEX
)
;
CREATE STATISTICS stats_col1 on dbo.table1 (col1);
CREATE STATISTICS stats_col2 on dbo.table2 (col2);
CREATE STATISTICS stats_col3 on dbo.table3 (col3);
Verwenden einer gespeicherten Prozedur zum Erstellen von Statistiken zu allen Spalten in einer Datenbank
Der SQL-Pool verfügt nicht über eine systemgespeicherte Prozedur, die in SQL Server entspricht sp_create_stats . Diese gespeicherte Prozedur erstellt für jede Spalte der Datenbank, die noch keine Statistiken enthält, ein Statistikobjekt.
Das folgende Beispiel hilft Ihnen bei den ersten Schritten mit Ihrem Datenbankentwurf. Sie können sie an Ihre Bedürfnisse anpassen:
CREATE PROCEDURE [dbo].[prc_sqldw_create_stats]
( @create_type tinyint -- 1 default, 2 Fullscan, 3 Sample
, @sample_pct tinyint
)
AS
IF @create_type IS NULL
BEGIN
SET @create_type = 1;
END;
IF @create_type NOT IN (1,2,3)
BEGIN
THROW 151000,'Invalid value for @stats_type parameter. Valid range 1 (default), 2 (fullscan) or 3 (sample).',1;
END;
IF @sample_pct IS NULL
BEGIN;
SET @sample_pct = 20;
END;
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN;
DROP TABLE #stats_ddl;
END;
CREATE TABLE #stats_ddl
WITH ( DISTRIBUTION = HASH([seq_nmbr])
, LOCATION = USER_DB
)
AS
WITH T
AS
(
SELECT t.[name] AS [table_name]
, s.[name] AS [table_schema_name]
, c.[name] AS [column_name]
, c.[column_id] AS [column_id]
, t.[object_id] AS [object_id]
, ROW_NUMBER()
OVER(ORDER BY (SELECT NULL)) AS [seq_nmbr]
FROM sys.[tables] t
JOIN sys.[schemas] s ON t.[schema_id] = s.[schema_id]
JOIN sys.[columns] c ON t.[object_id] = c.[object_id]
LEFT JOIN sys.[stats_columns] l ON l.[object_id] = c.[object_id]
AND l.[column_id] = c.[column_id]
AND l.[stats_column_id] = 1
LEFT JOIN sys.[external_tables] e ON e.[object_id] = t.[object_id]
WHERE l.[object_id] IS NULL
AND e.[object_id] IS NULL -- not an external table
)
SELECT [table_schema_name]
, [table_name]
, [column_name]
, [column_id]
, [object_id]
, [seq_nmbr]
, CASE @create_type
WHEN 1
THEN CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+')' AS VARCHAR(8000))
WHEN 2
THEN CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH FULLSCAN' AS VARCHAR(8000))
WHEN 3
THEN CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH SAMPLE '+CONVERT(varchar(4),@sample_pct)+' PERCENT' AS VARCHAR(8000))
END AS create_stat_ddl
FROM T
;
DECLARE @i INT = 1
, @t INT = (SELECT COUNT(*) FROM #stats_ddl)
, @s NVARCHAR(4000) = N''
;
WHILE @i <= @t
BEGIN
SET @s=(SELECT create_stat_ddl FROM #stats_ddl WHERE seq_nmbr = @i);
PRINT @s
EXEC sp_executesql @s
SET @i+=1;
END
DROP TABLE #stats_ddl;
Führen Sie die gespeicherte Prozedur aus, um damit Statistiken für alle Spalten in der Tabelle mit den Standardwerten zu erstellen.
EXEC [dbo].[prc_sqldw_create_stats] 1, NULL;
Rufen Sie diese Prozedur auf, um Statistiken zu allen Spalten in der Tabelle mithilfe eines Fullscans zu erstellen:
EXEC [dbo].[prc_sqldw_create_stats] 2, NULL;
Um beispielierte Statistiken für alle Spalten in der Tabelle zu erstellen, geben Sie "3" und "Beispielprozent" ein. Im folgenden Verfahren wird eine Stichprobenrate von 20 Prozent verwendet.
EXEC [dbo].[prc_sqldw_create_stats] 3, 20;
Beispiele: Statistikaktualisierung
Sie können wie folgt vorgehen, um Statistiken zu aktualisieren:
- Aktualisieren Sie ein Statistikobjekt. Geben Sie den Namen des Statistikobjekts an, das Sie aktualisieren möchten.
- Aktualisieren Sie alle Statistikobjekte einer Tabelle. Geben Sie anstelle eines bestimmten Statistikobjekts den Namen der Tabelle an.
Aktualisieren eines bestimmten Statistikobjekts
Verwenden Sie die folgende Syntax, um ein bestimmtes Statistikobjekt zu aktualisieren:
UPDATE STATISTICS [schema_name].[table_name]([stat_name]);
Beispiel:
UPDATE STATISTICS [dbo].[table1] ([stats_col1]);
Indem Sie bestimmte Statistikobjekte aktualisieren, können Sie den Zeit- und Ressourcenaufwand reduzieren, der zum Verwalten von Statistiken erforderlich ist. Diese Aktion erfordert einige Überlegungen, um die besten Statistikobjekte auszuwählen, die aktualisiert werden sollen.
Update aller Statistiken für eine Tabelle
Hier ist eine einfache Methode zum Aktualisieren aller Statistikobjekte einer Tabelle:
UPDATE STATISTICS [schema_name].[table_name];
Beispiel:
UPDATE STATISTICS dbo.table1;
Die UPDATE STATISTICS-Anweisung ist einfach zu verwenden. Denken Sie nur daran, dass alle Statistiken in der Tabelle aktualisiert werden, was mehr Arbeit verursacht, als nötig ist.
Wenn die Leistung kein Problem darstellt, ist diese Methode die einfachste und vollständigste Methode, um sicherzustellen, dass Statistiken auf dem neuesten Stand sind.
Hinweis
Beim Aktualisieren aller Statistiken für eine Tabelle führt ein dedizierter SQL-Pool eine Überprüfung durch, um die Tabelle für jedes Statistikobjekt zu probieren. Wenn die Tabelle groß ist und viele Spalten und Statistiken enthält, kann es effizienter sein, je nach Bedarf einzelne Spalten zu aktualisieren.
Eine Implementierung einer UPDATE STATISTICS Prozedur finden Sie in temporären Tabellen. Die Implementierungsmethode unterscheidet sich etwas von der obigen CREATE STATISTICS-Prozedur, aber das Ergebnis ist identisch.
Die vollständige Syntax finden Sie unter "Update statistics".
Statistikmetadaten
Es gibt mehrere Systemsichten und -funktionen, die Sie zum Suchen nach Informationen zu Statistiken verwenden können. Beispielsweise können Sie sehen, ob ein Statistikobjekt möglicherweise nicht mehr aktuell ist, indem Sie die Funktion STATS_DATE() verwenden. mit STATS_DATE() können Sie sehen, wann Statistiken zuletzt erstellt oder aktualisiert wurden.
Katalogsichten für Statistiken
Diese Systemsichten enthalten Informationen zu Statistiken:
| Katalogsicht | BESCHREIBUNG |
|---|---|
| sys.columns | Eine Zeile für jede Spalte. |
| sys.objects | Eine Zeile für jedes Objekt in der Datenbank. |
| sys.schemas | Eine Zeile für jedes Schema in der Datenbank. |
| sys.stats | Eine Zeile für jedes Statistikobjekt. |
| sys.stats_columns | Eine Zeile für jede Spalte im Statistikobjekt. Eine Verknüpfung zurück zu sys.columns. |
| sys.tables | Eine Zeile für jede Tabelle (einschließlich externer Tabellen). |
| sys.table_types | Eine Zeile für jeden Datentyp. |
Systemfunktionen für Statistiken
Diese Systemfunktionen sind nützlich für die Arbeit mit Statistiken:
| Systemfunktion | BESCHREIBUNG |
|---|---|
| STATS_DATE | Datum, an dem das Statistikobjekt zuletzt aktualisiert wurde. |
| DBCC-SHOW_STATISTICS | Zusammenfassungsebene und detaillierte Informationen zur Verteilung von Werten, wie sie vom Statistikobjekt verstanden werden. |
Kombinieren von Statistikspalten und -funktionen zu einer Sicht
Diese Ansicht enthält Spalten, die sich auf Statistiken und Ergebnisse aus der funktion STATS_DATE() beziehen.
CREATE VIEW dbo.vstats_columns
AS
SELECT
sm.[name] AS [schema_name]
, tb.[name] AS [table_name]
, st.[name] AS [stats_name]
, st.[filter_definition] AS [stats_filter_definition]
, st.[has_filter] AS [stats_is_filtered]
, STATS_DATE(st.[object_id],st.[stats_id])
AS [stats_last_updated_date]
, co.[name] AS [stats_column_name]
, ty.[name] AS [column_type]
, co.[max_length] AS [column_max_length]
, co.[precision] AS [column_precision]
, co.[scale] AS [column_scale]
, co.[is_nullable] AS [column_is_nullable]
, co.[collation_name] AS [column_collation_name]
, QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS two_part_name
, QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS three_part_name
FROM sys.objects AS ob
JOIN sys.stats AS st ON ob.[object_id] = st.[object_id]
JOIN sys.stats_columns AS sc ON st.[stats_id] = sc.[stats_id]
AND st.[object_id] = sc.[object_id]
JOIN sys.columns AS co ON sc.[column_id] = co.[column_id]
AND sc.[object_id] = co.[object_id]
JOIN sys.types AS ty ON co.[user_type_id] = ty.[user_type_id]
JOIN sys.tables AS tb ON co.[object_id] = tb.[object_id]
JOIN sys.schemas AS sm ON tb.[schema_id] = sm.[schema_id]
WHERE 1=1
AND st.[user_created] = 1
;
DBCC SHOW_STATISTICS()-Beispiele
DBCC SHOW_STATISTICS() zeigt die Daten an, die in einem Statistikobjekt gespeichert sind. Diese Daten bestehen aus drei Teilen:
- Kopfzeile
- Dichtevektor
- Histogramm
Der Header ist die Metadaten zu den Statistiken. Im Histogramm wird die Verteilung der Werte in der ersten Schlüsselspalte des Statistikobjekts angezeigt.
Der Dichtevektor misst die spaltenübergreifende Korrelation. Der dedizierte SQL-Pool berechnet Kardinalitätsschätzungen anhand der Daten im Statistikobjekt.
Anzeigen von Header, Dichte und Histogramm
In diesem einfachen Beispiel werden alle drei Teile eines Statistikobjekts angezeigt:
DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)
Beispiel:
DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1');
Anzeigen eines oder mehrerer Teile von DBCC SHOW_STATISTICS()
Wenn Sie nur bestimmte Teile anzeigen möchten, verwenden Sie die WITH-Klausel und geben an, welche Teile dies sein sollen:
DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)
WITH stat_header, histogram, density_vector
Beispiel:
DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1')
WITH histogram, density_vector
DBCC SHOW_STATISTICS()-Unterschiede
DBCC SHOW_STATISTICS() ist im Vergleich zu SQL Server im dedizierten SQL-Pool strenger implementiert:
- Nicht dokumentierte Features werden nicht unterstützt.
- Stats_stream kann nicht verwendet werden.
- Ergebnisse für bestimmte Teilmengen von Statistikdaten können nicht verknüpft werden. Beispiel: (STAT_HEADER JOIN DENSITY_VECTOR).
- NO_INFOMSGS kann nicht für die Nachrichtenunterdrückung festgelegt werden.
- Eckige Klammern um Namen von Statistiken können nicht verwendet werden.
- Spaltennamen können nicht zum Identifizieren von Statistikobjekten verwendet werden.
- Benutzerdefinierter Fehler 2767 wird nicht unterstützt.
Statistiken im serverlosen SQL-Pool
Statistiken werden pro bestimmte Spalte für ein bestimmtes Dataset (Speicherpfad) erstellt.
Hinweis
Statistiken können für LOB-Spalten nicht erstellt werden.
Gründe für die Verwendung von Statistiken
Je serverloserer SQL-Pool über Ihre Daten weiß, desto schneller kann er Abfragen dafür ausführen. Das Sammeln von Statistiken zu Ihren Daten ist eines der wichtigsten Dinge, die Sie tun können, um Ihre Abfragen zu optimieren.
Der serverlose SQL-Poolabfrageoptimierer ist ein kostenbasierter Optimierer. Die Kosten der verschiedenen Abfragepläne werden verglichen, und dann wird der Plan mit den geringsten Kosten gewählt. In den meisten Fällen wählt er den Plan aus, der am schnellsten ausgeführt wird.
Wenn der Optimierer beispielsweise schätzt, dass das Datum, nach dem Ihre Abfrage gefiltert wird, eine Zeile zurückgibt, wählt er einen Plan aus. Wenn es schätzt, dass das ausgewählte Datum 1 Millionen Zeilen zurückgibt, wird ein anderer Plan ausgewählt.
Automatische Erstellung von Statistiken
Der serverlose SQL-Pool analysiert eingehende Benutzerabfragen auf fehlende Statistiken. Wenn Statistiken fehlen, erstellt der Abfrageoptimierer Statistiken für einzelne Spalten im Abfrageprädikat oder der Verknüpfungsbedingung, um Kardinalitätsschätzungen für den Abfrageplan zu verbessern.
Die SELECT-Anweisung löst die automatische Erstellung von Statistiken aus.
Hinweis
Für die automatische Erstellung von Statistiken wird die Stichprobenentnahme verwendet, und in den meisten Fällen beträgt der Stichprobenprozentsatz weniger als 100 %. Dieser Flow ist für jedes Dateiformat identisch. Beachten Sie, dass beim Lesen von CSV mit der Parserversion 1.0 die Stichprobenentnahme nicht unterstützt wird und die automatische Erstellung von Statistiken nicht erfolgt, wenn der Stichprobenprozentsatz weniger als 100 % beträgt. Für kleine Tabellen mit geschätzter niedriger Kardinalität (Anzahl der Zeilen) wird die automatische Erstellung von Statistiken mit einem Stichprobenprozentsatz von 100 % ausgelöst. Das bedeutet im Grunde, dass fullscan ausgelöst wird und automatische Statistiken auch für CSV mit Parser Version 1.0 erstellt werden.
Die automatische Erstellung von Statistiken erfolgt synchron, sodass die Abfrageleistung geringfügig beeinträchtigt wird, wenn Statistiken für Ihre Spalten fehlen. Die Zeit zum Erstellen von Statistiken für eine einzelne Spalte hängt von der Größe der Zieldateien ab.
Manuelle Erstellung von Statistiken
Mit serverlosen SQL-Pool können Sie Statistiken manuell erstellen. Falls Sie parser Version 1.0 mit CSV verwenden, müssen Sie wahrscheinlich Statistiken manuell erstellen, da diese Parserversion das Sampling nicht unterstützt. Die automatische Erstellung von Statistiken bei parser Version 1.0 erfolgt nicht, es sei denn, der Samplingprozentwert ist 100%.
In den folgenden Beispielen finden Sie Anweisungen zum manuellen Erstellen von Statistiken.
Statistikaktualisierung
Änderungen an Daten in Dateien, Löschen und Hinzufügen von Dateien führen zu Datenverteilungsänderungen und machen Statistiken veraltet. In diesem Fall müssen Statistiken aktualisiert werden.
Der Serverlose SQL-Pool erstellt automatisch Statistiken für OPENROWSET-Spalten neu, wenn Daten erheblich geändert werden. Jedes Mal, wenn Statistiken automatisch erstellt werden, wird auch der aktuelle Zustand des Datasets gespeichert: Dateipfade, Größen, Datum der letzten Änderung.
Wenn Statistiken veraltet sind, werden neue erstellt. Der Algorithmus durchläuft die Daten und vergleicht sie mit dem aktuellen Zustand des Datasets. Wenn die Größe der Änderungen größer als der spezifische Schwellenwert ist, werden alte Statistiken gelöscht und über das neue Dataset neu erstellt.
Manuelle Statistiken werden nie veraltet deklariert.
Hinweis
Für die automatische Neuerstellung von Statistiken wird die Stichprobenmethode verwendet, und in den meisten Fällen beträgt der Stichprobenanteil weniger als 100%. Dieser Flow ist für jedes Dateiformat identisch. Beachten Sie, dass beim Lesen von CSV mit Parserversion 1.0 die Stichprobenentnahme nicht unterstützt wird und die automatische Neuerstellung von Statistiken nicht erfolgt, wenn der Stichprobenprozentsatz weniger als 100 % beträgt. In diesem Fall müssen Sie Statistiken manuell ablegen und neu erstellen. Schauen Sie sich die Beispiele unten an, um zu erfahren, wie Sie Statistiken löschen und erstellen können. Bei kleinen Tabellen mit geschätzter niedriger Kardinalität (Anzahl der Zeilen) wird die automatische Wiederherstellung von Statistiken mit dem Sampling-Prozentsatz von 100%ausgelöst. Das bedeutet im Grunde, dass fullscan ausgelöst wird und automatische Statistiken auch für CSV mit Parser Version 1.0 erstellt werden.
Eine der ersten Fragen, die Sie bei der Problembehandlung einer Abfrage stellen müssen, lautet " Sind die Statistiken auf dem neuesten Stand?"
Wenn sich die Anzahl von Zeilen deutlich geändert hat oder es eine wesentliche Änderung bei der Verteilung der Werte für eine Spalte gibt, ist der Zeitpunkt gekommen, die Statistiken zu aktualisieren.
Hinweis
Wenn es eine wesentliche Änderung der Verteilung von Werten für eine Spalte gibt, sollten Sie Statistiken unabhängig vom zeitpunkt der letzten Aktualisierung aktualisieren.
Implementieren der Statistikverwaltung
Möglicherweise möchten Sie Ihre Datenpipeline erweitern, um sicherzustellen, dass Statistiken aktualisiert werden, wenn Daten durch Ergänzung, Löschung oder Änderung von Dateien erheblich geändert werden.
Im Folgenden finden Sie einige Richtlinien zur Aktualisierung von Statistiken:
- Stellen Sie sicher, dass das Dataset mindestens ein Statistikobjekt aktualisiert hat. Im Rahmen der Statistikaktualisierung werden dann die Informationen zur Größe (Zeilen- und Seitenanzahl) aktualisiert.
- Konzentrieren Sie sich auf Spalten, die an WHERE-, JOIN-, GROUP BY-, ORDER BY- und DISTINCT-Klauseln teilnehmen.
- Aktualisieren Sie die Spalten "aufsteigender Schlüssel", z. B. Transaktionsdaten, häufiger, da diese Werte nicht in das Statistik histogramm aufgenommen werden.
- Aktualisieren Sie statische Verteilungsspalten weniger häufig.
Weitere Informationen finden Sie unter Kardinalitätsschätzung (SQL Server).
Beispiele: Erstellen von Statistiken für Spalte im OPENROWSET-Pfad
Die folgenden Beispiele zeigen, wie Sie verschiedene Optionen zum Erstellen von Statistiken in Azure Synapse serverlosen SQL-Pools verwenden. Die Optionen, die Sie für jede Spalte verwenden, hängen von den Merkmalen Ihrer Daten und der Verwendung der Spalte in Abfragen ab. Weitere Informationen zu den in diesen Beispielen verwendeten gespeicherten Prozeduren finden Sie unter sys.sp_create_openrowset_statistics und sys.sp_drop_openrowset_statistics, die für nur serverlose SQL-Pools gelten.
Hinweis
Sie können im Moment nur Statistiken mit einer Spalte erstellen.
Die folgenden Berechtigungen sind zum Ausführen von sp_create_openrowset_statistics und sp_drop_openrowset_statistics erforderlich: ADMINISTER BULK OPERATIONS oder ADMINISTER DATABASE BULK OPERATIONS.
Die folgende gespeicherte Prozedur wird zum Erstellen von Statistiken verwendet:
sys.sp_create_openrowset_statistics [ @stmt = ] N'statement_text'
Argumente: [ @stmt = ] N'statement_text' – Gibt eine Transact-SQL Anweisung an, die Spaltenwerte zurückgibt, die für Statistiken verwendet werden sollen. Sie können TABLESAMPLE verwenden, um Beispiele für zu verwendende Daten anzugeben. Wenn TABLESAMPLE nicht angegeben ist, wird FULLSCAN verwendet.
<tablesample_clause> ::= TABLESAMPLE ( sample_number PERCENT )
Hinweis
Das CSV-Sampling funktioniert nicht, wenn Sie die Parser-Version 1.0 verwenden; nur FULLSCAN wird für CSV mit der Parser-Version 1.0 unterstützt.
Erstellen von Einspaltenstatistiken per Untersuchung jeder Zeile
Um Statistiken für eine Spalte zu erstellen, geben Sie eine Abfrage an, die die Spalte zurückgibt, für die Sie Statistiken benötigen.
Wenn Sie beim manuellen Erstellen von Statistiken nichts anderes angeben, verwendet der serverlose SQL-Pool bei der Erstellung von Statistiken standardmäßig 100 % der im Dataset bereitgestellten Daten.
Um beispielsweise Statistiken mit Standardoptionen (FULLSCAN) für eine Grundgesamtheitsspalte des Datasets basierend auf der us_population.csv Datei zu erstellen:
EXEC sys.sp_create_openrowset_statistics N'SELECT
population
FROM OPENROWSET(
BULK ''https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/raw_us_population_county/us_population.csv'',
FORMAT = ''CSV'',
PARSER_VERSION = ''2.0'',
HEADER_ROW = TRUE)
AS [r]'
Erstellen von Einspaltenstatistiken durch Angeben der Stichprobengröße
Sie können die Beispielgröße als Prozent angeben:
/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO
CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = ''
GO
*/
EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
FORMAT = ''PARQUET''
) AS [nyc]
TABLESAMPLE(5 PERCENT)
'
Beispiele: Statistikaktualisierung
Zum Aktualisieren von Statistiken müssen Sie Statistiken löschen und erstellen. Weitere Informationen finden Sie unter sys.sp_create_openrowset_statistics und sys.sp_drop_openrowset_statistics.
Die gespeicherte Prozedur sys.sp_drop_openrowset_statistics wird zum Löschen von Statistiken verwendet:
sys.sp_drop_openrowset_statistics [ @stmt = ] N'statement_text'
Hinweis
Die folgenden Berechtigungen sind zum Ausführen von sp_create_openrowset_statistics und sp_drop_openrowset_statistics erforderlich: ADMINISTER BULK OPERATIONS oder ADMINISTER DATABASE BULK OPERATIONS.
Argumente: [ @stmt = ] N'statement_text' – Gibt die gleiche Transact-SQL Anweisung an, die beim Erstellen der Statistiken verwendet wurde.
Um die Statistiken für die Jahresspalte im Dataset zu aktualisieren, die auf der population.csv Datei basiert, müssen Sie Statistiken ablegen und erstellen:
EXEC sys.sp_drop_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
FORMAT = ''PARQUET''
) AS [nyc]
TABLESAMPLE(5 PERCENT)
'
GO
/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO
CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = ''
GO
*/
EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
FORMAT = ''PARQUET''
) AS [nyc]
TABLESAMPLE(5 PERCENT)
'
Beispiele: Erstellen von Statistiken für externe Tabellenspalte
In den folgenden Beispielen wird gezeigt, wie Sie verschiedene Optionen zum Erstellen von Statistiken verwenden. Die Optionen, die Sie für jede Spalte verwenden, hängen von den Merkmalen Ihrer Daten und der Verwendung der Spalte in Abfragen ab.
Hinweis
Sie können im Moment nur Statistiken mit einer Spalte erstellen.
Zum Erstellen von Statistiken für eine Spalte geben Sie einen Namen für das Statistikobjekt und den Namen der Spalte an.
CREATE STATISTICS statistics_name
ON { external_table } ( column )
WITH
{ FULLSCAN
| [ SAMPLE number PERCENT ] }
, { NORECOMPUTE }
Argumente: external_table Gibt eine externe Tabelle an, in der Statistiken erstellt werden sollen.
FULLSCAN berechnet die Statistiken, indem alle Zeilen überprüft werden. FULLSCAN und SAMPLE 100 PERCENT führen zu gleichen Ergebnissen. FULLSCAN kann nicht in Verbindung mit der Option SAMPLE verwendet werden.
SAMPLE Zahl PERCENT – Gibt den ungefähren Prozentsatz oder die ungefähre Anzahl von Zeilen in der Tabelle oder indizierten Sicht an, die vom Abfrageoptimierer beim Erstellen von Statistiken verwendet werden sollen. Die Zahl kann zwischen 0 und 100 sein.
SAMPLE kann nicht in Verbindung mit der Option FULLSCAN verwendet werden.
Hinweis
Das CSV-Sampling funktioniert nicht, wenn Sie die Parser-Version 1.0 verwenden; nur FULLSCAN wird für CSV mit der Parser-Version 1.0 unterstützt.
Erstellen von Einspaltenstatistiken per Untersuchung jeder Zeile
CREATE STATISTICS sState
on census_external_table (STATENAME)
WITH FULLSCAN, NORECOMPUTE
Erstellen von Einspaltenstatistiken durch Angeben der Stichprobengröße
-- following sample creates statistics with sampling 5%
CREATE STATISTICS sState
on census_external_table (STATENAME)
WITH SAMPLE 5 percent, NORECOMPUTE
Beispiele: Statistikaktualisierung
Zum Aktualisieren von Statistiken müssen Sie Statistiken löschen und erstellen. Löschen Sie zunächst die Statistik:
DROP STATISTICS census_external_table.sState
Und erstellen Sie Statistiken:
CREATE STATISTICS sState
on census_external_table (STATENAME)
WITH FULLSCAN, NORECOMPUTE
Statistikmetadaten
Es gibt mehrere Systemsichten und -funktionen, die Sie zum Suchen nach Informationen zu Statistiken verwenden können. Beispielsweise können Sie sehen, ob ein Statistikobjekt möglicherweise nicht mehr aktuell ist, indem Sie die Funktion STATS_DATE() verwenden. mit STATS_DATE() können Sie sehen, wann Statistiken zuletzt erstellt oder aktualisiert wurden.
Hinweis
Statistikmetadaten sind nur für externe Tabellenspalten verfügbar. Statistikmetadaten sind für OPENROWSET-Spalten nicht verfügbar.
Katalogsichten für Statistiken
Diese Systemsichten enthalten Informationen zu Statistiken:
| Katalogsicht | BESCHREIBUNG |
|---|---|
| sys.columns | Eine Zeile für jede Spalte. |
| sys.objects | Eine Zeile für jedes Objekt in der Datenbank. |
| sys.schemas | Eine Zeile für jedes Schema in der Datenbank. |
| sys.stats | Eine Zeile für jedes Statistikobjekt. |
| sys.stats_columns | Eine Zeile für jede Spalte im Statistikobjekt. Eine Verknüpfung zurück zu sys.columns. |
| sys.tables | Eine Zeile für jede Tabelle (einschließlich externer Tabellen). |
| sys.table_types | Eine Zeile für jeden Datentyp. |
Systemfunktionen für Statistiken
Diese Systemfunktionen sind nützlich für die Arbeit mit Statistiken:
| Systemfunktion | BESCHREIBUNG |
|---|---|
| STATS_DATE | Datum, an dem das Statistikobjekt zuletzt aktualisiert wurde. |
Kombinieren von Statistikspalten und -funktionen zu einer Sicht
Diese Ansicht enthält Spalten, die sich auf Statistiken und Ergebnisse aus der funktion STATS_DATE() beziehen.
CREATE VIEW dbo.vstats_columns
AS
SELECT
sm.[name] AS [schema_name]
, tb.[name] AS [table_name]
, st.[name] AS [stats_name]
, st.[filter_definition] AS [stats_filter_definition]
, st.[has_filter] AS [stats_is_filtered]
, STATS_DATE(st.[object_id],st.[stats_id])
AS [stats_last_updated_date]
, co.[name] AS [stats_column_name]
, ty.[name] AS [column_type]
, co.[max_length] AS [column_max_length]
, co.[precision] AS [column_precision]
, co.[scale] AS [column_scale]
, co.[is_nullable] AS [column_is_nullable]
, co.[collation_name] AS [column_collation_name]
, QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS two_part_name
, QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS three_part_name
FROM sys.objects AS ob
JOIN sys.stats AS st ON ob.[object_id] = st.[object_id]
JOIN sys.stats_columns AS sc ON st.[stats_id] = sc.[stats_id]
AND st.[object_id] = sc.[object_id]
JOIN sys.columns AS co ON sc.[column_id] = co.[column_id]
AND sc.[object_id] = co.[object_id]
JOIN sys.types AS ty ON co.[user_type_id] = ty.[user_type_id]
JOIN sys.tables AS tb ON co.[object_id] = tb.[object_id]
JOIN sys.schemas AS sm ON tb.[schema_id] = sm.[schema_id]
WHERE st.[user_created] = 1
;
Nächste Schritte
Informationen zur weiteren Verbesserung der Abfrageleistung für dedizierten SQL-Pool finden Sie unter Überwachen Ihrer Workload und bewährter Methoden für dedizierten SQL-Pool.
Informationen zur weiteren Verbesserung der Abfrageleistung für serverlose SQL-Pools finden Sie unter Bewährte Methoden für serverlose SQL-Pool.