CREATE STATISTICS (Transact-SQL)

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics SQL Analytics-Endpunkt in Microsoft Fabric Warehouse in Microsoft Fabric

Erstellt Abfrageoptimierungsstatistiken in einer oder mehreren Spalten einer Tabelle, einer indizierten Sicht oder einer externen Tabelle. Für die meisten Abfragen generiert der Abfrageoptimierer bereits die erforderlichen Statistiken für einen qualitativ hochwertigen Abfrageplan; In einigen Fällen müssen Sie zusätzliche Statistiken erstellen oder CREATE STATISTICS den Abfrageentwurf ändern, um die Abfrageleistung zu verbessern.

Weitere Informationen finden Sie unter Statistiken.

Transact-SQL-Syntaxkonventionen

Syntax

Syntax für SQL Server, Azure SQL-Datenbank und Azure SQL verwaltete Instanz.

-- Create statistics on an external table
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ , ...n ] )
    [ WITH FULLSCAN ] ;

-- Create statistics on a regular table or indexed view
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ , ...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH
        [ FULLSCAN
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | SAMPLE number { PERCENT | ROWS }
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | <update_stats_stream_option> [ , ...n ]
        [ [ , ] NORECOMPUTE ]
        [ [ , ] INCREMENTAL = { ON | OFF } ]
        [ [ , ] MAXDOP = max_degree_of_parallelism ]
        [ [ , ] AUTO_DROP = { ON | OFF } ]
        ]
    ];

<filter_predicate> ::=
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant , ...)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

<update_stats_stream_option> ::=
    [ STATS_STREAM = stats_stream ]
    [ ROWCOUNT = numeric_constant ]
    [ PAGECOUNT = numeric_contant ]

Syntax für Azure Synapse Analytics and Analytics Platform System (PDW).

CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name  [ , ...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[ ; ]

<filter_predicate> ::=
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant , ...)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

Syntax für Microsoft Fabric.

CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name )
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[ ; ]

Argumente

statistics_name

Der Name der zu erstellenden Statistiken.

table_or_indexed_view_name

Der Name der Tabelle, der indizierten Ansicht oder der externen Tabelle, auf der die Statistiken erstellt werden sollen. Legen Sie einen qualifizierten Tabellennamen fest, um Statistiken für eine andere Datenbank zu erstellen.

Spalte [ ,...n]

Mindestens eine Spalte, die in den Statistiken enthalten sein soll. Die Spalten sollten von links nach rechts nach Priorität geordnet sein. Nur die erste Spalte wird zum Erstellen des Histrogramms verwendet. Alle Spalten werden für spaltenübergreifende Statistiken verwendet, die als „Dichten“ bezeichnet werden.

Sie können beliebige Spalten angeben, die von folgenden Ausnahmen abgesehen als Indexschlüsselspalte angegeben werden können:

• XML-, Volltext- und FILESTREAM-Spalten können nicht angegeben werden.

• Berechnete Spalten können nur angegeben werden, wenn die ARITHABORT Einstellungen und QUOTED_IDENTIFIER Datenbankeinstellungen sind ON.

• Spalten des CLR-benutzerdefiniertne Typs können angegeben werden, wenn der Typ die binäre Reihenfolge unterstützt. Berechnete Spalten, die als Methodenaufrufe einer Spalte eines benutzerdefinierten Typs definiert sind, können angegeben werden, wenn die Methoden als deterministisch gekennzeichnet sind.

WHERE <filter_predicate>

Gibt einen Ausdruck zum Auswählen einer Teilmenge von Zeilen an, die beim Erstellen des Statistikobjekts eingeschlossen werden sollen. Statistiken, die mit einem Filterprädikat erstellt werden, werden als gefilterte Statistiken bezeichnet. Das Filterprädikat verwendet einfache Vergleichslogik und kann nicht auf eine berechnete Spalte, eine UDT-Spalte, eine Spalte für räumliche Datentypen oder eine HierarchieID-Datentypspalte verweisen. Vergleiche, die Literale verwenden NULL , sind mit den Vergleichsoperatoren nicht zulässig. Verwenden Sie stattdessen die Operatoren IS NULL und IS NOT NULL.

