Freigeben über

Kardinalitätsschätzung (SQL Server)

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

Der Abfrageoptimierer von SQL Server arbeitet kostenorientiert. Das bedeutet, dass er die Abfragepläne zur Ausführung auswählt, die die niedrigsten geschätzten Verarbeitungskosten aufweisen. Der Abfrageoptimierer bestimmt die Kosten für die Ausführung eines Abfrageplans anhand zweier Hauptfaktoren:

  • Die Gesamtanzahl der Zeilen, die auf den jeweiligen Stufen eines Abfrageplans verarbeitet werden. Wird als Kardinalität des Plans bezeichnet.
  • Das Kostenmodell des Algorithmus, der von den in der Abfrage verwendeten Operatoren vorgeschrieben wird.

Der erste Faktor, die Kardinalität, wird als Eingabeparameter für den zweiten Faktor, das Kostenmodell, verwendet. Aus diesem Grund führt eine verbesserte Kardinalität zu verbesserten geschätzten Kosten und wiederum zu schnelleren Ausführungsplänen.

Die Kardinalitätsschätzung in SQL Server erfolgt in erster Linie mithilfe von Histogrammen, die gleichzeitig mit Indizes oder Statistiken erstellt werden. Der Vorgang kann entweder manuell oder automatisch ausgeführt werden. In manchen Fällen verwendet SQL Server auch Einschränkungsinformationen und logische Umschreibungen von Abfragen, um die Kardinalität zu bestimmen.

In den folgenden Fällen kann SQL Server die Kardinalitätswerte nicht genau berechnen. Dies führt zu ungenauen Kostenberechnungen, die wiederum nicht optimale Abfragepläne zur Folge haben können. Die Vermeidung dieser Konstrukte in Abfragen könnte die Abfrageleistung verbessern. In manchen Fällen sind alternative Abfrageformulierungen oder andere Maßnahmen möglich, die nachstehend angegeben sind:

  • Abfragen mit Prädikaten, die Vergleichsoperatoren zwischen verschiedenen Spalten derselben Tabelle verwenden.
  • Abfragen mit Prädikaten, die Operatoren verwenden, und für die eine der folgenden Voraussetzungen zutrifft:
    • Es liegen keine Statistiken zu den beteiligten Spalten auf beiden Seiten der Operatoren vor.
    • Die Verteilung der Werte in den Statistiken ist nicht einheitlich, die Abfrage sucht jedoch einen sehr selektiven Satz an Werten. Dieser Umstand tritt besonders dann ein, wenn ein anderer Operator als der equality-Operator (=) verwendet wird.
    • Das Prädikat verwendet den Ungleich-Vergleichsoperator (!=) oder den logischen Operator NOT.
  • Abfragen, die eine der integrierten SQL Server-Funktionen oder eine benutzerdefinierte Skalarwertfunktion verwenden, deren Argument kein konstanter Wert ist.
  • Abfragen, bei denen Spalten durch arithmetische Operatoren oder Zeichenfolgenverkettungsoperatoren verknüpft werden.
  • Abfragen, die Variablen vergleichen, deren Werte zum Zeitpunkt der Kompilierung und Optimierung nicht bekannt sind.

Dieser Artikel veranschaulicht, wie Sie die beste Konfiguration für die Kardinalitätsschätzung für Ihr System bewerten und auswählen. Die meisten Systeme profitieren von der neuesten Kardinalitätsschätzung, da sie die genaueste ist. Die Kardinalitätsschätzung sagt vorher, wie viele Zeilen eine Abfrage wahrscheinlich zurückgibt. Die Vorhersage der Kardinalität wird vom Abfrageoptimierer verwendet, um einen optimalen Abfrageplan zu generieren. Mit genaueren Schätzungen kann der Abfrageoptimierer in der Regel einen besseren Abfrageplan erzeugen.

Möglicherweise existiert in Ihrem Anwendungssystem eine wichtige Abfrage, deren Plan aufgrund von Änderungen an der Kardinalitätsschätzung zwischen Versionen in einen langsameren Plan geändert wird. Sie verfügen über Techniken und Tools, um eine Abfrage zu identifizieren, die aufgrund von Problemen bei der Kardinalitätsschätzung langsamer ausgeführt wird. Darüber hinaus verfügen Sie über Optionen, wie Sie das daraus resultierende Leistungsproblem beheben.

