Kardinalitätsschätzung (SQL Server)

  • Artikel

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

Der SQL Server-Abfrageoptimierer ist ein kostenbasierter Abfrageoptimierer. 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 (CE) in SQL Server wird hauptsächlich von Histogrammen abgeleitet, die beim Erstellen von Indizes oder Statistiken entweder manuell oder automatisch erstellt werden. Manchmal verwendet SQL Server auch Einschränkungsinformationen und logische Umschreibungen von Abfragen, um Karte inalität zu bestimmen.

In den folgenden Fällen kann SQL Server die Kardinalitätswerte nicht genau berechnen. Dies führt zu ungenauen Kostenberechnungen, die zu suboptimalen Abfrageplänen führen können. Das Vermeiden dieser Konstrukte in Abfragen kann 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 eine der folgenden Abfragen gilt:
    • 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

1998 war ein großes Update der CE Teil von SQL Server 7.0, für das die Kompatibilitätsstufe 70 war. 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

Nachfolgende Updates wurden mit SQL Server 2014 (12.x) gestartet, d. h. Kompatibilitätsstufen 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 Korrelation: Die Kombination der verschiedenen Spaltenwerte ist nicht notwendigerweise unabhängig. Dies könnte der Abfrage realer Daten ähneln.
  • 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 die Abfragespeicher ein praktisches Tool zum Untersuchen der Leistung Ihrer Abfragen. 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 eine Datenbankkompatibilitätsstufe auf einer beliebigen SQL Server-Plattform bewerben, sollten Sie das Upgrade von Datenbanken mithilfe des Abfrageoptimierungs-Assistenten in Betracht ziehen, wodurch die Abfrageleistung in zwei verschiedenen Kompatibilitätsstufen verglichen werden kann.

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 weitere Option zum Nachverfolgen des Karte inalitätsschätzungsprozesses ist die Verwendung des erweiterten Ereignisses namens 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 Informationen auf benutzerfreundliche 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 Azure SQL-Datenbank nicht verfügbar.

Informationen zu erweiterten Ereignissen, die auf 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 in Ihrer Datenbank sicher, dass 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 mit Datenbankbereich verwenden LEGACY_CARDINALITY_ESTIMATION , damit die gesamte Datenbank die ältere CE verwendet, während andere Verbesserungen im Abfrageoptimierer 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. Das Datenbankmodul für Azure SQL Server basiert auf derselben Codebasis wie das SQL Server-Datenbankmodul. 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 aktualisiert die Datenbankkompatibilitätsstufe für vorhandene Datenbanken nicht. 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

Für bereits vorhandene Datenbanken, die auf niedrigeren Kompatibilitätsebenen ausgeführt werden, ist es ein gültiger Ansatz, um die vorherige Datenbankkompatibilitätsstufe zu Standard beibehalten, solange die Anwendung keine Verbesserungen verwenden muss, die nur auf einer höheren Datenbankkompatibilitätsebene verfügbar sind. 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

Für eine SQL Server-Datenbank, die auf Kompatibilitätsebene 120 und höher festgelegt ist, kann die Legacy-Karte inality-Schätzung (CE Version 70) mithilfe der ALTER DATABASE SCOPED-KONFIGURATION auf Datenbankebene aktiviert werden.

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 AbfragehinweisUSE 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

Abfragen können gezwungen werden, die Legacy-Karte inality-Schätzung zu verwenden, ohne die Abfrage zu ändern, indem Abfragespeicher Hinweise verwendet werden.

  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 Abfragespeicher Hinweis angewendet, um die Legacy-Karte inality-Schätzung auf 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]). Derzeit ist dieses Feature 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 einige konstanten Ausdrücke frühzeitig aus, um die Abfrageleistung zu verbessern. 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 die Verwendung lokaler Variablen in Abfragen. Verwenden Sie stattdessen Parameter, Literale oder Ausdrücke in der Abfrage.

  • Beschränken Sie die Verwendung von Operatoren und Funktionen, die in eine Abfrage eingebettet sind, die einen Parameter auf diejenigen enthält, die unter "Auswertung des Kompilierungszeitausdrucks für Kardinalitätsschätzung" aufgeführt sind.

  • Stellen Sie sicher, dass nur konstanten Ausdrücke in der Bedingung Ihrer Abfrage entweder konstant gefaltet oder zur Kompilierungszeit ausgewertet werden können.

  • Wenn Sie eine lokale Variable verwenden müssen, um einen Ausdruck auszuwerten, der in einer Abfrage verwendet werden soll, sollten Sie ihn in einem anderen Bereich als der Abfrage auswerten. Es kann z. B. hilfreich sein, eine der folgenden Optionen auszuführen:

    • Übergeben Sie den Wert der Variablen an eine gespeicherte Prozedur, die 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 CE 120 versteht SQL Server möglicherweise eine Korrelation zwischen den beiden Spalten in derselben Tabelle Model und ModelVariant. 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