Es folgen einige Beispiele für Filterprädikate für die Production.BillOfMaterials-Tabelle:

• WHERE StartDate > '20000101' AND EndDate <= '20000630'
• WHERE ComponentID IN (533, 324, 753)
• WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Weitere Informationen zu Filter-Prädikaten finden Sie unter Erstellen gefilterter Indizes.

FULLSCAN

Gilt für: SQL Server 2016 (13.x) SP 1 CU 4, SQL Server 2017 (14.x) CU 1 und höhere Versionen

Berechnet die Statistiken, indem alle Zeilen überprüft werden. FULLSCAN und SAMPLE 100 PERCENT haben dieselben Ergebnisse. FULLSCAN kann nicht mit der SAMPLE Option verwendet werden.

Wenn diese Option ausgelassen wird, verwendet SQL Server Stichproben, um die Statistiken zu erstellen. Zudem wird die Größe der Stichprobe ermittelt, die erforderlich ist, um einen hochwertigen Abfrageplan zu erstellen.

In Warehouse in Microsoft Fabric werden nur einzelspaltige und einspaltige FULLSCAN SAMPLEStatistiken unterstützt. Wenn keine Option enthalten ist, SAMPLE werden Statistiken erstellt.

SAMPLE Zahl { PERCENT | ROWS }

Gibt den ungefähren Prozentsatz oder die Anzahl der Zeilen in der Tabellen- oder indizierten Ansicht für den Abfrageoptimierer an, der beim Erstellen von Statistiken verwendet werden soll. Bei PERCENT" kann "Zahl " zwischen 0 und 100 und für ROWS" Zahl" zwischen 0 und "Zahl " zwischen 0 und der Gesamtanzahl der Zeilen stehen. Der tatsächliche Prozentsatz oder die tatsächliche Anzahl von Zeilen, die vom Abfrageoptimierer als Stichprobe entnommen werden, stimmt möglicherweise nicht mit dem angegebenen Prozentsatz oder der angegebenen Anzahl überein. Der Abfrageoptimierer scannt z. B. alle Zeilen auf einer Datenseite.

SAMPLE ist für Sonderfälle nützlich, in denen der Abfrageplan basierend auf dem Standardsampling nicht optimal ist. In den meisten Fällen ist es nicht erforderlich, anzugeben SAMPLE , da der Abfrageoptimierer bereits Sampling verwendet und standardmäßig die statistisch signifikante Stichprobengröße bestimmt, wie erforderlich, um qualitativ hochwertige Abfragepläne zu erstellen.

SAMPLE kann nicht mit der FULLSCAN-Option verwendet werden. Wenn SAMPLE oder FULLSCAN nicht angegeben wird, verwendet der Abfrageoptimierer stichprobenierte Daten und berechnet standardmäßig die Beispielgröße.

Es wird davon abgeraten, 0 PERCENT oder 0 ROWS anzugeben. Wenn 0 PERCENT oder 0 ROWS wird angegeben, wird das Statistikobjekt erstellt, enthält jedoch keine Statistikdaten.

In Warehouse in Microsoft Fabric werden nur einzelspaltige und einspaltige FULLSCAN SAMPLEStatistiken unterstützt. Wenn keine Option enthalten ist, FULLSCAN werden Statistiken erstellt.

PERSIST_SAMPLE_PERCENT = { ON | OFF }

Wenn ONdie Statistiken den Prozentsatz der Erstellungssampling für nachfolgende Aktualisierungen beibehalten, die keinen Stichprobenprozentsatz explizit angeben. When OFF, Statistics Sampling Percentage gets reset to default sampling in subsequent updates that don't explizit specify a sampling percentage. Der Standardwert ist OFF.

STATS_STREAM = stats_stream

Nur für Informationszwecke identifiziert. Wird nicht unterstützt. Zukünftige Kompatibilität wird nicht sichergestellt.

NORECOMPUTE