Versionen der Kardinalitätsschätzung

Im Jahr 1998 wurde in SQL Server 7.0 ein wichtiges Update der Kardinalitätsschätzung eingeführt, mit dem Kompatibilitätsgrad 70. Diese Version des Modells der Kardinalitätsschätzung baut auf vier grundlegenden Annahmen auf:

  • Unabhängigkeit: Es wird angenommen, dass Datenverteilungen in verschiedenen Spalten unabhängig voneinander sind, es sei denn, Korrelationsinformationen sind verfügbar und verwendbar.

  • Einheitlichkeit: DISTINCT-Werte haben denselben Abstand und dieselbe Häufigkeit. Genauer gesagt, sind DISTINCT-Werte innerhalb der Schritte eines Histogramms gleichmäßig verteilt, und jeder Wert weist dieselbe Häufigkeit auf.

  • Einschluss (einfach): Benutzer fragen Daten ab, die bereits vorhanden sind. Bei einem Gleichheits-Join von zwei Tabellen sollten Sie z.B. die Selektivität1 von Prädikaten in jedem Eingabehistogramm berücksichtigen, bevor Sie Histogramme verknüpfen, um die Joinselektivität zu schätzen.

  • Aufnahme: Bei Filterprädikaten, in denen Column = Constant gilt, wird angenommen, dass sie für die zugeordnete Spalte tatsächlich vorhanden sind. Wenn ein entsprechender Histogrammschritt nicht leer ist, wird angenommen, dass einer der DISTINCT-Werte des Schritts dem Wert des Prädikats entspricht.

    1 Zeilenanzahl, die dem Prädikat entspricht

Spätere Updates beginnen mit SQL Server 2014 (12.x), also mit Kompatibilitätsgrad 120 und höher. Die Updates der Kardinalitätsschätzung für die Kompatibilitätsgrade 120 und höher umfassen aktualisierte Annahmen und Algorithmen, die für moderne Data Warehousing- und OLTP-Workloads gut funktionieren. Aus den Annahmen der Kardinalitätsschätzung 70 wurden ab Kardinalitätsschätzung 120 die folgenden Modellannahmen geändert:

  • Unabhängigkeit wird zu Korrelation: Die Kombination der verschiedenen Spaltenwerte ist nicht unbedingt unabhängig. Dies ähnelt eher einer realistischen Datenabfrage.
  • Der einfache Einschluss wird zum Basiseinschluss: Benutzer können Daten abfragen, die nicht vorhanden sind. Bei einem Gleichheits-Join von zwei Tabellen nutzen wir die Basistabellenhistogramme, um die Joinselektivität zu schätzen, und berücksichtigen anschließend die Selektivität der Prädikate.

Verwenden des Abfragespeichers zum Bewerten der Version der Kardinalitätsschätzung

Ab SQL Server 2016 (13.x) ist der Abfragespeicher als nützliches Tool enthalten, um die Leistung Ihrer Abfragen zu untersuchen. Sobald der Abfragespeicher aktiviert wird, verfolgt er die Abfrageleistung im Zeitverlauf nach (selbst bei einer Änderung der Ausführungspläne). Überwachen Sie den Abfragespeicher, um eine kostenintensive oder nachlassende Abfrageleistung zu ermitteln. Weitere Informationen finden Sie unter Leistungsüberwachung mit dem Abfragespeicher.

Wenn Sie ein Upgrade auf SQL Server vorbereiten oder einen Datenbank-Kompatibilitätsgrad auf einer SQL Server-Plattform höher stufen, sollten Sie das Upgraden von Datenbanken mit dem Abfrageoptimierungs-Assistenten in Betracht ziehen. Dadurch lässt sich die Abfrageleistung bei zwei unterschiedlichen Kompatibilitätsgraden vergleichen.

Wichtig

Stellen Sie sicher, dass der Abfragespeicher korrekt für Ihre Datenbank und Ihre Arbeitsauslastung konfiguriert ist. Weitere Informationen finden Sie unter Bewährte Methoden für den Abfragespeicher.

Verwenden erweiterter Ereignisse zum Bewerten der Version der Kardinalitätsschätzung

Eine andere Option zum Nachverfolgen des Prozesses der Kardinalitätsschätzung ist die Verwendung des erweiterten Ereignisses query_optimizer_estimate_cardinality. Das folgende Transact-SQL-Codebeispiel wird in SQL Server ausgeführt. Es schreibt eine XEL-Datei in C:\Temp\ (Sie können den Pfad ändern). Wenn Sie die XEL-Datei in Management Studio öffnen, werden die detaillierten Daten in einer von Benutzern gut lesbaren Weise angezeigt.

DROP EVENT SESSION Test_the_CE_qoec_1 ON SERVER;  
go  
  
CREATE EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
ADD EVENT sqlserver.query_optimizer_estimate_cardinality  
 (  
 ACTION (sqlserver.sql_text)  
  WHERE (  
  sql_text LIKE '%yourTable%'  
  and sql_text LIKE '%SUM(%'  
  )  
 )  
ADD TARGET package0.asynchronous_file_target
 (SET  
  filename = 'c:\temp\xe_qoec_1.xel',  
  metadatafile = 'c:\temp\xe_qoec_1.xem'  
 );  
GO  
  
ALTER EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
STATE = START;  --STOP;  
GO

Hinweis

Das Ereignis sqlserver.query_optimizer_estimate_cardinality ist für die Azure SQL-Datenbank nicht verfügbar.

Informationen zu erweiterten Ereignissen, die speziell auf die SQL-Datenbank zugeschnitten sind, finden Sie unter Erweiterte Ereignisse in SQL-Datenbank.

Schritte für die Bewertung der Version der Kardinalitätsschätzung

Mit den folgenden Schritten können Sie ermitteln, ob Ihre wichtigsten Abfragen bei Verwendung der neuesten Kardinalitätsschätzung mit geringerer Leistung ausgeführt werden. In einigen der Schritte wird ein Codebeispiel ausgeführt, das im vorherigen Abschnitt vorgestellt wurde.

  1. Öffnen Sie SQL Server Management Studio (SSMS). Stellen Sie sicher, dass für Ihre SQL Server-Datenbank der höchste verfügbare Kompatibilitätsgrad festgelegt wurde.

  2. Führen Sie die folgenden vorbereitenden Schritte aus:

    1. Öffnen Sie SQL Server Management Studio (SSMS).

    2. Führen Sie die Transact-SQL-Anweisung aus, um sicherzustellen, dass für Ihre SQL Server-Datenbank der höchste verfügbare Kompatibilitätsgrad festgelegt wurde.

    3. Stellen Sie sicher, dass die LEGACY_CARDINALITY_ESTIMATION-Konfiguration Ihrer Datenbank deaktiviert ist.

    4. Bereinigen Sie Ihren Abfragespeicher. Stellen Sie sicher, dass für Ihre Datenbank Abfragespeicher AKTIVIERT ist.

    5. Führen Sie die Anweisung SET NOCOUNT OFF; aus.

  3. Führen Sie die Anweisung SET STATISTICS XML ON; aus.

  4. Führen Sie Ihre wichtige Abfrage aus.

  5. Beachten Sie im Ergebnisbereich auf der Registerkarte Meldungen die tatsächliche Anzahl von betroffenen Zeilen.

  6. Doppelklicken Sie im Ergebnisbereich auf der Registerkarte Ergebnisse auf die Zelle, die die Statistik im XML-Format enthält. Es wird ein grafischer Abfrageplan angezeigt.

  7. Klicken Sie mit der rechten Maustaste auf das erste Feld im grafischen Abfrageplan, und wählen Sie Eigenschaften aus.

  8. Notieren Sie die Werte für die folgenden Eigenschaften, um diese später mit einer anderen Konfiguration vergleichen zu können:

    • CardinalityEstimationModelVersion.

    • Geschätzte Anzahl von Zeilen.

    • Geschätzte E/A-Kostensowie einige weitere ähnliche geschätzte Eigenschaften, die sich eher auf die tatsächliche Leistung als auf Vorhersagen zur Zeilenanzahl beziehen.

    • Logische Operation und Physische Operation. Parallelität ist ein guter Wert.

    • Tatsächlicher Ausführungsmodus. Batch ist ein guter Wert und besser als Zeile.

  9. Vergleichen Sie die geschätzte Anzahl von Zeilen mit der tatsächliche Zeilenanzahl. Ist die Kardinalitätsschätzung um 1% oder um 10% ungenau (höher oder niedriger)?

  10. Führen Sie SET STATISTICS XML OFF; aus.

  11. Führen Sie die Transact-SQL-Anweisung aus, um den Kompatibilitätsgrad Ihrer Datenbank um eine Stufe zu senken (z. B. von 130 auf 120).

  12. Führen Sie alle Schritte erneut aus, die keine Vorbereitungsschritte sind.

  13. Vergleichen Sie die Eigenschaftswerte der Kardinalitätsschätzung der beiden Ausführungen.

    • Ist der Prozentsatz der Ungenauigkeit mit der neuesten Kardinalitätsschätzung geringer als mit der älteren?

  14. Vergleichen Sie zum Schluss die verschiedenen Leistungswerte der beiden Ausführungen.

    • Hat Ihre Abfrage bei den beiden verschiedenen Kardinalitätsschätzungen unterschiedliche Abfragepläne verwendet?

    • Wurde Ihre Abfrage mit der neuesten Kardinalitätsschätzung langsamer ausgeführt?

    • Wenn Ihre Abfrage nicht mit der älteren Kardinalitätsschätzung besser und unter einem anderen Plan ausgeführt wird als mit der neuen Schätzung, sollten Sie mit größter Wahrscheinlichkeit die neueste Kardinalitätsschätzung verwenden.

    • Wenn Ihre Abfrage allerdings mit der älteren Kardinalitätsschätzung unter einem schnelleren Plan ausgeführt wird, sollten Sie erwägen, Ihr System zu zwingen, den schnelleren Plan zu verwenden und die Kardinalitätsschätzung zu ignorieren. Auf diese Weise können Sie die neueste Kardinalitätsschätzung allgemein im System verwenden, für Sonderfälle aber den schnelleren Plan beibehalten.