Deaktivieren Sie die Option "Automatische Statistikaktualisierung" AUTO_STATISTICS_UPDATEfür statistics_name. Wenn diese Option angegeben wird, schließt der Abfrageoptimierer alle laufenden Statistikupdates für statistics_name ab und deaktiviert zukünftige Updates.

Um Statistikaktualisierungen erneut zu aktivieren, entfernen Sie die Statistiken mit DROP STATISTICS, und führen Sie dann ohne die NORECOMPUTE Option ausCREATE STATISTICS.

Weitere Informationen zur AUTO_STATISTICS_UPDATE Option finden Sie unter ALTER DATABASE SET-Optionen. Weitere Informationen zum Deaktivieren und erneuten Aktivieren von Statistikupdates finden Sie unter Statistiken.

INCREMENTAL = { ON | OFF }

Gilt für: SQL Server 2014 (12.x) und höhere Versionen

Wenn ONdie erstellten Statistiken pro Partitionsstatistik vorliegen. Wenn OFF, Statistiken werden für alle Partitionen kombiniert. Der Standardwert ist OFF.

Wenn pro Partitionsstatistik nicht unterstützt wird, wird ein Fehler generiert. Inkrementelle Statistiken werden für folgende Statistiktypen nicht unterstützt:

• Statistiken, die mit Indizes erstellt wurden, die über keine Partitionsausrichtung mit der Basistabelle verfügen.
• Statistiken, die für lesbare sekundäre Always On-Datenbanken erstellt wurden.
• Statistiken, die für schreibgeschützte Datenbanken erstellt wurden.
• Statistiken, die für gefilterte Indizes erstellt wurden.
• Statistiken, die für Sichten erstellt wurden.
• Statistiken, die für interne Tabellen erstellt wurden.
• Statistiken, die mit räumlichen Indizes oder XML-Indizes erstellt wurden.

MAXDOP = max_degree_of_parallelism

Gilt für: SQL Server 2016 (13.x) SP 2, SQL Server 2017 (14.x) CU 3 und höhere Versionen

Überschreibt die maximale Parallelitätskonfigurationsoption während des Statistikvorgangs. Weitere Informationen finden Sie unter Konfigurieren des maximalen Parallelitätsgrads (Serverkonfigurationsoption). Wird verwendet MAXDOP , um die Anzahl der Prozessoren zu begrenzen, die in einer parallelen Planausführung verwendet werden. Maximal sind 64 Prozessoren zulässig.

max_degree_of_parallelism kann folgende Werte haben:

• 1: Unterdrückt die parallele Plangenerierung.
• >1: Beschränkt die maximale Anzahl von Prozessoren, die in einem parallelen Indexvorgang verwendet werden, auf die angegebene Zahl.
• 0 (Standard): Verwendet die tatsächliche Anzahl von Prozessoren oder weniger basierend auf der aktuellen Systemauslastung.

update_stats_stream_option

Nur für Informationszwecke identifiziert. Wird nicht unterstützt. Zukünftige Kompatibilität wird nicht sichergestellt.

AUTO_DROP = { ON | OFF }

Gilt für: SQL Server 2022 (16.x) und höhere Versionen und Azure SQL-Datenbank, Azure SQL verwaltete Instanz

Bevor SQL Server 2022 (16.x) von einem Benutzer- oder Drittanbietertool für eine Benutzerdatenbank manuell erstellt wird, können diese Statistikobjekte Schemaänderungen blockieren oder beeinträchtigen, die der Kunde möglicherweise wünscht.

Ab SQL Server 2022 (16.x) ist die AUTO_DROP Option standardmäßig für alle neuen und migrierten Datenbanken aktiviert. Die AUTO_DROP Eigenschaft ermöglicht die Erstellung von Statistikobjekten in einem Modus, sodass eine nachfolgende Schemaänderung nicht durch das Statistikobjekt blockiert wird, sondern die Statistiken bei Bedarf gelöscht werden. Auf diese Weise verhalten sich manuell erstellte Statistiken mit AUTO_DROP aktivierter Funktion wie automatisch erstellte Statistiken.