Aktivieren des besten Abfrageplans

Angenommen, mit der Kardinalitätsschätzung 120 oder höher wird ein langsamerer Abfrageplan für Ihre Abfrage generiert. Nachfolgend sind einige Optionen aufgeführt, um den besseren Plan zu aktivieren (vom größten bis zum kleinsten Bereich):

  • Sie können den Kompatibilitätsgrad für Ihre gesamte Datenbank auf einen niedrigeren Wert als den neuesten verfügbaren Grad festlegen.

    • Wenn Sie z.B. den Kompatibilitätsgrad auf 110 oder niedriger festlegen, wird die Kardinalitätsschätzung 70 aktiviert, obwohl alle Abfragen dem vorherigen Kardinalitätsschätzungsmodell unterliegen.

    • Beim Festlegen eines niedrigeren Kompatibilitätsgrads werden außerdem viele Verbesserungen des Abfrageoptimierers für die neuesten Versionen nicht genutzt. Zudem wirkt sich diese Änderung auf alle Abfragen aus, die an die Datenbank gesendet werden.

  • Sie können die Konfigurationsoption für den Reservierungsumfang der Datenbank LEGACY_CARDINALITY_ESTIMATION verwenden, sodass die gesamte Datenbank die ältere Kardinalitätsschätzung verwendet, die Verbesserungen des Abfrageoptimierers jedoch beibehalten werden.

  • Sie können den Abfragehinweis LEGACY_CARDINALITY_ESTIMATION verwenden, sodass eine einzelne Abfrage die ältere Kardinalitätsschätzung verwendet, die Verbesserungen des Abfrageoptimierers jedoch beibehalten werden.

  • Sie können die LEGACY_CARDINALITY_ESTIMATION über den Abfragespeicherhinweis erzwingen, damit eine einzelne Abfrage die ältere Kardinalitätsschätzung verwendet, ohne dabei die Abfrage zu ändern.

  • Erzwingen Sie einen anderen Plan mit dem Abfragespeicher.

Kompatibilitätsgrad der Datenbank

Sie können sicherstellen, dass ein bestimmter Grad für Ihre Datenbank gilt, indem Sie den folgenden Transact-SQL-Code für COMPATIBILITY_LEVEL ausführen.

Wichtig

Die Versionsnummern der Datenbank-Engine für SQL Server und Azure SQL-Datenbank sind nicht miteinander vergleichbar, sondern sind interne Buildnummern für diese separaten Produkte. Die Datenbank-Engine für Azure SQL Server basiert auf der gleichen Codebasis wie die SQL Server-Datenbank-Engine. Entscheidend ist dabei, dass die Datenbank-Engine in Azure SQL-Datenbank immer über die neuesten SQL-Datenbank-Engine-Bits verfügt. Version 12 von Azure SQL-Datenbank ist neuer als Version 15 von SQL Server. Seit November 2019 ist der Standardkompatibilitätsgrad für neu erstellte Datenbanken in Azure SQL-Datenbank gleich „150“. Microsoft passt bei vorhandenen Datenbanken den Datenbank-Kompatibilitätsgrad nicht an. Kunden können diesen nach Ihren eigenen Bedürfnissen anpassen.

SELECT ServerProperty('ProductVersion');  
GO  

SELECT d.name, d.compatibility_level  
FROM sys.databases AS d  
WHERE d.name = 'yourDatabase';  
GO

Bei vorhandenen Datenbanken mit niedrigeren Kompatibilitätsgraden gilt Folgendes: Solange eine Anwendung keine Verbesserungen nutzen muss, die nur bei einem höheren Kompatibilitätsgrad verfügbar sind, ist es sinnvoll, den vorherigen Kompatibilitätsgrad beizubehalten. Bei Neuentwicklungen oder falls eine vorhandene Anwendung neue Features wie die intelligente Abfrageverarbeitung sowie neue Transact-SQL-Abfragen nutzen muss, sollten Sie ein Upgrade auf den neuesten verfügbaren Datenbank-Kompatibilitätsgrad planen. Weitere Informationen finden Sie unter Kompatibilitätsgrade und Upgrades der Datenbank-Engine.

Achtung

Lesen Sie die Informationen unter Bewährte Methoden zum Aktualisieren des Datenbank-Kompatibilitätsgrads, bevor Sie den Datenbank-Kompatibilitätsgrad ändern.

ALTER DATABASE <yourDatabase>  
SET COMPATIBILITY_LEVEL = 150;  
GO

In einer SQL Server-Datenbank, die mit dem Kompatibilitätsgrad 120 oder höher eingerichtet wurde, zwingt die Aktivierung des Ablaufverfolgungsflags 9481 das System dazu, Version 70 der Kardinalitätsschätzung zu verwenden.

Legacy-Kardinalitätsschätzung

In einer SQL Server-Datenbank, die mit dem Kompatibilitätsgrad 120 oder höher eingerichtet wurde, kann die Legacy-Kardinalitätsschätzung (Version 70) auf Datenbankebene aktiviert werden. Verwenden Sie dazu ALTER DATABASE SCOPED CONFIGURATION.

ALTER DATABASE SCOPED CONFIGURATION 
SET LEGACY_CARDINALITY_ESTIMATION = ON;  
GO  
  
SELECT name, value  
FROM sys.database_scoped_configurations  
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';  
GO

Ändern der Abfrage zur Verwendung eines Hinweises

Ab SQL Server 2016 (13.x) SP1 ändern Sie die Abfrage so, dass der Abfragehinweis USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') verwendet wird.

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01'
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

Festlegen eines Abfragespeicherhinweises

Für Abfragen kann die Verwendung der Legacy-Kardinalitätsschätzung erzwungen werden, ohne die Abfrage zu ändern. Zu diesem Zweck werden Abfragespeicherhinweise verwendet.

  1. Suchen Sie in den Katalogsichten des Abfragespeichers sys.query_store_query_text und sys.query_store_query nach der Abfrage. Suchen Sie z. B. mithilfe eines Textfragments nach einer ausgeführten Abfrage:

    SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt 
INNER JOIN sys.query_store_query q ON 
qt.query_text_id = q.query_text_id 
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%'  
AND query_sql_text not like N'%query_store%';

  2. Im folgenden Beispiel wird ein Abfragespeicherhinweis angewendet, um die Legacy-Kardinalitätsschätzung für „query_id 39“ zu erzwingen, ohne die Abfrage zu ändern:

    EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