Weitere Informationen finden Sie unter der Option AUTO_DROP.

Berechtigungen

Erfordert eine der folgenden Berechtigungen:

• ALTER TABLE
• Der Benutzer ist der Tabellenbesitzer.
• Mitgliedschaft in der festen Datenbankrolle db_ddladmin.

Hinweise

SQL Server kann tempdb verwenden, um die als Stichprobe entnommenen Zeilen vor dem Erstellen der Statistiken zu sortieren.

Statistiken für externe Tabellen

Beim Erstellen von Statistiken für externe Tabellen importiert SQL Server die externe Tabelle in eine temporäre SQL Server-Tabelle und erstellt anschließend die Statistiken. Bei Statistiken für Stichproben werden nur die als Stichprobe entnommenen Zeilen importiert. Wenn Sie über eine große externe Tabelle verfügen, ist es schneller, das Standardsampling anstelle der vollständigen Scanoption zu verwenden.

Wenn die externe Tabelle DELIMITEDTEXT, CSV, PARQUET oder DELTA als Datentypen verwendet, unterstützt externe Tabellen nur Statistiken für eine Spalte pro CREATE STATISTICS-Befehl.

Statistiken mit einer gefilterten Bedingung

Gefilterte Statistiken können die Abfrageleistung für Abfragen verbessern, bei denen aus klar definierten Teilmengen von Daten ausgewählt wird. Gefilterte Statistiken verwenden ein Filterprädikat in der WHERE-Klausel, um die Teilmenge von Daten auszuwählen, die in den Statistiken enthalten ist.

Verwendung von CREATE STATISTICS

Weitere Informationen zur Verwendung von CREATE STATISTICS finden Sie unter Statistiken.

Verweisen auf Abhängigkeiten für gefilterte Statistiken

Die sys.sql_expression_dependencies-Katalogsicht kennzeichnet jede Spalte im gefilterten Statistikprädikat als eine verweisende Abhängigkeit. Berücksichtigen Sie die Vorgänge, die Sie in Tabellenspalten ausführen, bevor Sie gefilterte Statistiken erstellen. Sie können die Definition einer Tabellenspalte, die in einem gefilterten Statistik-Prädikat definiert ist, nicht ablegen, umbenennen oder ändern.

Begrenzungen

• Das Aktualisieren von Statistiken bei externen Tabellen wird nicht unterstützt. Zum Aktualisieren einer Statistik müssen Sie die Statistik löschen und neu erstellen.
• Sie können bis zu 64 Spalten pro Statistikobjekt auflisten.
• Die MAXDOP Option ist nicht kompatibel mit STATS_STREAM, ROWCOUNTund PAGECOUNT Optionen.
• Die Option MAXDOP ist, falls verwendet, durch die Einstellung „MAX_DOP“ der Resource Governor-Arbeitsauslastungsgruppe eingeschränkt.
• CREATEund DROP STATISTICS in externen Tabellen werden in Azure SQL-Datenbank nicht unterstützt.

Beispiele

Die Transact-SQL-Codebeispiele in diesem Artikel verwenden die AdventureWorks2022- oder AdventureWorksDW2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.

A. Verwenden von CREATE STATISTICS mit SAMPLE number PERCENT

Im folgenden Beispiel wird die ContactMail1-Statistik erstellt. Dabei wird eine zufällige Stichprobe von 5 Prozent aus den Spalten BusinessEntityID und EmailPromotion der Tabelle Person in der AdventureWorks2022-Datenbank verwendet.

CREATE STATISTICS ContactMail1
    ON Person.Person (BusinessEntityID, EmailPromotion)
    WITH SAMPLE 5 PERCENT;

B. Verwenden von CREATE STATISTICS mit FULLSCAN und NORECOMPUTE

Im folgenden Beispiel werden die NamePurchase-Statistiken für alle Zeilen in der BusinessEntityID-Spalte und der EmailPromotion-Spalte der Person-Tabelle erstellt. Dabei wird die automatische Neuberechnung von Statistiken deaktiviert.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, NORECOMPUTE;