Hinweis

Weitere Informationen finden Sie unter Query Story Hints (Preview) (Abfragespeicherhinweise [Vorschauversion]). Dieses Feature ist aktuell nur in Azure SQL-Datenbank verfügbar.

Erzwingen eines bestimmten Abfrageplans

Die genaueste Steuerung erzielen Sie, indem Sie erzwingen, dass das System den Plan verwendet, der während der Testphase mit der Kardinalitätsschätzung 70 generiert wurde. Nachdem Sie den bevorzugten Plan angeheftet haben, können Sie die gesamte Datenbank für die Verwendung des neuesten Kompatibilitätsgrad und der neuesten Kardinalitätsschätzung einrichten. Diese Option wird im nächsten Abschnitt erläutert.

Der Abfragespeicher bietet verschiedene Möglichkeiten, um die Verwendung eines bestimmten Abfrageplans im System zu erzwingen:

  • Führen Sie sys.sp_query_store_force_plan aus.

  • Erweitern Sie in SQL Server Management Studio (SSMS) den Knoten für Ihren Abfragespeicher, klicken Sie mit der rechten Maustaste auf Knoten mit dem höchsten Ressourcenverbrauch, und wählen Sie Knoten mit dem höchsten Ressourcenverbrauch anzeigen aus. Es werden die Schaltflächen Plan erzwingen und Erzwingung des Plans aufhebenangezeigt.

Weitere Informationen zum Abfragespeicher finden Sie unter Überwachen der Leistung mit dem Abfragespeicher.

Reduktion konstanter Ausdrücke und Ausdrucksauswertung während der Kardinalitätsschätzung

Die Datenbank-Engine wertet bestimmte konstante Ausdrücke frühzeitig aus, um die Abfrageleistung zu steigern. Dies wird als Reduktion konstanter Ausdrücke bezeichnet. Eine Konstante ist ein Transact-SQL-Literal, z. B. 3, 'ABC', '2005-12-31', 1.0e3 oder 0x12345678. Weitere Informationen finden Sie unter Reduktion konstanter Ausdrücke.

Außerdem werden bestimmte Ausdrücke, deren Konstanten nicht reduziert werden, deren Argumente jedoch zur Kompilierzeit bekannt sind – unabhängig davon, ob es sich dabei um Parameter oder Konstanten handelt –, vom Schätzer für die Resultsetgröße (Kardinalität) ausgewertet. Dieser Schätzer ist ein Bestandteil des Abfrageoptimierers. Weitere Informationen finden Sie unter Auswertung von Ausdrücken.

Bewährte Methoden: Verwenden der Reduktion konstanter Ausdrücke und der Auswertung von Ausdrücken zur Kompilierzeit zum Generieren optimaler Abfragepläne

Um optimale Abfragepläne zu generieren, sollten Sie Ihre Abfragen, gespeicherten Prozeduren und Batches so entwerfen, dass der Abfrageoptimierer die Selektivität der Abfragebedingungen auf der Grundlage der Statistiken zur Datenverteilung genau schätzen kann. Anderenfalls muss der Abfrageoptimierer bei der Schätzung der Selektivität eine Standardschätzung verwenden.

Damit die Kardinalität vom Abfrageoptimierer richtig eingeschätzt wird, sollten Sie zunächst überprüfen, ob die Datenbank-SET-Optionen AUTO_CREATE_STATISTICS und AUTO_UPDATE_STATISTICS auf ON festgelegt sind (dies ist die Standardeinstellung), oder manuell Statistiken für alle Spalten erstellen, auf die in einer Abfragebedingung verwiesen wird. Beim Entwerfen der Abfragebedingungen sollten Sie nach Möglichkeit Folgendes beachten:

  • Vermeiden Sie den Einsatz lokaler Variablen in Abfragen. Verwenden Sie stattdessen in der Abfrage Parameter, Literale oder Ausdrücke.

  • Beschränken Sie den Einsatz von in einer Abfrage eingebetteten Operatoren und Funktionen, die einen Parameter für die Elemente enthalten, der unter „Auswertung der Kompilierzeit für die Kardinalitätsschätzung“ aufgeführt ist.

  • Stellen Sie sicher, dass ausschließlich konstante Ausdrücke in der Bedingung Ihrer Abfrage entweder konstant faltbar sind oder zur Kompilierzeit ausgewertet werden können.

  • Wenn Sie einen Ausdruck, der in einer Abfrage verwendet werden soll, mit einer lokalen Variable auswerten müssen, sollten Sie ihn in einem anderen Umfang als der Abfrage auswerten. Beispielsweise könnte es sich lohnen, eine der folgenden Optionen auszuführen:

    • Übergeben Sie den Wert der Variable an eine gespeicherte Prozedur, welche die Abfrage enthält, die Sie auswerten möchten, und verwenden Sie den Prozedurparameter anstelle einer lokalen Variablen.

    • Erstellen Sie eine Zeichenfolge, die eine teilweise auf dem Wert der lokalen Variable basierende Abfrage enthält, und führen Sie die Zeichenfolge mithilfe von dynamischem SQL-Code aus (EXEC oder sp_executesql).

    • Parametrisieren Sie die Abfrage, und führen Sie sie mithilfe von sp_executesql aus. Übergeben Sie den Wert der Variable als Parameter an die Abfrage.

Beispiele für Verbesserungen der Kardinalitätsschätzung

Dieser Abschnitt enthält Beispielabfragen, die von den Erweiterungen der Kardinalitätsschätzung in neueren Versionen profitieren. Hierbei handelt es sich um Hintergrundinformationen, die keine bestimmte Aktion Ihrerseits erfordern.

Beispiel A. Die Kardinalitätsschätzung berücksichtigt, dass Maximalwerte höher sein können als bei der letzten Erfassung der Statistiken

Angenommen, die Statistiken für OrderTable wurden zuletzt am 2016-04-30 erstellt, als der maximale Wert für OrderAddedDate2016-04-30 entsprach. Bei der Kardinalitätsschätzung 120 (und höher) wird berücksichtigt, dass Spalten in OrderTable mit ansteigenden Daten Werte aufweisen können, die über dem von der Statistik aufgezeichneten Maximalwert liegen. Dies verbessert den Abfrageplan für Transact-SQL-SELECT-Anweisungen wie die folgende.

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01';

Beispiel B. Die Kardinalitätsschätzung berücksichtigt, dass gefilterte Prädikate in der gleichen Tabelle häufig korrelieren.

Die folgende SELECT-Anweisung zeigt gefilterte Prädikate für Model und ModelVariant. Wenn der Model-Wert „Xbox“ lautet, wird intuitiv verstanden, dass die Möglichkeit besteht, dass der ModelVariant-Wert „One“ lautet, da es von „Xbox“ eine Variante namens „One“ gibt.

Ab der Kardinalitätsschätzung 120 berücksichtigt SQL Server, dass eine Korrelation zwischen den Spalten Model und ModelVariant existieren kann, die sich in der gleichen Tabelle befinden. Die Kardinalitätsschätzung kann genauer einschätzen, wie viele Zeilen von der Abfrage zurückgegeben werden, und der Abfrageoptimierer generiert einen besseren Plan.

SELECT Model, Purchase_Price  
FROM dbo.Hardware  
WHERE Model = 'Xbox' AND  
ModelVariant = 'Series X';

Beispiel C. Die Kardinalitätsschätzung geht nicht mehr von einer Korrelation zwischen gefilterten Prädikaten aus verschiedenen Tabellen aus

Umfangreiche neue Untersuchungen moderner Arbeitsauslastungen und realer Geschäftsdaten haben ergeben, dass Prädikatfilter aus unterschiedlichen Tabellen üblicherweise nicht korrelieren. In der folgenden Abfrage nimmt die Kardinalitätsschätzung an, dass zwischen s.type und r.date keine Korrelation besteht. Daher wird die Anzahl der zurückgegebenen Zeilen niedriger eingeschätzt.

SELECT s.ticket, s.customer, r.store  
FROM dbo.Sales AS s  
CROSS JOIN dbo.Returns AS r  
WHERE s.ticket = r.ticket AND  
s.type = 'toy' AND  
r.date = '2016-05-11';

Zugehöriger Inhalt