C. Erstellen gefilterter Statistiken mithilfe von CREATE STATISTICS

Im folgenden Beispiel wird die gefilterte Statistik ContactPromotion1 erstellt. Die Datenbank-Engine nimmt 50 Prozent der Daten in die Stichprobe auf und wählt dann die Zeilen aus, in denen EmailPromotion gleich 2 ist.

CREATE STATISTICS ContactPromotion1
    ON Person.Person (BusinessEntityID, LastName, EmailPromotion)
WHERE EmailPromotion = 2
WITH SAMPLE 50 PERCENT;
GO

D: Erstellen von Statistiken für eine externe Tabelle

Sie müssen beim Erstellen von Statistiken für eine externe Tabelle abgesehen von der Bereitstellung einer Liste der Spalten lediglich entscheiden, ob die Statistiken durch Stichprobenentnahme aus den Zeilen oder durch einen Scan aller Zeilen erstellt werden soll. CREATEund DROP STATISTICS in externen Tabellen werden in Azure SQL-Datenbank nicht unterstützt.

Da SQL Server Daten aus der externen Tabelle in eine temporäre Tabelle importiert, um Statistiken zu erstellen, nimmt die FULL SCAN-Option wesentlich mehr Zeit in Anspruch. Bei einer großen Tabelle ist die Standardmethode für die Stichprobenentnahme in der Regel ausreichend.

--Create statistics on an external table and use default sampling.
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);

--Create statistics on an external table and scan all the rows
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

E. Verwenden von CREATE STATISTICS mit FULLSCAN und PERSIST_SAMPLE_PERCENT

Im folgenden Beispiel werden die NamePurchase Statistiken für alle Zeilen und BusinessEntityID EmailPromotion Spalten der Person Tabelle erstellt und für alle nachfolgenden Aktualisierungen, die keinen Samplingprozentsatz explizit angeben, ein Samplingprozentsatz von 100 Prozent festgelegt.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

In den Beispielen wird die Datenbank „AdventureWorksDW“ verwendet.

F. Erstellen von Statistiken für zwei Spalten

Im folgenden Beispiel werden die CustomerStats1-Statistiken basierend auf den Spalten CustomerKey und EmailAddress der Tabelle DimCustomer erstellt. Die Statistiken werden basierend auf einer statistisch relevanten Stichprobenentnahme der Zeilen in der Customer-Tabelle erstellt.

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);

G. Erstellen von Statistiken mithilfe eines vollständigen Scans

Im folgenden Beispiel wird die Statistik CustomerStatsFullScan basierend auf allen Zeilen in der Tabelle DimCustomer erstellt.

CREATE STATISTICS CustomerStatsFullScan
ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

H. Erstellen von Statistiken durch Angeben des Stichprobenprozentsatzes

Im folgenden Beispiel wird die Statistik CustomerStatsSampleScan basierend auf einem Scan von 50 Prozent der Zeilen in der Tabelle DimCustomer erstellt.

CREATE STATISTICS CustomerStatsSampleScan
ON DimCustomer (CustomerKey, EmailAddress) WITH SAMPLE 50 PERCENT;

I. Verwenden von CREATE STATISTICS mit AUTO_DROP

Um Auto-Drop-Statistiken zu verwenden, fügen Sie der WITH-Klausel bei der Statistikerstellung oder -aktualisierung einfach Folgendes hinzu.

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH AUTO_DROP = ON

Verwenden Sie die Spalte auto_drop in sys.stats, um die Auto-Drop-Einstellung in vorhandenen Statistiken auszuwerten:

SELECT object_id, [name], auto_drop
FROM sys.stats;

Zugehöriger Inhalt

• Statistik
• Statistiken in Fabric Data Warehouse
• UPDATE STATISTICS (Transact-SQL)
• sp_updatestats (Transact-SQL)
• DBCC SHOW_STATISTICS (Transact-SQL)
• DROP STATISTICS (Transact-SQL)
• sys.stats (Transact-SQL)
• sys.stats_columns (Transact-SQL)