Leitfaden zur Architektur der Abfrageverarbeitung

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

Die SQL Server-Datenbank-Engine verarbeitet Abfragen für verschiedene Datenspeicherarchitekturen, z.B. lokale Tabellen, partitionierte Tabellen und über mehrere Server verteilte Tabellen. In den folgenden Abschnitten wird beschrieben, wie SQL Server Abfragen verarbeitet und die Wiederverwendung von Abfragen durch das Zwischenspeichern des Ausführungsplans optimiert.

Ausführungsmodi

Die SQL Server-Datenbank-Engine kann Transact-SQL-Anweisungen mit zwei verschiedenen Verarbeitungsmodi verarbeiten:

• Zeilenmodusausführung
• Batchmodusausführung

Zeilenmodusausführung

Die Zeilenmodusausführung ist eine Methode zur Abfrageverarbeitung, die mit herkömmlichen RDMBS-Tabellen verwendet wird, in denen Daten im Zeilenformat gespeichert sind. Wenn eine Abfrage ausgeführt wird und auf Daten in Rowstore-Tabellen zugreift, lesen die Operatoren der Ausführungsstruktur und die untergeordneten Operatoren jede erforderliche Zeile in allen Spalten, die im Tabellenschema angegeben wurden. Von jeder gelesenen Zeile ruft SQL Server dann die Spalten ab, die für das Resultset erforderlich sind, wie von einer SELECT-Anweisung, einem JOIN-Prädikat oder einem Filter-Prädikat referenziert.

Hinweis

Die Zeilenmodusausführung ist für OLTP-Szenarios sehr effizient, kann jedoch beim Überprüfen großer Datenmengen (z.B. in einem Data Warehousing-Szenario) weniger effizient sein.

Batchmodusausführung

DieBatchmodusausführung ist eine Methode zur Abfrageverarbeitung, die zum gleichzeitigen Abfragen mehrerer Zeilen (d.h. eines Batchs) verwendet wird. Jede Spalte innerhalb eines Batchs wird als Vektor in einem separaten Bereich des Arbeitsspeichers gespeichert. Die Batchmodusverarbeitung ist also vektorbasiert. Die Batchmodusverarbeitung verwendet ebenfalls Algorithmen, die für Mehrkern-CPUs und erhöhten Arbeitsspeicherdurchsatz bei moderner Hardware optimiert sind.

Bei ihrer Einführung war die Batchmodusausführung eng mit dem Columnstore-Speicherformat integriert und für dieses optimiert. Ab SQL Server 2019 (15.x) und in Azure SQL-Datenbank erfordert die Batchmodusausführung jedoch keine Spaltenspeicherindizes mehr. Weitere Informationen finden Sie unter Batchmodus bei Rowstore.

Bei der Batchmodusverarbeitung kommen, sofern möglich, komprimierte Daten zum Einsatz. Zugleich werden die Austauschoperatoren beseitigt, die von der Zeilenmodusausführung verwendet werden. Das Ergebnis ist eine bessere Parallelität und Leistung.

Wenn eine Abfrage im Batchmodus ausgeführt wird und auf Daten in Columnstore-Indizes zugreift, lesen die Operatoren der Ausführungsstruktur und die untergeordneten Operatoren mehrere Zeilen gleichzeitig in Spaltensegmenten. SQL Server liest nur die Spalten, die für das Ergebnis erforderlich sind, wie von einer SELECT-Anweisung, einem JOIN-Prädikat oder einem Filter-Prädikat referenziert. Weitere Informationen zu Columnstore-Indizes finden Sie unter Columnstore-Indizes: Architektur.

Hinweis

Die Batchmodusausführung ist in Data Warehousing-Szenarios, bei denen große Datenmengen gelesen und aggregiert werden, sehr effizient.

SQL-Anweisungsverarbeitung

Die Verarbeitung einer einzelnen Transact-SQL-Anweisung ist das grundlegendste Verfahren, mit dem Transact-SQL-Anweisungen von SQL Server ausgeführt werden. Die Schritte, die zur Verarbeitung einer einzelnen SELECT -Anweisung verwendet werden, die nur auf lokale Basistabellen verweist (keine Sichten oder Remotetabellen), sollen das zugrunde liegende Verfahren veranschaulichen.

Rangfolge logischer Operatoren

Wenn mehr als ein logischer Operator in einer Anweisung verwendet wird, wird NOT zuerst ausgewertet, dann AND und schließlich OR. Arithmetische (und bitweise) Operatoren werden vor logischen Operatoren verarbeitet. Weitere Informationen finden Sie unter Operator Precedence (Operatorrangfolge).

Im folgenden Beispiel ist die Color-Bedingung nur für ProductModel 21 anwendbar und nicht für ProductModel 20, weil AND Vorrang gegenüber OR hat.

SELECT ProductID, ProductModelID
FROM Production.Product
WHERE ProductModelID = 20 OR ProductModelID = 21
  AND Color = 'Red';
GO

Sie können die Bedeutung der Abfrage ändern, indem Sie durch Hinzufügen von Klammern veranlassen, dass der Operator OR zuerst ausgewertet wird. Die folgende Abfrage findet nur Produkte unter den Modellen 20 und 21, deren Farbe „red“ (rot) ist.

SELECT ProductID, ProductModelID
FROM Production.Product
WHERE (ProductModelID = 20 OR ProductModelID = 21)
  AND Color = 'Red';
GO

Die Verwendung von Klammern kann auch dann empfehlenswert sein, wenn diese nicht unbedingt erforderlich sind, da sie die Übersichtlichkeit von Abfragen verbessern und zudem die Wahrscheinlichkeit von Flüchtigkeitsfehlern verringern, die sich aus der Rangfolge der Operatorenauswertung ergeben. Die Leistung wird durch den Einsatz von Klammern nicht wesentlich beeinträchtigt. Das folgende Beispiel ist leichter zu lesen als das ursprüngliche Beispiel, obwohl sie syntaktisch übereinstimmen:

SELECT ProductID, ProductModelID
FROM Production.Product
WHERE ProductModelID = 20 OR (ProductModelID = 21
  AND Color = 'Red');
GO

Optimieren von SELECT-Anweisungen

Eine SELECT Anweisung ist nicht prozedural. Sie gibt nicht die genauen Schritte an, die der Datenbankserver zum Abrufen der angeforderten Daten verwenden soll. Dies bedeutet, dass der Datenbankserver die Anweisung analysieren muss, um das effizienteste Verfahren zum Extrahieren der angeforderten Daten zu ermitteln. Dieser Vorgang wird als Optimieren der SELECT -Anweisung bezeichnet. Die komponente, die dies tut, wird als Abfrageoptimierer bezeichnet. Die Eingaben für den Abfrageoptimierer bestehen aus der Abfrage, dem Datenbankschema (Tabellen- und Indexdefinitionen) und den Datenbankstatistiken. Die Ausgabe des Abfrageoptimierers ist ein Abfrageausführungsplan, der manchmal auch als Abfrageplan oder Ausführungsplan bezeichnet wird. Die Inhalte eines Ausführungsplans werden weiter unten in diesem Artikel ausführlicher beschrieben.

Die Ein- und Ausgaben des Abfrageoptimierers während der Optimierung einer einzelnen SELECT-Anweisung werden in folgendem Diagramm dargestellt:

Eine SELECT -Anweisung definiert lediglich Folgendes:

• Das Format des Resultsets. Dieses wird meistens in der Auswahlliste angegeben. Das endgültige Format des Resultsets wird jedoch auch von anderen Klauseln, wie z.B. ORDER BY und GROUP BY , beeinflusst.
• Die Tabellen, die die Quelldaten enthalten. Dies wird in der FROM -Klausel angegeben.
• Die logischen Beziehungen zwischen den Tabellen, die im Rahmen der SELECT -Anweisung relevant sind. Dies wird in den Verknüpfungsspezifikationen definiert, die in der WHERE Klausel oder in einer ON folgenden FROMKlausel angezeigt werden können.
• Die Bedingungen, die die Zeilen in den Quelltabellen erfüllen müssen, um für die SELECT -Anweisung qualifiziert zu sein. Diese werden in den WHERE - und HAVING -Klauseln angegeben.

In einem Abfrageausführungsplan wird Folgendes definiert:

• Die Reihenfolge des Zugriffs auf die Quelltabellen.
  In der Regel gibt es viele Abfolgen, in denen der Datenbankserver auf die Basistabellen zugreifen kann, um das Resultset zu erstellen. Wenn die SELECT -Anweisung z.B. auf drei Tabellen verweist, könnte der Datenbankserver zuerst auf TableAzugreifen, dann die Daten aus TableA verwenden, um die entsprechenden Zeilen aus TableBzu extrahieren, und dann die Daten aus TableB verwenden, um Daten aus TableCzu extrahieren. Die anderen Abfolgen, in denen der Datenbankserver auf die Tabellen zugreifen kann, lauten:
  TableC, TableB, TableAoder
  TableB, TableA, TableCoder
  TableB, TableC, TableAoder
  TableC, TableATableB

• Die Methoden, die verwendet werden, um Daten aus den einzelnen Tabellen zu extrahieren.
  Für den Zugriff auf die Daten in den einzelnen Tabellen gibt es in der Regel unterschiedliche Methoden. Wenn nur wenige Zeilen mit bestimmten Schlüsselwerten erforderlich sind, kann der Datenbankserver einen Index verwenden. Wenn alle Zeilen der Tabelle erforderlich sind, kann der Datenbankserver die Indizes übergehen und einen Tabellenscan ausführen. Wenn alle Zeilen in einer Tabelle erforderlich sind, aber ein Index vorhanden ist, dessen Schlüsselspalten sich in einer ORDER BYTabelle befinden, kann beim Durchführen einer Indexüberprüfung anstelle einer Tabellenüberprüfung eine separate Sortierung des Resultsets gespeichert werden. Wenn eine Tabelle sehr klein ist, sind Tabellenscans möglicherweise die effizienteste Methode für fast alle Zugriffe auf die Tabelle.

• Die Methoden, die für Berechnungen und zum Filtern, Aggregieren und Sortieren von Daten aus den einzelnen Tabellen verwendet werden.
  Beim Zugriff auf Daten von Tabellen aus gibt es verschiedene Methoden zum Durchführen von Berechnungen für Daten – z. B. Berechnen von skalaren Werten –, zum Aggregieren und Sortieren von Daten wie im Abfragetext definiert – z. B. bei Verwendung einer GROUP BY- oder ORDER BY-Klausel –, und zum Filtern von Daten – z. B. bei Verwendung einer WHERE- oder HAVING-Klausel.

Der Vorgang, in dessen Verlauf ein bestimmter Ausführungsplan aus einer Anzahl möglicher Ausführungspläne ausgewählt wird, wird Optimierung genannt. Der Abfrageoptimierer ist eine der wichtigsten Komponenten des Datenbank-Engine. Der Abfrageoptimierer erzeugt zwar den zusätzlichen Aufwand, um die Abfrage analysieren und einen Plan auswählen zu können, ein Vielfaches dieses Aufwands wird jedoch normalerweise dadurch eingespart, dass der Abfrageoptimierer einen effizienten Ausführungsplan auswählt. Nehmen Sie z. B. an, zwei Bauunternehmer erhalten dieselben Konstruktionszeichnungen für ein Haus. Wenn nun das eine Unternehmen zunächst einige Tage darauf verwendet, den Bau des Hauses detailliert zu planen, das andere Unternehmen jedoch sofort und ohne weitere Planung mit dem Bau des Hauses beginnt, ist es mehr als wahrscheinlich, dass das erste Unternehmen, das sich Zeit für die Planung des Projekts nimmt, den Bau des Hauses zuerst abschließen wird.

Der SQL Server-Abfrageoptimierer ist ein kostenbasierter Optimierer. Jeder denkbare Ausführungsplan verfügt über zugeordnete Kosten hinsichtlich des Umfangs der benötigten Verarbeitungsressourcen. Der Abfrageoptimierer muss die möglichen Pläne analysieren und den Plan auswählen, der die geringsten geschätzten Kosten verursacht. Einige komplexe SELECT -Anweisungen verfügen über mehrere Tausend mögliche Ausführungspläne. In diesen Fällen analysiert der Abfrageoptimierer nicht alle möglichen Kombinationen. Stattdessen werden komplexe Algorithmen verwendet, um einen Ausführungsplan zu ermitteln, dessen Kosten sich in vernünftigem Rahmen an die möglichen Mindestkosten annähern.

Der SQL Server-Abfrageoptimierer wählt nicht nur den Ausführungsplan mit den niedrigsten Ressourcenkosten aus. er wählt den Plan aus, der Ergebnisse an den Benutzer mit angemessenen Kosten in Ressourcen zurückgibt und die Ergebnisse am schnellsten zurückgibt. Für die parallele Verarbeitung einer Abfrage werden in der Regel mehr Ressourcen verwendet als für die serielle Verarbeitung, die Abfrageausführung wird jedoch schneller beendet. Der SQL Server-Abfrageoptimierer verwendet einen parallelen Ausführungsplan, um Ergebnisse zurückzugeben, wenn die Last auf dem Server nicht beeinträchtigt wird.

Der SQL Server-Abfrageoptimierer basiert auf Verteilungsstatistiken, wenn die Ressourcenkosten verschiedener Methoden zum Extrahieren von Informationen aus einer Tabelle oder einem Index geschätzt werden. Die Verteilungsstatistiken werden für Spalten und Indizes gespeichert und enthalten Informationen über die Dichte¹ der zugrunde liegenden Daten. Dies dient dazu, die Selektivität der Werte in einem bestimmten Index oder einer bestimmten Spalte zu kennzeichnen. In einer Tabelle für Autos stammen z. B. viele Autos von demselben Hersteller, jedes Auto verfügt jedoch über eine eindeutige Fahrzeugnummer. Ein Index für das VIN-Objekt weist eine höhere Selektivität auf als ein Index für den Hersteller, da „VIN“ eine niedrigere Dichte als „Hersteller“ aufweist. Wenn die Indexstatistiken nicht aktuell sind, macht der Abfrageoptimierer möglicherweise nicht die beste Wahl für den aktuellen Status der Tabelle. Weitere Informationen zu Dichten finden Sie unter Statistik.

¹ Dichte definiert die Verteilung von eindeutigen Werten, die in den Daten vorhanden sind, oder die durchschnittliche Anzahl doppelter Werte für eine bestimmte Spalte. Bei einer Verringerung der Dichte erhöht sich die Selektivität eines Werts.

Der SQL Server-Abfrageoptimierer ist wichtig, da er es dem Datenbankserver ermöglicht, die Bedingungen in der Datenbank dynamisch anzupassen, ohne dass ein Programmierer oder Datenbankadministrator Eingaben erfordert. Programmierer können sich somit darauf konzentrieren, das endgültige Ergebnis der Abfrage zu beschreiben. Sie können vertrauen, dass der SQL Server-Abfrageoptimierer bei jeder Ausführung der Anweisung einen effizienten Ausführungsplan für den Status der Datenbank erstellt.

Hinweis

SQL Server Management Studio verfügt über drei Optionen zum Anzeigen von Ausführungsplänen:

• Der geschätzte Ausführungsplan: Dieser entspricht dem kompilierten, vom Abfrageoptimierer erzeugten Plan.
• Der tatsächliche Ausführungsplan: Dieser entspricht dem kompilierten Plan und enthält zusätzlich den zugehörigen Ausführungskontext. Dies umfasst Laufzeitinformationen, die nach Abschluss der Ausführung verfügbar sind, z. B. Ausführungswarnungen oder in neueren Versionen der Datenbank-Engine, der verstrichenen und CPU-Zeit, die während der Ausführung verwendet werden.
• Die Live-Abfragestatistik: Diese entspricht dem kompilierten Plan und enthält zusätzlich den Ausführungskontext. Dies umfasst Laufzeitinformationen während des Ausführungsfortschritts, die sekündlich aktualisiert werden. Laufzeitinformationen enthalten beispielsweise die genaue Anzahl der Zeilen, die die Operatoren durchlaufen.

Verarbeiten einer SELECT-Anweisung

SQL Server führt zur Verarbeitung einer einzelnen SELECT-Anweisung die folgenden grundlegenden Schritte aus:

1. Der Parser scannt die SELECT -Anweisung und spaltet sie in ihre logischen Einheiten auf, wie z.B. Schlüsselwörter, Ausdrücke, Operatoren und Bezeichner.
2. Eine Abfragestruktur, manchmal auch Sequenzstruktur genannt, wird erstellt, die die logischen Schritte beschreibt, die für die Transformation der Quelldaten in das für das Resultset benötigte Format erforderlich sind.
3. Der Abfrageoptimierer analysiert verschiedene Arten des Zugriffs auf die Quelltabellen. Anschließend wählt er die Reihenfolge der Schritte aus, mit denen die Ergebnisse am schnellsten mithilfe möglichst weniger Ressourcen zurückgegeben werden. Die Abfragestruktur wird aktualisiert, um diese genaue Reihenfolge von Schritten aufzuzeichnen. Die endgültige, optimierte Version der Abfragestruktur wird als Ausführungsplan bezeichnet.
4. Die relationale Engine beginnt mit der Ausführung des Ausführungsplans. Während der Verarbeitung von Schritten, für die Daten aus den Basistabellen erforderlich sind, fordert die relationale Engine an, dass die Speicher-Engine die Daten aus den Rowsets übergibt, die durch die relationale Engine angefordert wurden.
5. Die relationale Engine transformiert die Daten, die von der Speicher-Engine zurückgegeben werden, in das für das Resultset definierte Format und gibt das Resultset an den Client zurück.

Konstante Faltung und Ausdrucksauswertung

SQL Server 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.

Faltbare Ausdrücke

SQL Server verwendet eine konstante Faltung mit den folgenden Ausdruckstypen:

• Arithmetische Ausdrücke, z 1 + 1 . B. und 5 / 3 * 2, die nur Konstanten enthalten.
• Logische Ausdrücke, z 1 = 1 . B. und 1 > 2 AND 3 > 4, die nur Konstanten enthalten.
• Integrierte Funktionen, die von SQL Server als faltbar angesehen werden, einschließlich CAST und CONVERT. Im Allgemeinen gilt eine systeminterne Funktion als zur Kompilierzeit reduzierbar, wenn sie ausschließlich aus Eingaben besteht – ohne weitere kontextbezogene Informationen wie SET-Optionen, Spracheinstellungen, Datenbankoptionen oder Verschlüsselungsschlüssel. Nicht deterministische Funktionen sind nicht zur Kompilierzeit reduzierbar. Deterministische integrierte Funktionen sind bis auf einige Ausnahmen zur Kompilierzeit reduzierbar.
• Deterministische Methoden von benutzerdefinierten CLR-Typen und deterministischen, benutzerdefinierten CLR-Funktionen (beginnend mit SQL Server 2012 (11.x)). Weitere Informationen finden Sie unter Reduktion konstanter Ausdrücke für benutzerdefinierte CLR-Funktionen und -Methoden.

Hinweis

Eine Ausnahme sind große Objekte. Wenn der Ausgabetyp des Faltvorgangs ein großer Objekttyp (Text,ntext, Bild, nvarchar(max), varchar(max), varbinary(max) oder XML ist, wird der Ausdruck von SQL Server nicht gefaltet.

Nicht ordnerfähige Ausdrücke

Alle anderen Ausdruckstypen können nicht zur Kompilierzeit reduziert werden. Das gilt insbesondere für folgende Arten von Ausdrücken:

• Nicht konstante Ausdrücke, wie z. B. Ausdrücke, deren Ergebnisse vom Wert einer Spalte abhängig sind.
• Ausdrücke, deren Ergebnisse von einer lokalen Variable bzw. einem lokalen Parameter abhängig sind, wie z. B. @x.
• Nicht deterministische Funktionen.
• Benutzerdefinierte Transact-SQL-Funktionen¹.
• Ausdrücke, deren Ergebnisse von Spracheinstellungen abhängig sind.
• Ausdrücke, deren Ergebnisse von SET-Optionen abhängig sind.
• Ausdrücke, deren Ergebnisse von Serverkonfigurationsoptionen abhängig sind.

¹ Vor SQL Server 2012 (11.x) waren benutzerdefinierte CLR-benutzerdefinierte CLR-Funktionen und -Methoden von benutzerdefinierten CLR-Typen nicht gefaltet.

Beispiele für faltbare und nicht foldable Konstantenausdrücke

Betrachten Sie die folgende Abfrage:

SELECT *
FROM Sales.SalesOrderHeader AS s
INNER JOIN Sales.SalesOrderDetail AS d
ON s.SalesOrderID = d.SalesOrderID
WHERE TotalDue > 117.00 + 1000.00;

Wenn die PARAMETERIZATION Datenbankoption für diese Abfrage nicht festgelegt FORCED ist, wird der Ausdruck 117.00 + 1000.00 ausgewertet und durch das Ergebnis ersetzt, 1117.00bevor die Abfrage kompiliert wird. Die Vorteile dieser Reduktion des konstanten Ausdrucks sind folgende:

• Der Ausdruck muss zur Laufzeit nicht wiederholt ausgewertet werden.
• Der durch die Auswertung des Ausdrucks erhaltene Wert wird vom Abfrageoptimierer verwendet, um die Größe des Resultsets der Teilabfrage TotalDue > 117.00 + 1000.00 zu schätzen.

Wenn es sich jedoch dbo.f um eine skalare benutzerdefinierte Funktion handelt, wird der Ausdruck dbo.f(100) nicht gefaltet, da SQL Server Ausdrücke, die benutzerdefinierte Funktionen umfassen, nicht faltet, auch wenn sie deterministisch sind. Weitere Informationen zur Parametrisierung finden Sie unter Erzwungene Parametrisierung weiter unten in diesem Artikel.

Ausdrucksauswertung

Außerdem werden bestimmte Ausdrücke, die zwar nicht zur Kompilierzeit ausgewertet werden, deren Argumente jedoch zur Kompilierzeit bekannt sind – unabhängig davon, ob es sich bei den Argumenten um Parameter oder Konstanten handelt – hinsichtlich der Größe ihrer Resultsets (Kardinalität) geschätzt. Dieser Vorgang ist ein Bestandteil des Abfrageoptimierers.

Insbesondere werden folgende integrierte Funktionen und spezielle Operatoren zur Kompilierzeit ausgewertet, wenn alle diesbezüglichen Eingaben bekannt sind: UPPER, LOWER, RTRIM, DATEPART( YY only ), GETDATE, CAST und CONVERT. Die folgenden Operatoren werden ebenfalls zur Kompilierzeit ausgewertet, wenn alle diesbezüglichen Eingaben bekannt sind:

• Arithmetische Operatoren: +, -, *, /, unäres Minus
• Logische Operatoren: AND, OR und NOT
• Vergleichsoperatoren: <, >, <=, >=, <>, LIKE, IS NULL, IS NOT NULL

Während der Kardinalitätsschätzung wertet der Abfrageoptimierer keine anderen Funktionen oder Operatoren aus.

Beispiele für die Auswertung des Kompilierungszeitausdrucks

Sehen Sie sich diese gespeicherte Prozedur an:

USE AdventureWorks2022;
GO
CREATE PROCEDURE MyProc( @d datetime )
AS
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d+1;

Während der Optimierung der SELECT-Anweisung der Prozedur versucht der Abfrageoptimierer, die erwartete Kardinalität des Resultsets für die Bedingung OrderDate > @d+1 auszuwerten. Der Ausdruck @d+1 ist nicht konstant gefaltet, da es sich um @d einen Parameter handelt. Zum Zeitpunkt der Optimierung ist der Wert dieses Parameters jedoch bekannt. Dadurch kann der Abfrageoptimierer die Größe des Resultsets genau schätzen, was zur Auswahl des optimalen Abfrageplans beiträgt.

Betrachten Sie nun ein ähnliches Beispiel, in dem jedoch @d2 durch eine lokale Variable, @d+1, ersetzt wird, und der Ausdruck statt in einer Abfrage in einer SET-Anweisung ausgewertet wird.

USE AdventureWorks2022;
GO
CREATE PROCEDURE MyProc2( @d datetime )
AS
BEGIN
  DECLARE @d2 datetime
  SET @d2 = @d+1
  SELECT COUNT(*)
  FROM Sales.SalesOrderHeader
  WHERE OrderDate > @d2
END;

Wenn die SELECT Anweisung in MyProc2 SQL Server optimiert ist, wird der Wert von @d2 nicht bekannt. Daher verwendet der Abfrageoptimierer eine Standardschätzung für die Selektivität von OrderDate > @d2 (in diesem Fall 30 Prozent).

Verarbeiten anderer Anweisungen

Die zuvor beschriebenen grundlegenden Schritte für die Verarbeitung einer SELECT-Anweisung gelten auch für andere Transact-SQL-Anweisungen wie INSERT, UPDATE und DELETE. UPDATE - und DELETE -Anweisungen müssen sich auf die Gruppe von Zeilen beziehen, die geändert bzw. gelöscht werden soll. Der Vorgang zum Identifizieren dieser Zeilen ist der gleiche Vorgang, der zum Identifizieren der Quellzeilen verwendet wird, die einen Beitrag zum Resultset einer SELECT -Anweisung leisten. Die UPDATE anweisungen INSERT können beide eingebettete SELECT Anweisungen enthalten, die die zu aktualisierenden oder eingefügten Datenwerte bereitstellen.

Sogar DDL-Anweisungen (Data Definition Language, Datendefinitionssprache), wie z.B. CREATE PROCEDURE oder ALTER TABLE, werden letztendlich in eine Folge relationaler Operationen aufgelöst, die für die Systemkatalogtabellen und manchmal (wie bei ALTER TABLE ADD COLUMN) auch für die Datentabellen ausgeführt werden.

Arbeitstabellen

Das relationale Modul muss möglicherweise eine Arbeitstabelle erstellen, um einen logischen Vorgang auszuführen, der in einer Transact-SQL-Anweisung angegeben ist. Arbeitstabellen sind interne Tabellen, die zum Speichern von Zwischenergebnissen verwendet werden. Arbeitstabellen werden für bestimmte GROUP BY-, ORDER BY- oder UNION -Abfragen generiert. Wenn beispielsweise eine ORDER BY Klausel auf Spalten verweist, die nicht von Indizes abgedeckt werden, muss das relationale Modul möglicherweise eine Arbeitstabelle generieren, um das Resultset in der angeforderten Reihenfolge zu sortieren. Arbeitstabellen werden mitunter auch als Spool-Speicher verwendet, die vorübergehend das Ergebnis der Ausführung eines Teils eines Abfrageplans aufnehmen. Arbeitstabellen werden in tempdb erstellt und automatisch wieder gelöscht, sobald sie nicht mehr benötigt werden.

Ansichtsauflösung

Der SQL Server-Abfrageprozessor behandelt indizierte und nicht indizierte Sichten unterschiedlich:

• Die Zeilen einer indizierten Sicht werden in der Datenbank in demselben Format wie eine Tabelle gespeichert. Wenn sich der Abfrageoptimierer entscheidet, eine indizierte Sicht in einem Abfrageplan zu verwenden, wird die indizierte Sicht auf die gleiche Weise wie eine Basistabelle behandelt.
• Nur die Definition einer nicht indizierten Sicht wird gespeichert, nicht die Zeilen der Sicht. Der Abfrageoptimierer nimmt die Logik aus der Sichtdefinition in den Ausführungsplan auf, den er für die Transact-SQL-Anweisung erstellt, die auf die nicht indizierte Sicht verweist.

Die vom SQL Server-Abfrageoptimierer verwendete Logik, um zu entscheiden, wann eine indizierte Ansicht verwendet werden soll, ähnelt der Logik, die verwendet wird, um zu entscheiden, wann ein Index für eine Tabelle verwendet werden soll. Wenn die Daten in der indizierten Sicht die gesamte oder einen Teil der Transact-SQL-Anweisung erfüllen und der Abfrageoptimierer ermittelt, dass ein Index für die Sicht der Zugriffspfad mit den geringsten Kosten ist, wählt der Abfrageoptimierer den Index unabhängig davon aus, ob im Namen der Abfrage auf die Sicht verwiesen wird.

Wenn eine Transact-SQL-Anweisung auf eine nicht indizierte Sicht verweist, analysieren der Parser und der Abfrageoptimierer sowohl die Quelle der Transact-SQL-Anweisung als auch die Quelle der Sicht und lösen sie dann zu einem einzelnen Ausführungsplan auf. Es gibt keinen Plan für die Transact-SQL-Anweisung und einen separaten Plan für die Ansicht.

Nehmen Sie z. B. an, dass die folgende Sicht verwendet wird:

USE AdventureWorks2022;
GO
CREATE VIEW EmployeeName AS
SELECT h.BusinessEntityID, p.LastName, p.FirstName
FROM HumanResources.Employee AS h
JOIN Person.Person AS p
  ON h.BusinessEntityID = p.BusinessEntityID;
GO

Von dieser Sicht ausgehend führen die beiden folgenden Transact-SQL-Anweisungen die gleichen Vorgänge für die Basistabellen aus und erzeugen identische Ergebnisse:

/* SELECT referencing the EmployeeName view. */
SELECT LastName AS EmployeeLastName, SalesOrderID, OrderDate
FROM AdventureWorks2022.Sales.SalesOrderHeader AS soh
JOIN AdventureWorks2022.dbo.EmployeeName AS EmpN
  ON (soh.SalesPersonID = EmpN.BusinessEntityID)
WHERE OrderDate > '20020531';

/* SELECT referencing the Person and Employee tables directly. */
SELECT LastName AS EmployeeLastName, SalesOrderID, OrderDate
FROM AdventureWorks2022.HumanResources.Employee AS e
JOIN AdventureWorks2022.Sales.SalesOrderHeader AS soh
  ON soh.SalesPersonID = e.BusinessEntityID
JOIN AdventureWorks2022.Person.Person AS p
  ON e.BusinessEntityID =p.BusinessEntityID
WHERE OrderDate > '20020531';

Durch die SQL Server Management Studio-Showplanfunktion wird deutlich, dass die relationale Engine für beide SELECT-Anweisungen denselben Ausführungsplan erstellt.

Verwenden von Hinweisen mit Ansichten

Hinweise, die in Ansichten in einer Abfrage platziert werden, können mit anderen Hinweisen in Konflikt stehen, die ermittelt werden, wenn die Ansicht erweitert wird, um auf ihre Basistabellen zuzugreifen. Wenn das passiert, gibt die Abfrage einen Fehler zurück. Angenommen, die folgende Sicht enthält einen Tabellenhinweis in ihrer Definition:

USE AdventureWorks2022;
GO
CREATE VIEW Person.AddrState WITH SCHEMABINDING AS
SELECT a.AddressID, a.AddressLine1,
    s.StateProvinceCode, s.CountryRegionCode
FROM Person.Address a WITH (NOLOCK), Person.StateProvince s
WHERE a.StateProvinceID = s.StateProvinceID;

Nehmen Sie nun an, dass die folgende Abfrage eingegeben wird:

SELECT AddressID, AddressLine1, StateProvinceCode, CountryRegionCode
FROM Person.AddrState WITH (SERIALIZABLE)
WHERE StateProvinceCode = 'WA';

Die Abfrage erzeugt einen Fehler, weil der SERIALIZABLE -Hinweis, der für die Person.AddrState -Sicht in der Abfrage angewendet wird, an die beiden Tabellen Person.Address und Person.StateProvince in der Sicht weitergegeben wird, wenn diese erweitert wird. Das Erweitern der Sicht legt jedoch außerdem den NOLOCK -Hinweis für Person.Addressoffen. Da die SERIALIZABLE - und NOLOCK -Hinweise einen Konflikt verursachen, ist die sich ergebende Abfrage falsch.

Die PAGLOCK-, NOLOCK-, ROWLOCK-, TABLOCK- oder TABLOCKX -Tabellenhinweise verursachen Konflikte miteinander, genau wie die HOLDLOCK-, NOLOCK-, READCOMMITTED-, REPEATABLEREAD-, SERIALIZABLE -Tabellenhinweise.

Hinweise können über die Ebenen geschachtelter Sichten weitergegeben werden. Angenommen, eine Abfrage wendet den HOLDLOCK -Hinweis auf eine v1-Sicht an. Wenn v1 erweitert wird, wird erkennbar, dass die Sicht v2 Teil ihrer Definition ist. Die Definition vonv2enthält einen NOLOCK -Hinweis für eine der Basistabellen der Sicht. Diese Tabelle erbt jedoch außerdem den HOLDLOCK -Hinweis für die Sicht v1von der Abfrage. Da die NOLOCK - und HOLDLOCK -Hinweise einen Konflikt verursachen, führt die Abfrage zu einem Fehler.

Wenn der FORCE ORDER -Hinweis in einer Abfrage verwendet wird, die eine Sicht enthält, wird die Joinreihenfolge der Tabellen innerhalb der Sicht durch die Position der Sicht im sortierten Konstrukt festgelegt. Die folgende Abfrage trifft z. B. eine Auswahl aus drei Tabellen und einer Sicht:

SELECT * FROM Table1, Table2, View1, Table3
WHERE Table1.Col1 = Table2.Col1
    AND Table2.Col1 = View1.Col1
    AND View1.Col2 = Table3.Col2;
OPTION (FORCE ORDER);

Außerdem ist View1 wie im folgenden Beispiel gezeigt definiert:

CREATE VIEW View1 AS
SELECT Colx, Coly FROM TableA, TableB
WHERE TableA.ColZ = TableB.Colz;

Die Joinreihenfolge im Abfrageplan lautet Table1, Table2, TableA, TableB, Table3.

Auflösen von Indizes in Ansichten

Wie bei jedem Index wählt SQL Server aus, eine indizierte Ansicht im Abfrageplan nur zu verwenden, wenn der Abfrageoptimierer feststellt, dass dies von Vorteil ist.

Indizierte Sichten können in jeder Edition von SQL Server erstellt werden. In einigen Editionen einiger älterer Versionen von SQL Server berücksichtigt der Abfrageoptimierer automatisch die indizierte Ansicht. In einigen Editionen einiger älteren Versionen von SQL Server muss der NOEXPAND Tabellenhinweis verwendet werden, um eine indizierte Ansicht zu verwenden. Vor SQL Server 2016 (13.x) Service Pack 1 wurde die automatische Verwendung einer indizierten Sicht durch den Abfrageoptimierer nur in bestimmten Editionen von SQL Server unterstützt. Da alle Editionen die automatische Verwendung einer indizierten Ansicht unterstützen. Azure SQL-Datenbank und Azure SQL Managed Instance unterstützen auch die automatische Verwendung indizierter Ansichten ohne Angabe des NOEXPAND-Hinweises.

Der SQL Server-Abfrageoptimierer verwendet eine indizierte Ansicht, wenn die folgenden Bedingungen erfüllt sind:

• Diese Sitzungsoptionen sind auf :ON
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • ARITHABORT
  • CONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER
• Die NUMERIC_ROUNDABORT -Sitzungsoption ist auf OFF festgelegt.
• Der Abfrageoptimierer findet eine Übereinstimmung zwischen den Ansichtsindexspalten und Elementen in der Abfrage, z. B. den folgenden:
  • Suchbedingungsprädikate in der WHERE-Klausel
  • Joinvorgänge
  • Aggregatfunktionen
  • GROUP BY -Klauseln
  • Tabellenverweise
• Die geschätzten Kosten für das Verwenden des Indexes sind die niedrigsten Kosten aller durch den Abfrageoptimierer berücksichtigten Zugriffsmechanismen.
• Für jede Tabelle, auf die in der Abfrage verwiesen wird (entweder direkt oder durch Erweitern einer Sicht zum Zugriff auf die zugrunde liegenden Tabellen), die einem Tabellenverweis in der indizierten Sicht entspricht, muss derselbe Satz von Hinweisen in der Abfrage angewendet werden.

Hinweis

Die READCOMMITTED - und READCOMMITTEDLOCK -Hinweise werden in diesem Kontext immer als unterschiedliche Hinweise angesehen, unabhängig von der aktuellen Transaktionsisolationsstufe.

Abweichend von den Anforderungen für die SET-Optionen und Tabellenhinweise verwendet der Abfrageoptimierer hier dieselben Regeln, mit denen er ermittelt, ob ein Tabellenindex eine Abfrage erfüllt. In der zu verwendenden Abfrage für eine indizierte Sicht muss nichts weiter angegeben werden.

Eine Abfrage muss nicht explizit auf eine indizierte Ansicht in der FROM Klausel für den Abfrageoptimierer verweisen, um die indizierte Ansicht zu verwenden. Falls die Abfrage Verweise auf Spalten in den Basistabellen enthält, die auch in der indizierten Sicht vorhanden sind, und der Abfrageoptimierer schätzt, dass das Verwenden der indizierten Sicht den kostengünstigsten Zugriffsmechanismus darstellt, wählt der Abfrageoptimierer die indizierte Sicht aus. Die Vorgehensweise ist dabei ähnlich wie bei der Auswahl von Basistabellenindizes, wenn in einer Abfrage nicht direkt auf diese verwiesen wird. Der Abfrageoptimierer kann die Ansicht auswählen, wenn sie Spalten enthält, auf die nicht von der Abfrage verwiesen wird, solange die Ansicht die niedrigste Kostenoption für die Abdeckung einer oder mehrerer der in der Abfrage angegebenen Spalten bietet.

Der Abfrageoptimierer behandelt eine indizierte Sicht, auf die in der FROM-Klausel verwiesen wird, als Standardsicht. Der Abfrageoptimierer erweitert am Beginn des Optimierungsprozesses die Definition der Sicht in die Abfrage. Dann erfolgt der Abgleich der indizierten Sicht. Die indizierte Ansicht kann im endgültigen Ausführungsplan verwendet werden, der vom Abfrageoptimierer ausgewählt wurde, oder stattdessen kann der Plan erforderliche Daten aus der Ansicht materialisieren, indem auf die Basistabellen zugegriffen wird, auf die in der Ansicht verwiesen wird. Der Abfrageoptimierer wählt die kostengünstigste Alternative aus.

Verwenden von Hinweisen mit indizierten Ansichten

Sie können verhindern, dass Sichtindizes für eine Abfrage verwendet werden, indem Sie den EXPAND VIEWS -Abfragehinweis verwenden oder indem Sie mit dem NOEXPAND -Tabellenhinweis die Verwendung eines Indexes für eine indizierte Sicht erzwingen, die in der FROM -Klausel einer Abfrage angegeben ist. Sie sollten jedoch den Abfrageoptimierer für jede Abfrage dynamisch ermitteln lassen, welches die besten Zugriffsmethoden sind. Verwenden Sie EXPAND und NOEXPAND nur in bestimmten Fällen, wenn Tests gezeigt haben, dass durch sie die Leistung deutlich gesteigert wird.

• Die Option EXPAND VIEWS gibt an, dass der Abfrageoptimierer für die gesamte Abfrage keine Sichtindizes verwendet.

• Wenn NOEXPAND für eine Sicht angegeben wird, zieht der Abfrageoptimierer die Verwendung sämtlicher Indizes in Erwägung, die für die Sicht definiert sind. NOEXPAND mit der optionalen INDEX()-Klausel zwingt den Abfrageoptimierer, die angegebenen Indizes zu verwenden. NOEXPAND kann nur für eine indizierte Ansicht angegeben werden und kann für eine Ansicht nicht indiziert werden. Vor SQL Server 2016 (13.x) Service Pack 1 wurde die automatische Verwendung einer indizierten Sicht durch den Abfrageoptimierer nur in bestimmten Editionen von SQL Server unterstützt. Da alle Editionen die automatische Verwendung einer indizierten Ansicht unterstützen. Azure SQL-Datenbank und Azure SQL Managed Instance unterstützen auch die automatische Verwendung indizierter Ansichten ohne Angabe des NOEXPAND-Hinweises.

Wenn weder NOEXPAND noch EXPAND VIEWS in einer Abfrage angegeben ist, die eine Sicht enthält, wird die Sicht erweitert, um auf die zugrunde liegenden Tabellen zuzugreifen. Wenn die Abfrage, die die Sicht bildet, irgendwelche Tabellenhinweise enthält, werden diese Hinweise auch an die zugrunde liegenden Tabellen weitergegeben. (Detaillierte Informationen zu diesem Vorgang finden Sie unter „Sichtauflösung“.) Solange die der Sicht zugrunde liegenden Tabellen identische Sätze von Hinweisen besitzen, kommt die Abfrage für den Abgleich mit einer indizierten Sicht infrage. Zumeist stimmen diese Hinweise miteinander überein, da sie direkt aus der Sicht vererbt werden. Wenn die Abfrage jedoch auf Tabellen anstelle von Ansichten verweist und die direkt auf diese Tabellen angewendeten Hinweise nicht identisch sind, ist eine solche Abfrage nicht für den Abgleich mit einer indizierten Ansicht geeignet. Wenn die INDEXIndizierungstabellen TABLOCKXUPDLOCKPAGLOCKROWLOCKoder XLOCK Hinweise auf die Tabellen angewendet werden, auf die nach der Ansichtserweiterung verwiesen wird, ist die Abfrage nicht für den Abgleich indizierter Ansichten berechtigt.

Wenn ein Tabellenhinweis in Form von INDEX (index_val[ ,...n] ) Verweisen auf eine Ansicht in einer Abfrage angezeigt wird und Sie nicht auch den NOEXPAND Hinweis angeben, wird der Indexhinweis ignoriert. Zum Angeben eines bestimmten Indexes verwenden Sie NOEXPAND.

Allgemein gilt: Wenn der Abfrageoptimierer eine indizierte Sicht mit einer Abfrage abgleicht, werden alle für die Tabellen oder Sichten in der Abfrage angegebenen Hinweise direkt auf die indizierte Sicht angewendet. Wenn der Abfrageoptimierer sich entscheidet, keine indizierte Sicht zu verwenden, werden alle Hinweise direkt zu den Tabellen weitergegeben, auf die in der Sicht verwiesen wird. Weitere Informationen finden Sie unter „Sichtauflösung“. Diese Verteilung gilt nicht für Verknüpfungshinweise. Diese werden ausschließlich an ihrer ursprünglichen Position in der Abfrage angewendet. Joinhinweise werden vom Abfrageoptimierer beim Abgleich von Abfragen zu indizierten Sichten nicht berücksichtigt. Wenn ein Abfrageplan eine indizierte Ansicht verwendet, die einem Teil einer Abfrage entspricht, die einen Verknüpfungshinweis enthält, wird der Verknüpfungshinweis nicht im Plan verwendet.

In den Definitionen indizierter Sichten sind Hinweise nicht zulässig. In den Kompatibilitätsmodi 80 und höher ignoriert SQL Server die in den Definitionen indizierter Sichten enthaltenen Hinweise, wenn diese verwaltet werden oder wenn Abfragen ausgeführt werden, in denen indizierte Sichten verwendet werden. Obwohl die Verwendung von Hinweisen in indizierten Ansichtsdefinitionen keinen Syntaxfehler im 80-Kompatibilitätsmodus erzeugt, werden sie ignoriert.

Weitere Informationen finden Sie unter Tabellenhinweise (Transact-SQL).

Auflösen verteilter partitionierte Ansichten

Der SQL Server-Abfrageprozessor optimiert die Leistung von verteilten partitionierten Sichten. Der wichtigste Aspekt bei der Leistung von verteilten partitionierten Sichten ist das Minimieren der Datenmenge, die zwischen den Mitgliedsservern übertragen wird.

SQL Server erstellt intelligente, dynamische Pläne, in denen verteilte Abfragen effizient für den Zugriff auf Daten in Remotemitgliedstabellen verwendet werden:

• Zunächst verwendet der Abfrageprozessor OLE DB, um die Definitionen der CHECK-Einschränkungen aus jeder Mitgliedstabelle abzurufen. Dadurch kann der Abfrageprozessor die Verteilung der Schlüsselwerte auf die Mitgliedstabellen zuordnen.
• Der Abfrageprozessor vergleicht die Schlüsselbereiche, die in der WHERE-Klausel einer Transact-SQL-Anweisung angegeben sind, mit der Zuordnung, die die Verteilung der Zeilen in den Mitgliedstabellen anzeigt. Anschließend erstellt der Abfrageprozessor einen Abfrageausführungsplan, der mithilfe von verteilten Abfragen nur die Remotezeilen abruft, die zum Ausführen der Transact-SQL-Anweisung erforderlich sind. Darüber hinaus wird der Ausführungsplan so erstellt, dass alle Zugriffe auf Remotemitgliedstabellen, entweder für Daten oder Metadaten, so lange verzögert werden, bis die Informationen benötigt werden.

Betrachten Sie beispielsweise ein System, bei dem eine Customers Tabelle auf Server1 (CustomerID von 1 bis 3299999), Server2 (CustomerID von 3300000 bis 6599999) und Server3 (CustomerID von 6600000 bis 9999999) partitioniert wird.

Stellen Sie sich den Ausführungsplan vor, der für diese auf Server1 ausgeführte Abfrage erstellt wird:

SELECT *
FROM CompanyData.dbo.Customers
WHERE CustomerID BETWEEN 3200000 AND 3400000;

Der Ausführungsplan für diese Abfrage extrahiert die Zeilen mit CustomerID -Schlüsselwerten von 3200000 bis 3299999 aus der lokalen Mitgliedstabelle und gibt eine verteilte Abfrage aus, um die Zeilen mit Schlüsselwerten von 3300000 bis 3400000 von Server2 abzurufen.

Der Abfrageprozessor von SQL Server kann zudem eine dynamische Logik in die Abfrageausführungspläne für Transact-SQL-Anweisungen integrieren, bei denen die Schlüsselwerte nicht bekannt sind, wenn der Plan erstellt werden muss. Sehen Sie sich z.B. diese gespeicherte Prozedur an:

CREATE PROCEDURE GetCustomer @CustomerIDParameter INT
AS
SELECT *
FROM CompanyData.dbo.Customers
WHERE CustomerID = @CustomerIDParameter;

SQL Server kann nicht vorhersagen, welcher Schlüsselwert jedes Mal vom @CustomerIDParameter Parameter bereitgestellt wird, wenn die Prozedur ausgeführt wird. Da der Schlüsselwert nicht vorhergesagt werden kann, kann der Abfrageprozessor auch nicht vorhersagen, auf welche Membertabelle zugegriffen werden muss. Wegen dieses Aspekts erstellt SQL Server einen Ausführungsplan mit Bedingungslogik (sogenannte dynamische Filter), um zu steuern, auf welche Mitgliedstabelle basierend auf den Eingabeparameterwerten zugegriffen wird. Angenommen die gespeicherte Prozedur GetCustomer wurde für Server1 ausgeführt, dann kann die Logik des Ausführungsplans wie folgt dargestellt werden:

IF @CustomerIDParameter BETWEEN 1 and 3299999
   Retrieve row from local table CustomerData.dbo.Customer_33
ELSE IF @CustomerIDParameter BETWEEN 3300000 and 6599999
   Retrieve row from linked table Server2.CustomerData.dbo.Customer_66
ELSE IF @CustomerIDParameter BETWEEN 6600000 and 9999999
   Retrieve row from linked table Server3.CustomerData.dbo.Customer_99

SQL Server erstellt diese dynamischen Ausführungspläne manchmal sogar für nicht parametrisierte Abfragen. Der Abfrageoptimierer kann eine Abfrage parametrisieren, sodass der Ausführungsplan wiederverwendet werden kann. Falls der Abfrageoptimierer eine Abfrage parametrisiert, die auf eine partitionierte Sicht verweist, kann der Abfrageoptimierer nicht mehr davon ausgehen, dass die erforderlichen Zeilen aus einer bestimmten Basistabelle stammen. In diesem Fall muss der Optimierer dynamische Filter im Ausführungsplan verwenden.

Gespeicherte Prozedur und Auslösen der Ausführung

SQL Server speichert nur die Quelle für gespeicherte Prozeduren und Trigger. Wenn eine gespeicherte Prozedur oder ein Trigger das erste Mal ausgeführt wird, wird die Quelle zu einem Ausführungsplan kompiliert. Wenn die gespeicherte Prozedur oder der Trigger erneut ausgeführt wird, bevor der Ausführungsplan aus dem Arbeitsspeicher entfernt wurde, erkennt die relationale Engine den vorhandenen Plan und verwendet ihn erneut. Wenn der Plan aus dem Arbeitsspeicher entfernt wurde, wird ein neuer Plan erstellt. Dieser Vorgang ist mit dem Verfahren vergleichbar, das SQL Server für alle Transact-SQL-Anweisungen anwendet. Der wesentliche Leistungsvorteil, den gespeicherte Prozeduren und Trigger in SQL Server im Vergleich zu Batches dynamischer Transact-SQL besitzen, besteht darin, dass ihre Transact-SQL-Anweisungen immer identisch sind. Aus diesem Grund können sie durch die relationale Engine auf einfache Weise vorhandenen Ausführungsplänen zugeordnet werden. Pläne für gespeicherte Prozeduren und Trigger können einfach erneut verwendet werden.

Der Ausführungsplan für gespeicherte Prozeduren und Trigger wird getrennt von dem Ausführungsplan für den Batch ausgeführt, der die gespeicherte Prozedur aufruft oder den Trigger auslöst. Dadurch können die Ausführungspläne für gespeicherte Prozeduren und Trigger mehrmals erneut verwendet werden.

Zwischenspeichern und Wiederverwenden von Ausführungsplanen

SQL Server verfügt über einen Arbeitsspeicherpool, der zum Speichern von Ausführungsplänen und von Datenpuffern verwendet wird. Der Prozentsatz des Pools, der entweder für Ausführungspläne oder für Datenpuffer zugeordnet wird, verändert sich dynamisch in Abhängigkeit vom Status des Systems. Der Teil des Arbeitsspeicherpools, der zum Speichern von Ausführungsplänen verwendet wird, wird Plancache genannt.

Der Plancache enthält zwei Speicher für alle kompilierten Pläne:

• Der Cache für Objektpläne (OBJCP) wird für Pläne verwendet, die sich auf persistente Objekte beziehen (gespeicherte Prozeduren, Funktionen und Auslöser).
• Der Cache für SQL-Pläne (SQLCP) wird für Pläne verwendet, die sich auf automatisch parametrisierte, dynamische oder vorbereitete Abfragen beziehen.

Die folgende Abfrage stellt Informationen zur Arbeitsspeicherauslastung für diese zwei Caches bereit:

SELECT * FROM sys.dm_os_memory_clerks
WHERE name LIKE '%plans%';

Hinweis

Der Plancache verfügt über zwei zusätzliche Speicher, die nicht zum Speichern von Plänen verwendet werden:

• Der Cache für Bound Trees (PHDR) wird für Datenstrukturen während der Plankompilierung für Ansichten, Einschränkungen und Standardwerte verwendet. Diese Strukturen werden „Bound Trees“ (Gebundene Strukturen) oder „Algebrizer Trees“ (Algebrizerstrukturen) genannt.
• Der Cache für erweiterte gespeicherte Prozedur (XPROC) wird für vordefinierte Systemprozeduren wie sp_executeSql oder xp_cmdshell verwendet, die mithilfe einer DLL-Datei und nicht mit Transact-SQL-Anweisungen definiert werden. Die zwischengespeicherte Struktur enthält nur den Funktionsnamen und den Namen der DLL-Datei, in der die Prozedur implementiert wird.

SQL Server-Ausführungspläne weisen die folgenden Hauptkomponenten auf:

• Kompilierter Plan (oder Abfrageplan)
  Der vom Kompilierungsprozess erstellte Abfrageplan ist größtenteils eine wiedereintrittsfähige, schreibgeschützte Datenstruktur, die von einer beliebigen Anzahl an Benutzern verwendet werden kann. Diese speichert Informationen über:

  • Physische Operatoren, die den von logischen Operatoren beschriebenen Vorgang implementieren.

  • Die Reihenfolge dieser Operatoren, die bestimmt, in welcher Reihenfolge auf Daten zugegriffen, gefiltert und aggregiert werden.

  • Die Anzahl der geschätzten Zeilen, die die Operatoren durchlaufen.

    Hinweis

    In neueren Versionen des Datenbank-Engine werden auch Informationen zu den Statistikobjekten gespeichert, die für Die Kardinalitätsschätzung verwendet wurden.

  • Welche Unterstützungsobjekte erstellt werden müssen, z . B. Arbeitstabellen oder Arbeitsdateien in tempdb. Im Abfrageplan werden keine Informationen über den Benutzerkontext oder die Laufzeit gespeichert. Im Arbeitsspeicher befinden sich immer nur eine oder zwei Kopien des Abfrageplans: eine Kopie für alle seriellen Ausführungen und eine weitere für alle parallelen Ausführungen. Die parallele Kopie deckt alle parallelen Ausführungen ab, und zwar unabhängig von ihrem Grad an Parallelität.

• Ausführungskontext
  Jeder Benutzer, der die Abfrage zurzeit ausführt, verfügt über eine Datenstruktur mit den Daten, die für diese Ausführung spezifisch sind, z. B. Parameterwerte. Diese Datenstruktur wird als Ausführungskontext bezeichnet. Die Ausführungskontextdatenstrukturen werden wiederverwendet, aber ihre Inhalte sind nicht vorhanden. Wenn ein anderer Benutzer dieselbe Abfrage ausführt, werden die Datenstrukturen mit dem Kontext für den neuen Benutzer nochmal initialisiert.

  

Wenn eine Transact-SQL-Anweisung in SQL Server ausgeführt wird, durchsucht die Datenbank-Engine zunächst den Plancache, um zu überprüfen, ob ein vorhandener Ausführungsplan für die gleiche Transact-SQL-Anweisung vorhanden ist. Die Transact-SQL-Anweisung wird als vorhanden qualifiziert, wenn sie exakt einer zuvor ausgeführten Transact-SQL-­Anweisung mit einem zwischengespeicherten Plan entspricht. SQL Server verwendet sämtliche vorhandenen Pläne wieder, die hierbei gefunden werden, und spart somit den Aufwand für das erneute Kompilieren der Transact-SQL-Anweisung. Wenn kein Ausführungsplan vorhanden ist, generiert SQL Server einen neuen Ausführungsplan für die Abfrage.

Hinweis

Die Ausführungspläne für einige Transact-SQL-Anweisungen werden nicht im Plancache beibehalten. Das gilt beispielsweise für Anweisungen für Massenvorgänge, die in Rowstore ausgeführt werden, oder für Anweisungen mit Zeichenfolgenliteralen mit einer Größe von mehr als 8 KB. Diese Pläne sind nur vorhanden, während die Abfrage ausgeführt wird.

SQL Server verwendet einen effizienten Algorithmus, um vorhandene Ausführungspläne für bestimmte Transact-SQL-Anweisungen zu suchen. In den meisten Systemen können durch die Wiederverwendung vorhandener Pläne anstelle des erneuten Kompilierens jeder Transact-SQL-Anweisung mehr Ressourcen eingespart werden als für den Scan nach vorhandenen Plänen erforderlich sind.

Die Algorithmen, die Transact-SQL-Anweisungen mit vorhandenen, nicht verwendeten Ausführungsplänen im Plancache vergleichen, erfordern, dass alle Objektverweise vollqualifiziert sind. Angenommen, Person ist das Standardschema für den Benutzer, der die unten angegebenen SELECT-Anweisungen ausführt. In diesem Beispiel ist es zwar nicht erforderlich, dass die Tabelle für die Person Ausführung voll qualifiziert ist, bedeutet dies jedoch, dass die zweite Anweisung nicht mit einem vorhandenen Plan übereinstimmt, aber der dritte entspricht:

USE AdventureWorks2022;
GO
SELECT * FROM Person;
GO
SELECT * FROM Person.Person;
GO
SELECT * FROM Person.Person;
GO

Das Ändern einer der folgenden SET-Optionen für eine bestimmte Ausführung wirkt sich auf die Möglichkeit der Wiederverwendung von Plänen aus, da die Datenbank-Engine eine konstante Faltung durchführt und diese Optionen sich auf die Ergebnisse solcher Ausdrücke auswirken:

ANSI_NULL_DFLT_OFF

FORCEPLAN

ARITHABORT

DATEFIRST

ANSI_PADDING

NUMERIC_ROUNDABORT

ANSI_NULL_DFLT_ON

LANGUAGE

CONCAT_NULL_YIELDS_NULL

DATEFORMAT

ANSI_WARNINGS

QUOTED_IDENTIFIER

ANSI_NULLS

NO_BROWSETABLE

ANSI_DEFAULTS

Zwischenspeichern mehrerer Pläne für dieselbe Abfrage

Abfragen und Ausführungspläne sind in der Datenbank-Engine eindeutig identifizierbar, ähnlich wie ein Fingerabdruck:

• Der Abfrageplanhash ist ein binärer Hashwert, der im Ausführungsplan für eine jeweilige Abfrage berechnet und dann zur eindeutigen Identifizierung ähnlicher Ausführungspläne verwendet wird.
• Der Abfragehash ist ein binärer Hashwert, der für den Transact-SQL-Text einer Abfrage berechnet und zur eindeutigen Identifizierung von Abfragen verwendet wird.

Ein kompilierter Plan kann mithilfe eines Planhandles aus dem Plancache abgerufen werden. Dies ist ein vorübergehender Bezeichner, der nur konstant bleibt, während der Plan sich im Cache befindet. Der Planhandle ist ein Hashwert, der vom kompilierten Plan des gesamten Batches abgeleitet wurde. Der Planhandle für einen kompilierten Plan bleibt gleich, auch wenn mindestens eine Anweisung im Batch neu kompiliert wird.

Hinweis

Wenn ein Plan für mehrere Anweisungen kompiliert wurde, können Sie den Plan für einzelne Anweisungen im Batch mithilfe des Planhandles und der Anweisungsoffsets abrufen. Die dynamische Verwaltungssicht sys.dm_exec_requests enthält die Spalten statement_start_offset und statement_end_offset für alle Datensätze, die auf die aktuell ausgeführte Anweisung eines Batches oder persistenten Objekts verweisen, das derzeit ausgeführt wird. Weitere Informationen finden Sie unter sys.dm_exec_requests (Transact-SQL). Die dynamische Verwaltungssicht sys.dm_exec_query_stats enthält diese Spalten für alle Datensätze, die auf die Position einer Anweisung im Batch oder persistenten Objekt verweisen. Weitere Informationen finden Sie unter sys.dm_exec_query_stats (Transact-SQL).

Der tatsächliche Transact-SQL-Text eines Batchs wird in einem vom Plancache getrennten Speicherbereich gespeichert, der als SQL Manager-Cache (SQLMGR) bezeichnet wird. Der Transact-SQL-Text für einen kompilierten Plan kann mithilfe eines SQL-Handles aus dem SQL Manager-Cache abgerufen werden. Dieses Handle ist ein vorübergehender Bezeichner, der nur konstant bleibt, solange sich mindestens ein Plan im Plancache befindet, der auf ihn verweist. Der SQL-Handle ist ein Hashwert, der vom gesamten Batchtext abgeleitet wird und ist für alle Batches immer eindeutig.

Hinweis

Wie bei einem kompilierten Plan wird der Transact-SQL-Text pro Batch mitsamt den Kommentaren gespeichert. Der SQL-Handle enthält den MD5-Hash des gesamten Batchtexts und ist für alle Batches immer eindeutig.

Die folgende Abfrage bietet Informationen über die Arbeitsspeicherauslastung für den SQL Manager-Cache:

SELECT * FROM sys.dm_os_memory_objects
WHERE type = 'MEMOBJ_SQLMGR';

Zwischen einem SQL-Handle und Planhandles besteht eine 1:n-Beziehung. Eine solche Bedingung liegt vor, wenn sich der Cacheschlüssel für die kompilierten Pläne unterscheidet. Dies kann aufgrund einer Änderung der SET-Optionen zwischen zwei Ausführungen desselben Batches auftreten.

Sehen Sie sich die folgende gespeicherte Prozedur an:

USE WideWorldImporters;
GO
CREATE PROCEDURE usp_SalesByCustomer @CID int
AS
SELECT * FROM Sales.Customers
WHERE CustomerID = @CID
GO

SET ANSI_DEFAULTS ON
GO

EXEC usp_SalesByCustomer 10
GO

Überprüfen Sie, was Sie mithilfe der folgenden Abfrage im Plancache ermitteln können:

SELECT cp.memory_object_address, cp.objtype, refcounts, usecounts,
    qs.query_plan_hash, qs.query_hash,
    qs.plan_handle, qs.sql_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle)
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle)
INNER JOIN sys.dm_exec_query_stats AS qs ON qs.plan_handle = cp.plan_handle
WHERE text LIKE '%usp_SalesByCustomer%'
GO

Hier ist das Resultset.

memory_object_address    objtype   refcounts   usecounts   query_plan_hash    query_hash
---------------------   -------  ---------  ---------  ------------------ ------------------
0x000001CC6C534060        Proc      2           1           0x3B4303441A1D7E6D 0xA05D5197DA1EAC2D

plan_handle
------------------------------------------------------------------------------------------
0x0500130095555D02D022F111CD01000001000000000000000000000000000000000000000000000000000000

sql_handle
------------------------------------------------------------------------------------------
0x0300130095555D02C864C10061AB000001000000000000000000000000000000000000000000000000000000

Führen Sie jetzt die gespeicherte Prozedur mit einem anderen Parameter aus, aber nehmen Sie keine anderen Änderungen am Ausführungskontext vor:

EXEC usp_SalesByCustomer 8
GO

Überprüfen Sie nochmal, was Sie im Plancache ermitteln können. Hier ist das Resultset.

memory_object_address    objtype   refcounts   usecounts   query_plan_hash    query_hash
---------------------   -------  ---------  ---------  ------------------ ------------------
0x000001CC6C534060        Proc      2           2           0x3B4303441A1D7E6D 0xA05D5197DA1EAC2D

plan_handle
------------------------------------------------------------------------------------------
0x0500130095555D02D022F111CD01000001000000000000000000000000000000000000000000000000000000

sql_handle
------------------------------------------------------------------------------------------
0x0300130095555D02C864C10061AB000001000000000000000000000000000000000000000000000000000000

Beachten Sie, dass dies usecounts auf 2 gestiegen ist, was bedeutet, dass derselbe zwischengespeicherte Plan wiederverwendet wurde, da die Ausführungskontextdatenstrukturen wiederverwendet wurden. Ändern Sie nun die SET ANSI_DEFAULTS-Option, und führen Sie die gespeicherte Prozedur mit dem gleichen Parameter aus.

SET ANSI_DEFAULTS OFF
GO

EXEC usp_SalesByCustomer 8
GO

Überprüfen Sie nochmal, was Sie im Plancache ermitteln können. Hier ist das Resultset.

memory_object_address    objtype   refcounts   usecounts   query_plan_hash    query_hash
---------------------   -------  ---------  ---------  ------------------ ------------------
0x000001CD01DEC060        Proc      2           1           0x3B4303441A1D7E6D 0xA05D5197DA1EAC2D
0x000001CC6C534060        Proc      2           2           0x3B4303441A1D7E6D 0xA05D5197DA1EAC2D

plan_handle
------------------------------------------------------------------------------------------
0x0500130095555D02B031F111CD01000001000000000000000000000000000000000000000000000000000000
0x0500130095555D02D022F111CD01000001000000000000000000000000000000000000000000000000000000

sql_handle
------------------------------------------------------------------------------------------
0x0300130095555D02C864C10061AB000001000000000000000000000000000000000000000000000000000000
0x0300130095555D02C864C10061AB000001000000000000000000000000000000000000000000000000000000

Beachten Sie, dass nun zwei Einträge in der dynamische Verwaltungssicht von sys.dm_exec_cached_plans enthalten sind:

• In usecounts der Spalte wird der Wert 1 im ersten Datensatz angezeigt, bei dem es sich um den einmal SET ANSI_DEFAULTS OFFausgeführten Plan handelt.
• In usecounts der Spalte wird der Wert 2 im zweiten Datensatz angezeigt, bei dem es sich um den mit dem Plan ausgeführten Plan handelt SET ANSI_DEFAULTS ON, da er zweimal ausgeführt wurde.
• Die unterschiedlichen memory_object_address-Werte beziehen sich auf verschiedene Ausführungsplaneinträge im Plancache. Der sql_handle-Wert gilt jedoch für beide Einträge, weil sie sich auf denselben Batch beziehen.
  • Die Ausführung, bei der OFF für ANSI_DEFAULTS festgelegt ist, verfügt über einen neuen plan_handle und kann in Aufrufen wiederverwendet werden, die über die gleichen SET-Optionen verfügen. Der neue Planhandle ist erforderlich, weil der Ausführungskontext aufgrund geänderter SET-Optionen neu initialisiert wurde. Dadurch wird jedoch keine Neukompilierung ausgelöst: beide Einträge beziehen sich auf denselben Plan und dieselbe Abfrage, was durch die identischen query_plan_hash- und query_hash-Werte bestätigt wird.

Das bedeutet schließlich, das zwei Planeinträge für denselben Batch im Cache enthalten sind. Dies unterstreicht die Wichtigkeit davon, dass sichergestellt werden muss, dass die SET-Optionen identisch sind, die sich auf den Plancache auswirken, wenn die gleichen Abfragen wiederholt ausgeführt werden, um die Wiederverwendung des Plans zu optimieren und die Größe des Plancaches auf das erforderliche Mindestmaß zu beschränken.

Tipp

Ein allgemeiner Fall ist, dass unterschiedliche Clients unterschiedliche Standardwerte für die SET-Optionen haben können. Eine Über SQL Server Management Studio hergestellte Verbindung wird z. B. automatisch auf EIN festgelegt QUOTED_IDENTIFIER , während SQLCMD auf OFF festgelegt wird QUOTED_IDENTIFIER . Wenn die gleichen Abfragen auf diesen zwei Clients ausgeführt werden, führt dies wie im Beispiel oben zu mehreren Plänen.

Entfernen von Ausführungsplänen aus dem Plancache

Ausführungspläne verbleiben im Plancache, solange ausreichend Speicherplatz für deren Speicherung zur Verfügung steht. Wenn arbeitsspeicherdruck vorhanden ist, verwendet sql Server Datenbank-Engine einen kostenbasierten Ansatz, um zu bestimmen, welche Ausführungspläne aus dem Plancache entfernt werden sollen. Um eine kostenbasierte Entscheidung zu treffen, erhöht und verringert die SQL Server-Datenbank-Engine eine aktuelle Kostenvariable für jeden Ausführungsplan entsprechend den folgenden Faktoren.

Wenn ein Benutzerprozess einen Ausführungsplan in den Cache einfügt, legt der Benutzerprozess die aktuellen Kosten auf die ursprünglichen Kompilierungskosten fest. für Ad-hoc-Ausführungspläne legt der Benutzerprozess die aktuellen Kosten auf Null fest. Anschließend wird jedes Mal, wenn ein Benutzerprozess auf einen Ausführungsplan verweist, die aktuellen Kosten auf die ursprünglichen Kompilierungskosten zurückgesetzt. für Ad-hoc-Ausführungspläne erhöht der Benutzerprozess die aktuellen Kosten. Für alle Pläne entspricht der maximale Wert für die aktuellen Kosten den Kosten der ursprünglichen Kompilierung.

Wenn arbeitsspeicherdruck vorhanden ist, reagiert die SQL Server-Datenbank-Engine, indem Ausführungspläne aus dem Plancache entfernt werden. Um zu ermitteln, welche Pläne entfernt werden sollen, überprüft sql Server Datenbank-Engine wiederholt den Status jedes Ausführungsplans und entfernt Pläne, wenn die aktuellen Kosten null sind. Ein Ausführungsplan mit null aktuellen Kosten wird nicht automatisch entfernt, wenn arbeitsspeicherdruck vorhanden ist; sie wird nur entfernt, wenn der SQL Server Datenbank-Engine den Plan untersucht und die aktuellen Kosten null sind. Beim Untersuchen eines Ausführungsplans verschiebt der SQL Server Datenbank-Engine die aktuellen Kosten in Richtung Null, indem die aktuellen Kosten verringert werden, wenn eine Abfrage derzeit nicht den Plan verwendet.

Die SQL Server-Datenbank-Engine überprüft wiederholt die Ausführungspläne, bis genügend entfernt wurde, um die Speicheranforderungen zu erfüllen. Während der Arbeitsspeicherdruck besteht, kann ein Ausführungsplan seine Kosten erhöhen und mehrmals verringert haben. Wenn der Arbeitsspeicherdruck nicht mehr vorhanden ist, verringert der SQL Server-Datenbank-Engine die aktuellen Kosten für nicht verwendete Ausführungspläne und alle Ausführungspläne erneut Standard im Plancache, auch wenn die Kosten null sind.

Die SQL Server-Datenbank-Engine verwendet die Ressourcenüberwachungs- und Benutzerarbeitsthreads, um Arbeitsspeicher aus dem Plancache als Reaktion auf den Speicherdruck freizugeben. Vom Ressourcenmonitor und von den Benutzerarbeitsthreads können gleichzeitig ausgeführte Pläne überprüft werden, um die Kosten für die nicht verwendeten Ausführungspläne zu senken. Wenn nicht ausreichend globaler Speicher zur Verfügung steht, werden durch den Ressourcenmonitor Ausführungspläne aus dem Plancache gelöscht. Dadurch wird die Einhaltung von Richtlinien für den Systemspeicher, Prozessspeicher, Ressourcenpoolspeicher und die maximale Größe aller Caches erzwungen.

Die maximale Größe für alle Caches ist eine Funktion der Pufferpoolgröße und darf den maximalen Serverspeicher nicht überschreiten. Weitere Informationen zum Konfigurieren des maximalen Serverarbeitsspeichers finden Sie in den Details zur Einstellung max server memory von sp_configure.

Wenn nicht ausreichend Einzelcachespeicher zur Verfügung steht, werden durch die Benutzerarbeitsthreads Ausführungspläne aus dem Plancache gelöscht. Dadurch wird die Einhaltung der Richtlinien für die maximale Einzelcachegröße und die maximale Anzahl von Einzelcacheeinträgen erzwungen.

In den folgenden Beispielen wird erläutert, welche Ausführungspläne aus dem Plancache entfernt werden:

• Auf einen Ausführungsplan wird regelmäßig verwiesen, sodass seine Kosten nie den Wert 0 (null) erreichen. Der Plan wird im Plancache neu Standard und wird nicht entfernt, es sei denn, es gibt Arbeitsspeicherdruck, und die aktuellen Kosten sind null.
• Ein Ad-hoc-Ausführungsplan wird eingefügt und nicht erneut referenziert, bevor arbeitsspeicherdruck vorhanden ist. Da Ad-hoc-Pläne mit den aktuellen Kosten von Null initialisiert werden, wenn der SQL Server-Datenbank-Engine den Ausführungsplan untersucht, wird die aktuellen Kosten angezeigt und der Plan aus dem Plancache entfernt. Der Ad-hoc-Ausführungsplan wird im Plancache mit null aktuellen Kosten neu Standard, wenn der Arbeitsspeicherdruck nicht vorhanden ist.

Um einen einzelnen Plan oder alle Pläne manuell aus dem Cache zu entfernen, verwenden Sie DBCC FREEPROCCACHE. DBCC FREESYSTEMCACHE kann auch verwendet werden, um jeden Cache, einschließlich des Plancaches, zu leeren. Ab SQL Server 2016 (13.x) wird der ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE Prozedurcache (Plan) für die Datenbank im Bereich gelöscht.

Eine Änderung in einigen Konfigurationseinstellungen über sp_configure und reconfigure führt ebenfalls dazu, dass Pläne aus dem Plancache entfernt werden. Die Liste dieser Konfigurationseinstellungen finden Sie im Abschnitt „Hinweise“ des DBCC FREEPROCCACHE-Artikels. Durch eine Konfigurationsänderung wie diese wird die folgende Infomeldung in das Fehlerprotokoll aufgenommen:

SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

Erneutes Kompilieren von Ausführungsplänen

Bestimmte Änderungen in einer Datenbank können dazu führen, dass ein Ausführungsplan basierend auf dem neuen Status der Datenbank ineffizient oder ungültig ist. SQL Server erkennt die Änderungen, die einen Ausführungsplan ungültig machen, und kennzeichnet den Plan als ungültig. Für die nächste Verbindung, die die Abfrage ausführt, muss dann ein neuer Plan kompiliert werden. Folgende Bedingungen können dazu führen, dass ein Plan ungültig wird:

• Änderungen, die an einer Tabelle oder einer Sicht vorgenommen werden, auf die in der Abfrage verwiesen wird (ALTER TABLE und ALTER VIEW).
• Änderungen, die an einer einzigen Prozedur vorgenommen werden, durch die alle Pläne für die Prozedur aus dem Cache gelöscht werden (ALTER PROCEDURE).
• Änderungen an Indizes, die vom Ausführungsplan verwendet werden.
• Updates der vom Ausführungsplan verwendeten Statistiken, die entweder explizit durch eine Anweisung, wie beispielsweise UPDATE STATISTICS, oder automatisch generiert werden.
• Löschen eines Indexes, der von dem Ausführungsplan verwendet wird.
• Ein expliziter Aufruf von sp_recompile.
• Eine große Anzahl von Änderungen an Schlüsseln (generiert durch INSERT - oder DELETE -Anweisungen von anderen Benutzern, die eine Tabelle ändern, auf die in der Abfrage verwiesen wird).
• Bei Tabellen mit Triggern eine deutliche Erhöhung der Zeilenanzahl in der eingefügten oder gelöschten Tabelle.
• Ausführen einer gespeicherten Prozedur mithilfe der Option WITH RECOMPILE .

Die meisten Neukompilierungen sind erforderlich, um die Richtigkeit der Anweisungen sicherzustellen oder um möglicherweise schnellere Abfrageausführungspläne zu erhalten.

In SQL Server-Versionen vor 2005 wurde, wenn eine Anweisung innerhalb eines Batches eine Neukompilierung verursacht, der gesamte Batch, ob über eine gespeicherte Prozedur, einen Trigger, einen Ad-hoc-Batch oder eine vorbereitete Anweisung, neu kompiliert wurde. Ab SQL Server 2005 (9.x) wird nur die Anweisung innerhalb des Batches, die die Neukompilierung auslöst, neu kompiliert. Außerdem gibt es zusätzliche Typen von Neukompilierungen in SQL Server 2005 (9.x) und höher aufgrund des erweiterten Featuresatzes.

Die Neukompilierung auf Anweisungsebene wirkt sich positiv auf die Leistung aus, da in den meisten Fällen wenige Anweisungen Neukompilierungen und die damit verbundenen Sanktionen in Bezug auf die CPU-Zeit und die Sperren verursachen. Diese Sanktionen werden daher für die anderen Anweisungen im Batch vermieden, die nicht neu kompiliert werden müssen.

Das erweiterte sql_statement_recompile-Ereignis (xEvent) meldet Neukompilierungen auf Anweisungsebene. Dieses xEvent erscheint, wenn ein beliebiger Batch eine Neukompilierung auf Anweisungsebene erfordert. Dazu gehören gespeicherte Prozeduren, Trigger, Ad-hoc-Batches und Abfragen. Batches können über mehrere Schnittstellen übermittelt werden, einschließlich sp_executesqldynamischer SQL, Prepare-Methoden oder Execute-Methoden.

Die recompile_cause-Spalte von sql_statement_recompile xEvent enthält einen ganzzahligen Code, der den Grund für die Neukompilierung angibt. Die folgende Tabelle enthält die möglichen Gründe:

Schema geändert

Statistiken geändert

Verzögerte Kompilierung

SET-Option geändert

Temporäre Tabelle geändert

Remote-Rowset geändert

FOR BROWSE-Berechtigung geändert

Abfragebenachrichtigungsumgebung geändert

Partitionierte Sicht geändert

Cursoroptionen geändert

OPTION (RECOMPILE) angefordert.

Parametrisierter Plan geleert

Plan geändert, der die Datenbankversion betrifft

Erzwingende Richtlinie des Abfragespeicherplans geändert

Erzwingende Richtlinie des Abfragespeicherplans fehlgeschlagen

Plan des Abfragespeichers fehlt

Hinweis

In SQL Server-Versionen, in denen keine erweiterten Ereignisse (Extended Events, XEvents) verfügbar sind, kann das Ablaufverfolgungsereignis SP:Recompile von SQL Server Profiler auch zur Berichterstellung von Neukompilierungen auf Anweisungsebene verwendet werden.

Das Ablaufverfolgungsereignis SQL:StmtRecompile meldet ebenfalls Neukompilierungen, und es kann auch zum Nachverfolgen und Debuggen von Neukompilierungen verwendet werden.

Generiert SP:Recompile zwar nur für gespeicherte Prozeduren und Trigger, SQL:StmtRecompile generiert für gespeicherte Prozeduren, Trigger, Ad-hoc-Batches, Batches, die mithilfe sp_executesqlvon vorbereiteten Abfragen und dynamischem SQL ausgeführt werden. Die EventSubClass-Spalte von SP:Recompile und SQL:StmtRecompile enthält einen ganzzahligen Code, der den Grund für die Neukompilierung angibt. Die Codes sind hier beschrieben.

Hinweis

Wenn die Datenbankoption AUTO_UPDATE_STATISTICS auf ON festgelegt wird, werden Abfragen neu kompiliert, wenn sie Tabellen oder indizierte Sichten betreffen, deren Statistiken aktualisiert wurden oder deren Kardinalitäten sich seit der letzten Ausführung signifikant geändert haben.

Dieses Verhalten gilt für standardmäßige benutzerdefinierte Tabellen, temporäre Tabellen und die durch DML-Trigger erstellten eingefügten und gelöschten Tabellen. Wenn sich sehr viele Neukompilierungen auf die Abfrageleistung auswirken, können Sie diese Einstellung in OFFändern. Wenn die AUTO_UPDATE_STATISTICS-Datenbankoption auf OFF festgelegt wird, werden auf der Grundlage von Statistiken oder wegen Änderungen der Kardinalität keine Neukompilierungen durchgeführt, mit Ausnahme der durch DML INSTEAD OF-Trigger erstellten eingefügten und gelöschten Tabellen. Da diese Tabellen erstellt tempdbwerden, hängt die Neukompilierung von Abfragen, die darauf zugreifen, von der Einstellung in tempdbAUTO_UPDATE_STATISTICS .

In SQL Server vor 2005 werden Abfragen weiterhin basierend auf änderungen an Karte inality-Änderungen an dem eingefügten und gelöschten DML-Trigger neu kompiliert, auch wenn diese Einstellung lautetOFF.

Wiederverwenden von Parametern und Ausführungsplan

Durch die Verwendung von Parametern, einschließlich der Parametermarkierungen in ADO-, OLE DB- und ODBC-Anwendungen, kann die Wiederverwendbarkeit von Ausführungsplänen erhöht werden.

Warnung

Es ist sicherer, Parameter oder Parametermarkierungen zu verwenden, die vom Endbenutzer eingegebene Werte enthalten, als die Werte in einer Zeichenfolge zu verketten, die dann mithilfe einer API-Datenzugriffsmethode, einer EXECUTE -Anweisung oder einer gespeicherten sp_executesql -Prozedur ausgeführt werden.

Die zwei folgenden SELECT -Anweisungen unterscheiden sich lediglich im Hinblick auf die Werte, die in der WHERE -Klausel verglichen werden:

SELECT *
FROM AdventureWorks2022.Production.Product
WHERE ProductSubcategoryID = 1;

SELECT *
FROM AdventureWorks2022.Production.Product
WHERE ProductSubcategoryID = 4;

Die Ausführungspläne für diese Abfragen unterscheiden sich lediglich hinsichtlich des Werts, der für den Vergleich mit der ProductSubcategoryID -Spalte gespeichert wird. Obwohl sql Server immer das Ziel hat, immer zu erkennen, dass die Anweisungen im Wesentlichen denselben Plan generieren und die Pläne wiederverwenden, erkennt SQL Server dies manchmal nicht in komplexen Transact-SQL-Anweisungen.

Wenn Sie Konstanten mithilfe von Parametern von den Transact-SQL-Anweisungen trennen, unterstützen Sie die relationale Engine dabei, doppelte Pläne zu erkennen. Es gibt folgende Möglichkeiten, um Parameter zu verwenden:

• Verwenden Sie in Transact-SQL sp_executesql:

  DECLARE @MyIntParm INT
  SET @MyIntParm = 1
  EXEC sp_executesql
     N'SELECT *
     FROM AdventureWorks2022.Production.Product
     WHERE ProductSubcategoryID = @Parm',
     N'@Parm INT',
     @MyIntParm
  

  Diese Methode wird für Transact-SQL-Skripts, gespeicherte Prozeduren oder Trigger empfohlen, die SQL-Anweisungen dynamisch generieren.

• ADO, OLE DB und ODBC verwenden Parametermarkierungen. Parametermarkierungen sind Fragezeichen (?), die eine Konstante in einer SQL-Anweisung ersetzen und an eine Programmvariable gebunden sind. Beispielsweise können Sie in einer ODBC-Anwendung folgende Aktionen ausführen:

  • Verwenden Sie SQLBindParameter , um eine ganzzahlige Variable an die erste Parametermarkierung in einer SQL-Anweisung zu binden.

  • Speichern Sie den ganzzahligen Wert in der Variablen.

  • Führen Sie die Anweisung aus, und geben Sie dabei die Parametermarkierung (?) an:

    SQLExecDirect(hstmt,
      "SELECT *
      FROM AdventureWorks2022.Production.Product
      WHERE ProductSubcategoryID = ?",
      SQL_NTS);
    

  Der SQL Server Native Client-OLE DB-Anbieter und der SQL Server Native Client-ODBC-Treiber, die beide mit SQL Server zur Verfügung gestellt werden, verwenden sp_executesql , um Anweisungen an SQL Server zu senden, wenn Parametermarkierungen in Anwendungen verwendet werden.

• Zum Entwerfen von gespeicherten Prozeduren mit vorprogrammierter Parameterverwendung

Wenn Sie parameter nicht explizit in den Entwurf Ihrer Anwendungen integrieren, können Sie sich auch auf den SQL Server Query Optimizer verlassen, um bestimmte Abfragen automatisch zu parametrisieren, indem Sie das Standardverhalten der einfachen Parametrisierung verwenden. Alternativ können Sie erzwingen, dass der Abfrageoptimierer die Parametrisierung aller Abfragen in der Datenbank in Betracht zieht, indem Sie die PARAMETERIZATION-Option der ALTER DATABASE-Anweisung auf FORCED festlegen.

Auch wenn die erzwungene Parametrisierung aktiviert ist, kann die einfache Parametrisierung erfolgen. Die folgende Abfrage kann z. B. nicht gemäß den Regeln der erzwungenen Parametrisierung parametrisiert werden:

SELECT * FROM Person.Address
WHERE AddressID = 1 + 2;

Sie kann jedoch nach den Regeln der einfachen Parametrisierung parametrisiert werden. Wenn die erzwungene Parametrisierung einen Fehler erzeugt, wird anschließend die einfache Parametrisierung versucht.

Einfache Parametrisierung

In SQL Server erhöht die Verwendung von Parametern oder Parametermarkern in Transact-SQL-Anweisungen die Fähigkeit des relationalen Moduls, neue Transact-SQL-Anweisungen mit vorhandenen, zuvor kompilierten Ausführungsplänen abzugleichen.

Warnung

Es ist sicherer, Parameter oder Parametermarkierungen zu verwenden, die vom Endbenutzer eingegebene Werte enthalten, als die Werte in einer Zeichenfolge zu verketten, die dann mithilfe einer API-Datenzugriffsmethode, einer EXECUTE -Anweisung oder einer gespeicherten sp_executesql -Prozedur ausgeführt werden.

Wenn eine Transact-SQL-Anweisung ohne Parameter ausgeführt wird, parametrisiert SQL Server die Anweisung intern, um die Wahrscheinlichkeit zu erhöhen, dass ein übereinstimmender Ausführungsplan gefunden wird. Dieser Prozess wird als einfache Parametrisierung bezeichnet. In SQL Server-Versionen vor 2005 wurde der Prozess als automatische Parametrierung bezeichnet.

Angenommen, die folgende Anweisung wird ausgeführt:

SELECT * FROM AdventureWorks2022.Production.Product
WHERE ProductSubcategoryID = 1;

Der Wert 1 am Ende der Anweisung kann als Parameter angegeben werden. Die relationale Engine erstellt den Ausführungsplan für diesen Batch so, als ob anstelle des Werts 1 ein Parameter angegeben worden wäre. Aufgrund dieser einfachen Parametrisierung erkennt SQL Server, dass die folgenden beiden Anweisungen im Prinzip den gleichen Ausführungsplan generieren, und verwendet den ersten Plan auch für die zweite Anweisung:

SELECT * FROM AdventureWorks2022.Production.Product
WHERE ProductSubcategoryID = 1;

SELECT * FROM AdventureWorks2022.Production.Product
WHERE ProductSubcategoryID = 4;

Bei der Verarbeitung komplexer Transact-SQL-Anweisungen kann das relationale Modul Schwierigkeiten haben, zu bestimmen, welche Ausdrücke parametrisiert werden können. Um die Fähigkeit des relationalen Moduls zu erhöhen, komplexe Transact-SQL-Anweisungen mit vorhandenen, nicht verwendeten Ausführungsplänen abzugleichen, geben Sie explizit die Parameter mithilfe von sp_executesql Parametermarkierungen oder Parametermarkierungen an.

Hinweis

Wenn die +Operatoren , -, *, oder /% arithmetischen Operatoren verwendet werden, um implizite oder explizite Konvertierungen von int, smallint, tinyint oder bigint Konstantenwerten in den Float-, Real-, Dezimal- oder numerische Datentypen durchzuführen, wendet SQL Server bestimmte Regeln an, um den Typ und die Genauigkeit der Ausdrucksergebnisse zu berechnen. Allerdings unterscheiden sich diese Regeln in Abhängigkeit davon, ob die Abfrage parametrisiert ist oder nicht. Daher können gleiche Ausdrücke in Abfragen in einigen Fällen zu unterschiedlichen Ergebnissen führen.

Beim Standardverhalten der einfachen Parametrisierung parametrisiert SQL Server eine relativ kleine Klasse von Abfragen. Allerdings können Sie angeben, dass mit bestimmten Einschränkungen alle Abfragen in einer Datenbank parametrisiert werden, indem Sie die PARAMETERIZATION -Option des Befehls ALTER DATABASE auf FORCEDfestlegen. Dies kann die Leistung von Datenbanken verbessern, die hohe Mengen gleichzeitiger Abfragen aufweisen, indem die Häufigkeit der Abfragekompilierungen reduziert wird.

Alternativ können Sie angeben, dass eine einzelne Abfrage und alle anderen Abfragen, die in ihrer Syntax gleichwertig sind, und lediglich in ihren Parameterwerten abweichen, parametrisiert werden.

Tipp

Bei Verwendung einer Objektrelationalzuordnungslösung (Object-Relational Mapping, ORM), z. B. Entity Framework (EF), können Anwendungsabfragen wie manuelle LINQ-Abfragestrukturen oder bestimmte rohe SQL-Abfragen möglicherweise nicht parametrisiert werden, was sich auf die Erneute Verwendung des Plans und die Möglichkeit auswirkt, Abfragen in der Abfragespeicher nachzuverfolgen. Weitere Informationen finden Sie unter Zwischenspeichern und Parametrisieren von Abfragen und Unformatierte SQL-Abfragen.

Erzwungene Parametrisierung

Sie können das standardmäßige Parametrisierungsverhalten von SQL Server, die einfache Parametrisierung, überschreiben, indem Sie angeben, dass alle SELECT-, INSERT-, UPDATE- und DELETE -Anweisungen in einer Datenbank mit bestimmten Einschränkungen parametrisiert werden sollen. Die erzwungene Parametrisierung wird aktiviert, indem die PARAMETERIZATION -Option in der FORCED -Anweisung auf ALTER DATABASE festgelegt wird. Die erzwungene Parametrierung kann die Leistung bestimmter Datenbanken verbessern, indem die Häufigkeit von Abfragekompilierungen und Neukompilierungen reduziert wird. Datenbanken, die von der erzwungenen Parametrisierung profitieren können, sind in der Regel diejenigen, die hohe Mengen gleichzeitiger Abfragen aus Quellen wie Point-of-Sale-Anwendungen erleben.

Wenn die PARAMETERIZATION -Option auf FORCEDfestgelegt ist, werden während der Kompilierung der Abfrage alle Literalwerte in SELECT-, INSERT-, UPDATE- oder DELETE -Anweisungen, ungeachtet der Form, in der sie übergeben wurden, in Parameter konvertiert. Ausnahmen bilden Literalwerte in folgenden Abfragekonstruktionen:

• INSERT...EXECUTE -Anweisungen.
• Anweisungen innerhalb des Hauptteils von gespeicherten Prozeduren, Triggern oder benutzerdefinierten Funktionen. In SQL Server werden bereits Abfragepläne für diese Routinen wiederverwendet.
• Vorbereitete Anweisungen, die bereits in der clientbasierten Anwendung parametrisiert wurden.
• Anweisungen, die XQuery-Methodenaufrufe enthalten, wo die Methode in einem Kontext angezeigt wird, in dem ihre Argumente normalerweise parametrisiert werden, wie beispielsweise die WHERE -Klausel. Wenn die Methode in einem Kontext angezeigt wird, in dem ihre Argumente nicht parametrisiert werden, wird der Rest der Anweisung parametrisiert.
• Anweisungen in einem Transact-SQL-Cursor. (SELECT -Anweisungen innerhalb von API-Cursorn werden parametrisiert.)
• Als veraltet markierte Abfragekonstrukte.
• Eine Anweisung, die im Kontext von ANSI_PADDING oder ANSI_NULLS mit der Einstellung OFFausgeführt wird.
• Anweisungen mit mehr als 2.097 parametrisierbaren Literalwerten.
• Anweisungen, die auf Variablen verweisen, wie beispielsweise WHERE T.col2 >= @bb.
• Anweisungen mit RECOMPILE -Abfragehinweis.
• Anweisungen mit COMPUTE -Klauseln.
• Anweisungen mit WHERE CURRENT OF -Klauseln.

Außerdem werden die folgenden Abfrageklauseln nicht parametrisiert. In diesen Fällen werden nur die Klauseln nicht parametrisiert. Andere Klauseln innerhalb derselben Abfrage können zur erzwungenen Parametrisierung berechtigt sein.

• <select_list> einer beliebigen SELECT-Anweisung. Dies trifft ebenfalls auf SELECT-Listen von Unterabfragen sowie SELECT-Listen innerhalb von INSERT-Anweisungen zu.
• Unterabfragen mit SELECT -Anweisungen innerhalb von IF -Anweisungen.
• Die Abfrageklauseln TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTOund FOR XML.
• Direkte oder als Teilausdrücke formulierte Argumente der Operatoren OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXMLsowie aller FULLTEXT -Operatoren.
• Das pattern-Argument und das escape_character-Argument einer LIKE -Klausel.
• Das style-Argument einer CONVERT -Klausel.
• Integer-Konstanten innerhalb einer IDENTITY -Klausel.
• Über die ODBC-Erweiterungssyntax angegebene Konstanten.
• Zum Kompilierungszeitpunkt auf eine Konstante reduzierbare Ausdrücke, die Argumente der Operatoren +, -, *, /und % sind. Wenn Sie die Berechtigung für die erzwungene Parametrierung in Betracht ziehen, betrachtet SQL Server einen Ausdruck als konstant gefaltet, wenn eine der folgenden Bedingungen zutrifft:
  • Der Ausdruck enthält keine Spalten, Variablen oder Unterabfragen.
  • Der Ausdruck enthält eine CASE -Klausel.
• Argumente von Abfragehinweisklauseln. Zu diesen Argumenten gehören das Argument number_of_rows des Abfragehinweises FAST, das Argument number_of_processors des Abfragehinweises MAXDOP sowie das Argument number des Abfragehinweises MAXRECURSION.

Die Parametrisierung wird auf der Ebene der einzelnen Transact-SQL-Anweisungen ausgeführt, d. h. die Anweisungen werden nacheinander batchweise parametrisiert. Nach dem Kompilieren wird eine parametrisierte Abfrage ausgeführt – in dem Kontext des Batches, in dem die Abfrage ursprünglich übermittelt wurde. Wenn ein Ausführungsplan für eine Abfrage zwischengespeichert wird, können Sie ermitteln, ob die Abfrage parametrisiert wurde, indem Sie auf die SQL-Spalte der sys.syscacheobjects dynamischen Verwaltungsansicht verweisen. Wenn eine Abfrage parametrisiert wird, stehen die Namen und Datentypen der Parameter vor dem Text des übergebenen Batches in dieser Spalte, wie beispielsweise (@1 tinyint).

Hinweis

Parameternamen sind willkürlich. Benutzer bzw. Anwendungen sollten sich nicht auf eine bestimmte Namensreihenfolge verlassen. Darüber hinaus kann folgendes zwischen Versionen von SQL Server- und Service Pack-Upgrades geändert werden: Parameternamen, die Auswahl von Literalen, die parametrisiert werden, und den Abstand im parametrisierten Text.

Datentypen von Parametern

Beim Parametrisieren von Literalwerten konvertiert SQL Server die Parameter in folgende Datentypen:

• Ganze Zahlenliterale, deren Größe andernfalls in den Datentyp "int" passt, wird int parametrisiert. Größere ganzzahlige Literale, die Teile von Prädikaten sind, die einen Vergleichsoperator umfassen (einschließlich <, , <=, =, !=, >, >=, ALL!>!<ANYSOME<>BETWEENund IN) parametrisieren in numerische(38,0). Größere Literale, die nicht Teil von Prädikaten mit Vergleichsoperatoren sind, werden bei der Parametrisierung in numerische Werte mit ausreichenden Ziffern (precision) für ihre Größe und einem Dezimalstellenwert (scale) von 0 konvertiert.
• Numerische Festkommaliterale, die Teil von Prädikaten mit Vergleichsoperatoren sind, werden bei der Parametrisierung in numerische Werte mit 38 Ziffern (precision) und einem für ihre Größe ausreichenden Dezimalstellenwert (scale) konvertiert. Numerische Festkommaliterale, die nicht Teil von Prädikaten mit Vergleichsoperatoren sind, werden bei der Parametrisierung in numerische Werte mit ausreichenden Ziffern (precision) und einem ausreichenden Dezimalstellenwert (scale) für ihre Größe konvertiert.
• Numerische Fließkommaliterale werden bei der Parametrisierung in float(53)-Werte konvertiert.
• Nicht-Unicode-Zeichenfolgenliterale werden bei der Parametrisierung in varchar(8000)-Werte konvertiert, wenn das Literal 8.000 Zeichen nicht überschreitet, und in varchar(max)-Werte, wenn es 8.000 Zeichen überschreitet.
• Unicode-Zeichenfolgenliterale werden bei der Parametrisierung in nvarchar(4000)-Werte konvertiert, wenn das Literal 4.000 Zeichen nicht überschreitet, und in nvarchar(max)-Werte, wenn es 4.000 Zeichen überschreitet.
• Binäre Literale werden bei der Parametrisierung in varbinary(8000)-Werte konvertiert, wenn das Literal 8.000 Bytes nicht überschreitet. Wenn es 8.000 Bytes überschreitet, wird es in einen varbinary(max)-Wert konvertiert.
• Literale vom Typ „money“ werden bei der Parametrisierung in money-Werte konvertiert.

Richtlinien für die Verwendung der erzwungenen Parametrisierung

Berücksichtigen Sie Folgendes, wenn Sie die PARAMETERIZATION -Option auf FORCED festlegen:

• Die erzwungene Parametrisierung konvertiert die literalen Konstanten einer Abfrage, sobald diese kompiliert wird, tatsächlich in Parameter. Daher kann der Abfrageoptimierer suboptimale Pläne für Abfragen auswählen. Insbesondere verringert sich die Wahrscheinlichkeit, dass der Abfrageoptimierer eine Übereinstimmung zwischen der Abfrage und der richtigen indizierten Sicht oder dem Index für eine berechnete Spalte findet. Es kann auch suboptimale Pläne für Abfragen auswählen, die in partitionierten Tabellen und verteilten partitionierten Ansichten gestellt werden. Die erzwungene Parametrisierung sollte nicht für Umgebungen verwendet werden, die stark auf indizierten Ansichten und Indizes auf berechneten Spalten basieren. Im Allgemeinen sollte die PARAMETERIZATION FORCED Option nur von erfahrenen Datenbankadministratoren verwendet werden, nachdem festgestellt wurde, dass sich dies nicht negativ auf die Leistung auswirkt.
• Verteilte Abfragen, die auf mehrere Datenbanken verweisen, sind für die erzwungene Parametrisierung geeignet, solange die PARAMETERIZATION -Option in der Datenbank auf FORCED festgelegt wird, in deren Kontext die Abfrage ausgeführt wird.
• Wenn die PARAMETERIZATION -Option auf FORCED festgelegt wird, werden alle Abfragepläne aus dem Plancache der Datenbank geleert, mit Ausnahme derer, die gerade kompiliert, erneut kompiliert oder ausgeführt werden. Die Pläne der Abfragen, die während der Einstellungsänderung kompiliert, erneut kompiliert oder ausgeführt werden, werden beim nächsten Ausführen der Abfrage parametrisiert.
• Das Festlegen der PARAMETERIZATION -Option ist ein Onlinevorgang, d.h., es sind keine exklusiven Sperren auf Datenbankebene erforderlich.
• Die aktuelle Einstellung der PARAMETERIZATION -Option wird beim erneuten Anfügen oder Wiederherstellen einer Datenbank beibehalten.

Sie können das Verhalten der erzwungenen Parametrisierung überschreiben, indem Sie angeben, dass für eine einzelne Abfrage und für alle anderen Abfragen, die syntaktisch äquivalent sind und sich nur in ihren Parameterwerten unterscheiden, die einfache Parametrisierung versucht werden soll. Im Gegensatz dazu können Sie angeben, dass die erzwungene Parametrisierung nur für einen Satz von syntaktisch äquivalenten Abfragen versucht werden soll, selbst wenn die erzwungene Parametrisierung in der Datenbank deaktiviert ist. Zu diesem Zweck werdenPlanhinweislisten verwendet.

Hinweis

Wenn die PARAMETERIZATION Option auf festgelegt FORCEDist, kann sich die Berichterstellung von Fehlermeldungen unterscheiden, wenn die PARAMETERIZATION Option auf SIMPLE:mehrere Fehlermeldungen unter erzwungener Parametrisierung gemeldet werden, wobei weniger Nachrichten unter einfacher Parametrisierung gemeldet werden, und die Zeilennummern, in denen Fehler auftreten, falsch gemeldet werden können.

Vorbereiten von SQL-Anweisungen

Die relationale Engine von SQL Server bietet vollständige Unterstützung für die Vorbereitung von Transact-SQL-Anweisungen vor ihrer Ausführung. Wenn eine Anwendung eine Transact-SQL-Anweisung mehrfach ausführen muss, kann mithilfe der Datenbank-API Folgendes erreicht werden:

• Einmaliges Vorbereiten der Anweisung. Mit diesem Schritt wird die Transact-SQL-Anweisung zu einem Ausführungsplan kompiliert.
• Ausführen des vorkompilierten Ausführungsplans immer dann, wenn die Anweisung ausgeführt werden muss. Dadurch muss die Transact-SQL-Anweisung nach der ersten Ausführung nicht jedes Mal erneut kompiliert werden. Das Vorbereiten und Ausführen von Anweisungen wird durch API-Funktionen und -Methoden gesteuert. Sie ist nicht Teil der Transact-SQL-Sprache. Das Vorbereiten/Ausführen-Modell für die Ausführung von Transact-SQL-Anweisungen wird vom SQL Server Native Client-OLE DB-Anbieter und vom SQL Server Native Client-ODBC-Treiber unterstützt. Bei einer Vorbereitungsanforderung sendet der Anbieter oder der Treiber die Anweisung zusammen mit der Anforderung zur Vorbereitung der Anweisung an SQL Server. Von SQL Server wird ein Ausführungsplan kompiliert und ein Handle für diesen Plan an den Anbieter oder Treiber zurückgegeben. Bei einer Ausführungsanforderung sendet der Anbieter bzw. Treiber eine Anforderung an den Server, den dem Handle zugeordneten Plan auszuführen.

Vorbereitete Anweisungen können nicht zum Erstellen temporärer Objekte auf SQL Server verwendet werden. Vorbereitete Anweisungen können nicht auf gespeicherte Systemprozeduren verweisen, die temporäre Objekte erstellen, z. B. temporäre Tabellen. Diese Prozeduren müssen direkt ausgeführt werden.

Durch übermäßige Verwendung des Vorbereiten/Ausführen-Modells kann die Leistung beeinträchtigt werden. Wenn eine Anweisung nur ein Mal ausgeführt wird, wird durch eine direkte Ausführung nur ein Netzwerkroundtrip zum Server benötigt. Das Vorbereiten und Ausführen einer Transact-SQL-Anweisung, die nur einmal ausgeführt wird, erfordert einen zusätzlichen Netzwerkroundtrip: einen Trip zur Vorbereitung und einen Trip zur Ausführung der Anweisung.

Das Vorbereiten einer Anweisung ist effizienter, wenn Parametermarkierungen verwendet werden. Nehmen Sie z.B. an, eine Anwendung soll gelegentlich Produktinformationen aus der AdventureWorks -Beispieldatenbank abrufen. Es gibt zwei Möglichkeiten, wie die Anwendung diese Aufgabe ausführen kann.

Die erste Möglichkeit besteht darin, dass die Anwendung für jedes angeforderte Produkt eine eigene Abfrage ausführt:

SELECT * FROM AdventureWorks2022.Production.Product
WHERE ProductID = 63;

Die zweite Möglichkeit umfasst folgende Schritte:

1. Die Anwendung bereitet eine Anweisung vor, die die Parametermarkierung (?) enthält:

   SELECT * FROM AdventureWorks2022.Production.Product
   WHERE ProductID = ?;
   

2. Die Anwendung bindet eine Programmvariable an die Parametermarkierung.

3. Die Anwendung füllt die gebundene Variable mit dem Schlüsselwert und führt die Anweisung aus, sobald die Produktinformationen benötigt werden.

Die zweite Methode ist effizienter, sobald die Anweisung mehr als drei Mal ausgeführt wird.

In SQL Server bietet das Vorbereiten/Ausführen-Modell aufgrund der Art und Weise, wie Ausführungspläne wiederverwendet, keine erheblichen Leistungsvorteile gegenüber der direkten Ausführung. SQL Server besitzt effiziente Algorithmen zur Ermittlung von Übereinstimmungen zwischen aktuellen Transact-SQL-Anweisungen und Ausführungsplänen, die für vorhergehende Ausführungen der gleichen Transact-SQL-Anweisung generiert wurden. Wenn eine Anwendung eine SQL Server-Anweisung mit Parametermarkierungen mehrfach ausführt, verwendet Transact-SQL den Ausführungsplan der ersten Ausführung für die zweite und alle folgenden Ausführungen wieder (es sei denn, der Plan wird aus dem Plancache entfernt). Das Vorbereiten/Ausführen-Modell bietet jedoch weiterhin die folgenden Vorteile:

• Das Suchen eines Ausführungsplans anhand eines identifizierenden Handles ist effizienter als die Algorithmen, die für das Ermitteln einer übereinstimmenden Transact-SQL-Anweisung mit vorhandenen Ausführungsplänen verwendet werden.
• Die Anwendung kann steuern, wann der Ausführungsplan erstellt, und wann er wiederverwendet werden soll.
• Das Vorbereiten/Ausführen-Modell kann auf andere Datenbanken portiert werden, einschließlich früherer Versionen von SQL Server.

Parameterempfindlichkeit

Die Parameterempfindlichkeit, auch als "Parametersiffing" bezeichnet, bezieht sich auf einen Prozess, bei dem SQL Server die aktuellen Parameterwerte während der Kompilierung oder Neukompilierung "sniffs" an den Abfrageoptimierer weitergibt, sodass sie zum Generieren potenziell effizienterer Abfrageausführungspläne verwendet werden können.

Parameterwerte werden während der Kompilierung oder Neukompilierung für die folgenden Batchtypen ermittelt:

• Gespeicherten Prozeduren
• Abfragen, die über sp_executesql übermittelt werden
• Vorbereitete Abfragen

Weitere Informationen zur Problembehandlung bei fehlerhaften Problemen beim Erkennen von Parametern finden Sie unter:

• Untersuchen und Beheben von Problemen mit Parametern
• Parameter und Wiederverwendung von Ausführungsplänen
• Optimierung parametersensitiver Pläne
• Behandeln von Abfragen mit Problemen mit dem Ausführungsplan für parameterbezogene Abfragen in Azure SQL-Datenbank
• Behandeln von Abfragen mit Problemen mit vertraulichen Abfrageausführungsplans in Azure SQL verwaltete Instanz

Hinweis

Für Abfragen, die den RECOMPILE-Hinweis verwenden, werden jeweils die Parameterwerte und aktuellen Werte der lokalen Variablen ermittelt. Die ermittelten Werte (der Parameter und lokalen Variablen) sind die, die an dem Ort direkt vor der Anweisung mit dem RECOMPILE-Hinweis vorhanden sind. Im Gegensatz dazu werden bei Parametern die Werte, die innerhalb des Batchaufrufs übermittelt werden, nicht geprüft.

Parallele Abfrageverarbeitung

SQL Server ermöglicht parallele Abfragen, um die Abfrageausführung und Indexvorgänge für Computer zu optimieren, die über mehrere Mikroprozessoren (CPUs) verfügen. Da SQL Server einen Abfrage- oder Indexvorgang parallel ausführen kann, indem mehrere Arbeitsthreads des Betriebssystems verwendet werden, kann der Vorgang schnell und effizient abgeschlossen werden.

Während der Abfrageoptimierung sucht SQL Server nach Abfragen oder Indexvorgängen, für die eine parallele Ausführung vorteilhaft ist. Für diese Abfragen fügt SQL Server Verteilungsoperatoren in den Abfrageausführungsplan ein, um die Abfrage für die parallele Ausführung vorzubereiten. Ein Verteilungsoperator ist ein Operator in einem Plan für die Abfrageausführung, der die Prozessverwaltung, die Neuverteilung der Daten und die Ablaufsteuerung ermöglicht. Der Verteilungsoperator schließt die logischen Operatoren Distribute Streams, Repartition Streamsund Gather Streams als Untertypen ein. Einer oder mehrere dieser Operatoren können in der Showplanausgabe eines Abfrageplans für eine parallele Abfrage enthalten sein.

Wichtig

Bestimmte Konstrukte hemmen die Fähigkeit von SQL Server, Parallelität für den gesamten Ausführungsplan oder Teile oder den Ausführungsplan zu verwenden.

Zu den Konstrukten, die Parallelität verhindern, gehören:

• Benutzerdefinierte Skalarfunktionen
  Weitere Informationen zu benutzerdefinierten Skalarfunktionen finden Sie unter Erstellen benutzerdefinierter Funktionen. Ab SQL Server 2019 (15.x) hat die SQL Server-Datenbank-Engine die Möglichkeit, diese Funktionen inline zu integrieren und die Verwendung von Parallelität während der Abfrageverarbeitung freizuschalten. Weitere Informationen zum Inlining benutzerdefinierter Skalarfunktionen finden Sie unter Intelligente Abfrageverarbeitung in SQL-Datenbanken.

• Remote Query
  Weitere Informationen zu Remote Query finden Sie unter Referenz zu logischen und physischen Showplanoperatoren.

• Dynamische Cursor
  Weitere Informationen zu Cursorn finden Sie unter DECLARE CURSOR.

• Rekursive Abfragen
  Weitere Informationen zur Rekursion finden Sie unter Richtlinien zum Definieren und Verwenden rekursiver allgemeiner Tabellenausdrücke und Rekursion in T-SQL.

• Tabellenwertfunktionen mit mehreren Anweisungen (Multi-statement table-valued functions, MSTVFs)
  Weitere Informationen zu MSTVFs finden Sie unter Erstellen benutzerdefinierter Funktionen (Datenbank-Engine).

• TOP-Schlüsselwort
  Weitere Informationen finden Sie unter TOP (Transact-SQL).

Ein Abfrageausführungsplan kann das NonParallelPlanReason-Attribut im QueryPlan-Element enthalten, das beschreibt, warum Parallelität nicht verwendet wurde. Zu den Werten für dieses Attribut gehören:

NonParallelPlanReason Value	Beschreibung
MaxDOPSetToOne	Der maximale Grad an Parallelität ist auf 1 festgelegt.
EstimatedDOPIsOne	Der geschätzte Grad der Parallelität ist 1.
NoParallelWithRemoteQuery	Parallelität wird für Remoteabfragen nicht unterstützt.
NoParallelDynamicCursor	Parallele Pläne werden für dynamische Cursor nicht unterstützt.
NoParallelFastForwardCursor	Parallele Pläne werden für schnelle Vorwärtscursor nicht unterstützt.
NoParallelCursorFetchByBookmark	Parallele Pläne werden nicht für Cursor unterstützt, die anhand von Lesezeichen abrufen.
NoParallelCreateIndexInNonEnterpriseEdition	Die parallele Indexerstellung wird für andere Editionen als Enterprise nicht unterstützt.
NoParallelPlansInDesktopOrExpressEdition	Parallele Pläne werden für die Desktop- und Express-Edition nicht unterstützt.
NonParallelizableIntrinsicFunction	Die Abfrage verweist auf eine nicht parallelisierbare intrinsische Funktion.
CLRUserDefinedFunctionRequiresDataAccess	Parallelität wird für eine benutzerdefinierte CLR-Funktion, die Datenzugriff erfordert, nicht unterstützt.
TSQLUserDefinedFunctionsNotParallelizable	Die Abfrage verweist auf eine benutzerdefinierte T-SQL-Funktion, die nicht parallelisierbar war.
TableVariableTransactionsDoNotSupportParallelNestedTransaction	Tabellenvariable Transaktionen unterstützen keine parallelen geschachtelten Transaktionen.
DMLQueryReturnsOutputToClient	DIE DML-Abfrage gibt die Ausgabe an den Client zurück und ist nicht parallelisierbar.
MixedSerialAndParallelOnlineIndexBuildNotSupported	Nicht unterstützte Mischung aus seriellen und parallelen Plänen für eine einzelne Onlineindexerstellung.
CouldNotGenerateValidParallelPlan	Fehler bei der Überprüfung des parallelen Plans, Rückgriff auf seriell.
NoParallelForMemoryOptimizedTables	Parallelität wird für referenzierte In-Memory-OLTP-Tabellen nicht unterstützt.
NoParallelForDmlOnMemoryOptimizedTable	Parallelität wird für DML in einer In-Memory-OLTP-Tabelle nicht unterstützt.
NoParallelForNativelyCompiledModule	Parallelität wird für referenzierte nativ compilierte Module nicht unterstützt.
NoRangesResumableCreate	Fehler bei der Bereichsgenerierung für einen fortsetzbaren Erstellungsvorgang.

Nach dem Einfügen eines Verteilungsoperators ist das Ergebnis ein Plan für eine parallele Abfrageausführung. Ein Plan für die parallele Abfrageausführung kann mehrere Arbeitsthreads verwenden. Ein serieller Ausführungsplan, der von einer nicht parallelen (seriellen) Abfrage verwendet wird, verwendet nur einen Arbeitsthread bei seiner Ausführung. Die tatsächliche Anzahl der Arbeitsthreads, die von einer parallelen Abfrage verwendet werden, wird während der Initialisierung der Abfrageplanausführung bestimmt und durch die Komplexität des Plans und den Grad der Parallelität bestimmt.

Der Grad der Parallelität (DOP) bestimmt die maximale Anzahl von CPUs, die verwendet werden; Dies bedeutet nicht die Anzahl der Arbeitsthreads, die verwendet werden. Der Grad der Parallelität wird taskbezogen festgelegt. Dieser Grenzwert gilt nicht pro Anforderung oder pro Abfrage. Das bedeutet, dass während einer parallelen Abfrageausführung eine einzelne Anforderung mehrere Tasks erzeugen kann, die einem Scheduler zugeordnet sind. Mehr Prozessoren als vom MAXDOP angegeben, können gleichzeitig an einem bestimmten Punkt der Abfrageausführung verwendet werden, wenn verschiedene Aufgaben gleichzeitig ausgeführt werden. Weitere Informationen finden Sie im Handbuch zur Thread- und Taskarchitektur.

Der SQL Server-Abfrageoptimierer verwendet keinen parallelen Ausführungsplan für eine Abfrage, wenn eine der folgenden Bedingungen zutrifft:

• Der serielle Ausführungsplan ist trivial oder überschreitet den Kostenschwellenwert für die Parallelitätseinstellung nicht.
• Der serielle Ausführungsplan hat eine niedrigere geschätzte Unterstrukturkosten als alle parallelen Ausführungsplan, die vom Optimierer untersucht werden.
• Die Abfrage enthält skalare oder relationale Operatoren, die nicht parallel ausgeführt werden können. Bestimmte Operatoren können verursachen, dass ein Abschnitt des Ausführungsplans oder der gesamte Plan im seriellen Modus ausgeführt wird.

Hinweis

Die geschätzten Unterstrukturkosten eines parallelen Plans können unter dem Kostenschwellenwert für die Parallelitätseinstellung liegen. Dies gibt an, dass die geschätzten Unterstrukturkosten des seriellen Plans sie überschritten haben, und der Abfrageplan mit den niedrigeren geschätzten Teilstrukturkosten wurde ausgewählt.

Grad der Parallelität (DOP)

SQL Server erkennt automatisch den am besten geeigneten Grad an Parallelität für jede Instanz einer parallelen Abfrageausführung oder eines DDL-Indizierungsvorgangs (Data Definition Language). Dazu werden die folgenden Kriterien untersucht:

1. Gibt an, ob SQL Server auf einem Computer mit mehr als einem Mikroprozessor oder einer CPU ausgeführt wird, z. B. einem symmetrischen Multiprozessorcomputer (SMP). Nur Computer mit mehreren CPUs können parallele Abfragen verwenden.

2. Sind ausreichend Arbeitsthreads verfügbar? Jeder Abfrage- oder Indexvorgang setzt zu seiner Ausführung eine bestimmte Anzahl von Arbeitsthreads voraus. Das Ausführen eines parallelen Plans erfordert mehr Arbeitsthreads als ein serieller Plan, und die Anzahl der erforderlichen Arbeitsthreads steigt mit dem Grad der Parallelität. Wenn die Arbeitsthreadanforderung des parallelen Plans für einen bestimmten Grad an Parallelität nicht erfüllt werden kann, verringert sql Server Datenbank-Engine den Grad der Parallelität automatisch oder vollständig aufgibt den parallelen Plan im angegebenen Arbeitsauslastungskontext. Stattdessen wird der serielle Plan (ein Arbeitsthread) ausgeführt.

3. Welcher Abfragetyp oder Indexvorgangstyp soll ausgeführt werden? Indexvorgänge, die einen Index erstellen oder neu erstellen oder einen gruppierten Index löschen, sowie Abfragen, die sehr viele CPU-Zyklen beanspruchen, eignen sich am besten für einen parallelen Plan. So sind z. B. Joins großer Tabellen, umfassende Aggregationen und Sortierungen großer Resultsets gut geeignet. Für einfache Abfragen, die häufig in transaktionsverarbeitenden Anwendungen eingesetzt werden, wird der zusätzliche Aufwand, der für die Koordinierung einer parallelen Abfrageausführung erforderlich ist, durch die erwartete Leistungssteigerung in der Regel nicht gerechtfertigt. Um zwischen Abfragen zu unterscheiden, die von Parallelität und denen profitieren, die nicht profitieren, vergleicht sql Server Datenbank-Engine die geschätzten Kosten für die Ausführung des Abfrage- oder Indexvorgangs mit dem Kostenschwellenwert für den Parallelitätswert. Benutzer können den Standardwert 5 mithilfe von sp_configure ändern, wenn durch einen richtigen Test ermittelt wurde, dass ein anderer Wert besser für die ausgeführte Workload geeignet ist.

4. Gibt es eine ausreichende Anzahl von zu verarbeitenden Zeilen? Wenn der Abfrageoptimierer feststellt, dass die Anzahl der Zeilen zu niedrig ist, führt er keine Exchange-Operatoren zum Verteilen der Zeilen ein. Daher werden die Operatoren fortlaufend ausgeführt. Durch das Ausführen der Operatoren in einem seriellen Plan werden Situationen vermieden, in denen die Kosten für Start, Verteilung und Koordinierung den Nutzen übersteigen, der durch die parallele Ausführung der Operatoren erzielt würde.

5. Sind aktuelle Verteilungsstatistiken verfügbar? Wenn der höchste Grad an Parallelität nicht möglich ist, werden niedrigere Grad berücksichtigt, bevor der parallele Plan abgebrochen wird. Wenn Sie beispielsweise einen gruppierten Index für eine Ansicht erstellen, können Verteilungsstatistiken nicht ausgewertet werden, da der gruppierte Index noch nicht vorhanden ist. In diesem Fall kann die SQL Server-Datenbank-Engine nicht den höchsten Parallelitätsgrad für den Indexvorgang bereitstellen. Allerdings können einige Vorgänge, wie z. B. das Sortieren und Scannen, von der parallelen Ausführung profitieren.

Hinweis

Parallele Indexvorgänge sind nur in den Editionen SQL Server Enterprise, Developer und Evaluation verfügbar.

Zur Ausführungszeit bestimmt die SQL Server-Datenbank-Engine, ob die aktuelle Systemauslastung und Konfigurationsinformationen, die zuvor beschrieben wurden, die parallele Ausführung zulassen. Wenn die parallele Ausführung gewährleistet ist, bestimmt die SQL Server-Datenbank-Engine die optimale Anzahl von Arbeitsthreads und verteilt die Ausführung des parallelen Plans über diese Arbeitsthreads. Wenn die parallele Ausführung eines Abfrage- oder Indexvorgangs mit mehreren Arbeitsthreads gestartet wird, wird dieselbe Anzahl an Arbeitsthreads bis zur Beendigung des Vorgangs verwendet. Die SQL Server-Datenbank-Engine überprüft die optimale Anzahl von Arbeitsthreadentscheidungen jedes Mal, wenn ein Ausführungsplan aus dem Plancache abgerufen wird. Bei einer Ausführung einer Abfrage könnte z. B. ein serieller Plan verwendet werden, bei einer späteren Ausführung derselben Abfrage ein paralleler Plan, der drei Arbeitsthreads verwendet, und bei der dritten Ausführung dieser Abfrage ein paralleler Plan, der vier Arbeitsthreads verwendet.

Die Aktualisierungs- und Löschoperatoren in einem parallelen Abfrageausführungsplan werden serial ausgeführt, die Klausel einer UPDATE oder einer DELETE Anweisung kann jedoch WHERE parallel ausgeführt werden. Die eigentlichen Datenänderungen werden anschließend seriell auf die Datenbank angewendet.

Bis zu SQL Server 2012 (11.x) wird der Einfügeoperator auch serial ausgeführt. Der SELECT-Teil einer INSERT-Anweisung kann jedoch parallel ausgeführt werden. Die eigentlichen Datenänderungen werden anschließend seriell auf die Datenbank angewendet.

Ab SQL Server 2014 (12.x) und Datenbankkompatibilitätsebene 110 kann die SELECT ... INTO Anweisung parallel ausgeführt werden. Andere Formen von Einfügeoperatoren funktionieren auf die gleiche Weise wie für SQL Server 2012 (11.x).

Ab SQL Server 2016 (13.x) und Datenbankkompatibilitätsebene 130 kann die INSERT ... SELECT Anweisung parallel ausgeführt werden, wenn sie in Heaps oder gruppierte Spaltenspeicherindizes (CCI) eingefügt wird und die TABLOCK-Hinweise verwenden. Einfügevorgänge in lokale temporäre Tabellen (durch das #-Präfix gekennzeichnet) und in globale temporäre Tabellen (durch das ##-Präfix gekennzeichnet) sind ebenfalls für Parallelität geeignet, wenn der TABLOCK-Hinweis verwendet wird. Weitere Informationen finden Sie unter INSERT (Transact-SQL).

Statische Cursor und keysetgesteuerte Cursor können durch parallele Ausführungspläne aufgefüllt werden. Das spezifische Verhalten dynamischer Cursor kann jedoch nur durch die serielle Ausführung gewährleistet werden. Für eine Abfrage, die Teil eines dynamischen Cursors ist, generiert der Abfrageoptimierer immer einen seriellen Ausführungsplan.

Überschreiben von Parallelitätsgraden

Der Grad an Parallelität legt die Anzahl der bei der Ausführung paralleler Pläne einzusetzenden Prozessoren fest. Diese Konfiguration kann auf verschiedenen Ebenen festgelegt werden:

1. Auf Serverebene mithilfe der Serverkonfigurationsoption für den maximalen Grad an Parallelität (MAXDOP).
   Gilt für: SQL Server

   Hinweis

   SQL Server 2019 (15.x) führt automatische Empfehlungen zum Festlegen der MAXDOP-Serverkonfigurationsoption während des Installationsprozesses ein. Auf der Setupbenutzeroberfläche können Sie entweder die empfohlenen Einstellungen übernehmen oder Ihren eigenen Wert eingeben. Weitere Informationen finden Sie unter Konfiguration der Datenbank-Engine – Seite „MaxDOP“.

2. Auf Arbeitsauslastungsebene mithilfe der Konfigurationsoption für die Resource Governor-ArbeitsauslastungsgruppeMAX_DOP.
   Gilt für: SQL Server

3. Auf Datenbankebene mithilfe der datenbankweit gültigen KonfigurationMAXDOP.
   Gilt für: SQL Server und Azure SQL-Datenbank

4. Auf Abfrage- oder INDEX-Anweisungsebene mithilfe des AbfragehinweisesMAXDOP oder der Indexoption MAXDOP. Sie können z.B. die MAXDOP-Option verwenden, um durch Erhöhen oder Reduzieren eine Steuerung der Anzahl der einem Onlineindexvorgang zugewiesenen Prozessoren zu bewirken. Auf diese Weise können Sie die Ressourcen, die von dem Indexvorgang verwendet werden, mit den Ressourcen gleichzeitiger Benutzer ausgleichen.
   Gilt für: SQL Server und Azure SQL-Datenbank

Durch Festlegen der Option für den maximalen Grad der Parallelität auf 0 (Standard) kann SQL Server alle verfügbaren Prozessoren bis zu maximal 64 Prozessoren in einer parallelen Planausführung verwenden. Obwohl SQL Server ein Laufzeitziel von 64 logischen Prozessoren festlegt, wenn die MAXDOP-Option auf 0 festgelegt ist, kann bei Bedarf ein anderer Wert manuell festgelegt werden. Wenn MAXDOP für Abfragen und Indizes auf 0 (null) festgelegt wurde, kann SQL Server alle verfügbaren Prozessoren (maximal 64) zur Ausführung paralleler Pläne für die jeweiligen Abfragen oder Indizes verwenden. MAXDOP ist kein erzwungener Wert für alle parallelen Abfragen, sondern ein vorläufiges Ziel für alle Abfragen, die für Parallelität geeignet sind. Dies bedeutet, dass, wenn nicht genügend Arbeitsthreads zur Laufzeit verfügbar sind, eine Abfrage mit einem niedrigeren Grad an Parallelität als die MAXDOP-Serverkonfigurationsoption ausgeführt wird.

Tipp

Weitere Informationen finden Sie in den MAXDOP-Empfehlungen für Richtlinien zum Konfigurieren von MAXDOP auf Server-, Datenbank-, Abfrage- oder Hinweisebene.

Beispiel für parallele Abfrage

In der folgenden Abfrage wird die Anzahl der Bestellungen gezählt, die in einem bestimmten Quartal, beginnend mit dem 1. April 2000, aufgegeben wurden und in denen mindestens ein Artikel der Bestellung vom Kunden erst nach dem angekündigten Datum empfangen wurde. Die Abfrage listet die Anzahl dieser Bestellungen gruppiert nach Priorität der Bestellung und in aufsteigender Reihenfolge der Priorität auf.

In diesem Beispiel werden erfundene Tabellen- und Spaltennamen verwendet.

SELECT o_orderpriority, COUNT(*) AS Order_Count
FROM orders
WHERE o_orderdate >= '2000/04/01'
   AND o_orderdate < DATEADD (mm, 3, '2000/04/01')
   AND EXISTS
         (
          SELECT *
            FROM    lineitem
            WHERE l_orderkey = o_orderkey
               AND l_commitdate < l_receiptdate
         )
   GROUP BY o_orderpriority
   ORDER BY o_orderpriority

Nehmen Sie an, dass die folgenden Indizes für die lineitem- und die orders-Tabelle definiert werden:

CREATE INDEX l_order_dates_idx
   ON lineitem
      (l_orderkey, l_receiptdate, l_commitdate, l_shipdate)

CREATE UNIQUE INDEX o_datkeyopr_idx
   ON ORDERS
      (o_orderdate, o_orderkey, o_custkey, o_orderpriority)

Im Folgenden sehen Sie einen möglichen parallelen Plan, der für die zuvor beschriebene Abfrage generiert wurde:

|--Stream Aggregate(GROUP BY:([ORDERS].[o_orderpriority])
                  DEFINE:([Expr1005]=COUNT(*)))
    |--Parallelism(Gather Streams, ORDER BY:
                  ([ORDERS].[o_orderpriority] ASC))
         |--Stream Aggregate(GROUP BY:
                  ([ORDERS].[o_orderpriority])
                  DEFINE:([Expr1005]=Count(*)))
              |--Sort(ORDER BY:([ORDERS].[o_orderpriority] ASC))
                   |--Merge Join(Left Semi Join, MERGE:
                  ([ORDERS].[o_orderkey])=
                        ([LINEITEM].[l_orderkey]),
                  RESIDUAL:([ORDERS].[o_orderkey]=
                        [LINEITEM].[l_orderkey]))
                        |--Sort(ORDER BY:([ORDERS].[o_orderkey] ASC))
                        |    |--Parallelism(Repartition Streams,
                           PARTITION COLUMNS:
                           ([ORDERS].[o_orderkey]))
                        |         |--Index Seek(OBJECT:
                     ([tpcd1G].[dbo].[ORDERS].[O_DATKEYOPR_IDX]),
                     SEEK:([ORDERS].[o_orderdate] >=
                           Apr  1 2000 12:00AM AND
                           [ORDERS].[o_orderdate] <
                           Jul  1 2000 12:00AM) ORDERED)
                        |--Parallelism(Repartition Streams,
                     PARTITION COLUMNS:
                     ([LINEITEM].[l_orderkey]),
                     ORDER BY:([LINEITEM].[l_orderkey] ASC))
                             |--Filter(WHERE:
                           ([LINEITEM].[l_commitdate]<
                           [LINEITEM].[l_receiptdate]))
                                  |--Index Scan(OBJECT:
         ([tpcd1G].[dbo].[LINEITEM].[L_ORDER_DATES_IDX]), ORDERED)

Die folgende Abbildung zeigt einen Abfrageplan, der mit einem Parallelitätsgrad von 4 ausgeführt wird und ein Join von zwei Tabellen einschließt.

Der parallele Plan enthält drei Parallelism-Operatoren. Sowohl der „Index Seek“-Operator des o_datkey_ptr-Indexes als auch der „Index Scan“-Operator des l_order_dates_idx-Indexes werden parallel ausgeführt. Dadurch werden mehrere exklusive Datenströme erzeugt. Dies kann mithilfe der nächsten Parallelism-Operatoren oberhalb der Operatoren „Index Scan“ und „Index Seek“ bestimmt werden. Beide Operatoren nehmen einfach eine Umverteilung der Daten auf die Datenströme vor, sodass dieselbe Anzahl von Datenströmen als Ausgabe erzeugt wird, wie als Eingabe vorlag. Diese Anzahl der Datenströme entspricht dem Grad an Parallelität.

Der „Parallelism“-Operator oberhalb des l_order_dates_idx Index Scan-Operators nimmt mithilfe des Werts für L_ORDERKEY als Schlüssel eine Neueinteilung der Eingabedatenströme vor. Auf diese Weise gelangen identische Werte für L_ORDERKEY in dieselben Ausgabedatenströme. Gleichzeitig behalten die Ausgabedatenströme die Reihenfolge für die L_ORDERKEY-Spalte bei, sodass die Eingabeanforderungen des „Merge Join“-Operators erfüllt sind.

Der „Parallelism“-Operator oberhalb des „Index Seek“-Operators nimmt mithilfe des Werts für O_ORDERKEY eine Neueinteilung der Eingabedatenströme vor. Da die Eingabe nicht in den O_ORDERKEY Spaltenwerten sortiert ist und dies die Verknüpfungsspalte im Merge Join Operator ist, stellen sie vom Sortieroperator zwischen dem Parallelismus und den Zusammenführungsoperatoren sicher, dass die Eingabe für den Merge Join Operator in den Verknüpfungsspalten sortiert ist. Der Sort-Operator wird wie der „Merge Join“-Operator parallel ausgeführt.

Der oberste „Parallelism“-Operator fasst die Ergebnisse von mehreren Datenströmen in einem einzigen Datenstrom zusammen. Teilaggregationen, die vom „Stream Aggregate“-Operator unterhalb des „Parallelism“-Operators vorgenommen werden, werden dann in dem „Stream Aggregate“-Operator oberhalb des „Parallelism“-Operators zu einem einzigen SUM-Wert für jeden Wert von O_ORDERPRIORITY aufsummiert. Dieser Plan verwendet acht Arbeisthreads, da er zwei Austauschsegmente mit einem Parallelitätsgrad von 4 besitzt.

Weitere Informationen zu den in diesem Beispiel verwendeten Operatoren finden Sie in der Referenz zu logischen und physischen Operatoren "Showplan".

Parallele Indexvorgänge

Die für das Erstellen oder Neuerstellen eines Indexes bzw. für das Löschen eines gruppierten Indexes erstellten Abfragepläne ermöglichen parallele Threadvorgänge mit mehreren Workern auf Computern, die über mehrere Mikroprozessoren verfügen.

Hinweis

Parallele Indexvorgänge sind nur in Enterprise Edition verfügbar, beginnend mit SQL Server 2008 (10.0.x).

SQL Server verwendet dieselben Algorithmen, um den Grad der Parallelität (die Gesamtanzahl der auszuführenden separaten Arbeitsthreads) für Indexvorgänge wie bei anderen Abfragen zu bestimmen. Der maximale Grad an Parallelität für einen Indexvorgang hängt von der Serverkonfigurationsoption Max. Grad an Parallelität ab. Der Wert „Max. Grad an Parallelität“ kann für einzelne Indexvorgänge überschrieben werden; legen Sie hierzu die MAXDOP-Indexoption in den Anweisungen CREATE INDEX, ALTER INDEX, DROP INDEX und ALTER TABLE fest.

Wenn der SQL Server-Datenbank-Engine einen Indexausführungsplan erstellt, wird die Anzahl der parallelen Vorgänge auf den niedrigsten Wert aus den folgenden Festgelegt:

• Die Anzahl der Mikroprozessoren (oder CPUs) des Computers.
• Die in der Serverkonfigurationsoption „Max. Grad an Parallelität“ angegebene Anzahl.
• Die Anzahl der CPUs, die noch nicht über einem Schwellenwert für Die Arbeit für SQL Server-Arbeitsthreads ausgeführt wurden.

Auf einem Computer mit acht CPUs und einem Wert für „Max. Grad an Parallelität“ in Höhe von 6 werden z.B. maximal sechs parallele Arbeitsthreads für einen Indexvorgang generiert. Wenn fünf der CPUs auf dem Computer den Schwellenwert der SQL Server-Arbeit überschreiten, wenn ein Indexausführungsplan erstellt wird, gibt der Ausführungsplan nur drei parallele Arbeitsthreads an.

Die Hauptphasen eines parallelen Indexvorgangs umfassen Folgendes:

• Ein koordinierender Arbeitsthread scannt die Tabelle schnell und nach dem Zufallsprinzip, um die Verteilung der Indexschlüssel einzuschätzen. Der koordinierende Arbeitsthread legt die Schlüsselgrenzen fest, die eine Reihe von Schlüsselbereichen erstellen, die dem Grad an parallelen Vorgängen entsprechen, wobei jeder Schlüsselbereich so geschätzt wird, dass eine ähnlich große Anzahl von Zeilen abgedeckt ist. Wenn z.B. vier Millionen Zeilen in einer Tabelle vorhanden sind und der Grad an Parallelität 4 beträgt, bestimmt der koordinierende Arbeitsthread die Schlüsselwerte, die vier Zeilengruppen mit je einer Million Zeilen in jeder Gruppe trennen. Wenn nicht genügend Schlüsselbereiche eingerichtet werden können, um alle CPUs zu verwenden, wird der Grad der Parallelität entsprechend reduziert.
• Der koordinierende Arbeitsthread verteilt eine Reihe von Arbeitsthreads, die dem Grad an parallelen Vorgängen entsprechen, und wartet, dass diese Arbeitsthreads ihre Arbeit beenden. Jeder Arbeitsthread scannt die Basistabelle mithilfe eines Filters, der nur Zeilen mit Schlüsselwerten in dem Bereich abruft, der dem Arbeitsthread zugewiesen ist. Jeder Arbeitsthread erstellt eine Indexstruktur für die Zeilen in seinem Schlüsselbereich. Bei einem partitionierten Index erstellt jeder Arbeitsthread eine angegebene Anzahl an Partitionen. Partitionen werden von Arbeitsthreads nicht gemeinsam genutzt.
• Nachdem alle parallelen Arbeitsthreads abgeschlossen sind, verbindet der koordinierende Arbeitsthread die Untereinheiten des Indexes zu einem einzelnen Index. Diese Phase gilt nur für Offline-Indexvorgänge.

Einzelne CREATE TABLE - oder ALTER TABLE -Anweisungen können über mehrere Einschränkungen verfügen, die die Erstellung eines Indexes erforderlich machen. Diese mehrere Indexerstellungsvorgänge werden in Reihe ausgeführt, obwohl jeder einzelne Indexerstellungsvorgang ein paralleler Vorgang auf einem Computer mit mehreren CPUs sein kann.

Verteilte Abfragearchitektur

Microsoft SQL Server unterstützt zwei Methoden, um auf heterogene OLE DB-Datenquellen in Transact-SQL-Anweisungen zu verweisen:

• Verbindungsservernamen
  Mithilfe der gespeicherten Systemprozeduren sp_addlinkedserver und sp_addlinkedsrvlogin kann einer OLE DB-Datenquelle ein Servername zugewiesen werden. Auf Objekte in diesen Verbindungsservern kann in Transact-SQL-Anweisungen mithilfe von aus vier Teilen bestehenden Namen verwiesen werden. Wenn z.B. der Verbindungsservername DeptSQLSrvr für eine andere Instanz von SQL Server definiert wird, verweist die folgende Anweisung auf eine Tabelle auf diesem Server:

  SELECT JobTitle, HireDate
  FROM DeptSQLSrvr.AdventureWorks2022.HumanResources.Employee;
  

  Der Verbindungsservername kann auch in einer OPENQUERY -Anweisung angegeben werden, um ein Rowset aus einer OLE DB-Datenquelle zu öffnen. In Transact-SQL-Anweisungen kann dann auf dieses Rowset wie auf eine Tabelle verwiesen werden.

• Ad-hoc-Konnektornamen
  Für seltene Verweise auf eine Datenquelle wird die OPENROWSET - oder OPENDATASOURCE -Funktion zusammen mit den Informationen angegeben, die zum Herstellen einer Verbindung mit dem Verbindungsserver erforderlich sind. Auf das Rowset kann dann auf die gleiche Weise verwiesen werden, wie auf eine Tabelle in Transact-SQL-Anweisungen verwiesen wird:

  SELECT *
  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
        'c:\MSOffice\Access\Samples\Northwind.mdb';'Admin';'';
        Employees);
  

SQL Server verwendet OLE DB für die Kommunikation zwischen der relationalen Engine und der Speicher-Engine. Die relationale Engine zerlegt jede Transact-SQL-Anweisung in eine Reihe von Vorgängen für einfache OLE DB-Rowsets, die durch die Speicher-Engine aus den Basistabellen geöffnet werden. Dies bedeutet, dass die relationale Engine einfache OLE DB-Rowsets auch für jede OLE DB-Datenquelle öffnen kann.

Die relationale Engine verwendet die OLE DB-API (Application Programming Interface), um die Rowsets auf Verbindungsservern zu öffnen, die Zeilen abzurufen und Transaktionen zu verwalten.

Auf dem Server, auf dem SQL Server ausgeführt wird, muss für jede OLE DB-Datenquelle, auf die als Verbindungsserver zugegriffen wird, ein OLE DB-Anbieter vorhanden sein. Die Reihe von Transact-SQL-Vorgängen, die für eine bestimmte OLE DB-Datenquelle angewendet werden können, wird durch die Funktionalität des OLE DB-Anbieters bestimmt.

Für jede Instanz von SQL Server können Mitglieder der festen Serverrolle die Verwendung von Ad-hoc-Connectornamen für einen OLE DB-Anbieter mithilfe der sysadmin SQL Server-Eigenschaft DisallowAdhocAccess aktivieren oder deaktivieren. Wenn der Ad-hoc-Zugriff aktiviert ist, kann jeder Benutzer, der sich bei dieser Instanz angemeldet hat, Transact-SQL-Anweisungen ausführen, die Ad-hoc-Connectornamen enthalten, wobei auf jede Datenquelle im Netzwerk verwiesen wird, auf die mit diesem OLE DB-Anbieter zugegriffen werden kann. Um den Zugriff auf Datenquellen zu steuern, können Mitglieder der sysadmin Rolle ad-hoc-Zugriff für diesen OLE DB-Anbieter deaktivieren, wodurch Benutzer nur auf diese Datenquellen beschränkt werden, auf die von den Administratoren definierten verknüpften Servernamen verwiesen wird. Standardmäßig ist der Ad-hoc-Zugriff für den OLE DB-Anbieter von SQL Server aktiviert und für alle anderen OLE DB-Anbieter deaktiviert.

Mithilfe von verteilten Abfragen kann Benutzern der Zugriff auf andere Datenquellen gewährt werden (z.B. auf Dateien, nicht relationale Datenquellen wie Active Directory usw.). Dies geschieht innerhalb des Sicherheitskontexts des Microsoft Windows-Kontos, mit dem der SQL Server-Dienst ausgeführt wird. SQL Server imitiert die Anmeldung entsprechend für Windows-Anmeldungen; Dies ist jedoch für SQL Server-Anmeldungen nicht möglich. Dadurch kann ein verteilter Abfragebenutzer möglicherweise auf eine andere Datenquelle zugreifen, für die er keine Berechtigungen besitzt, aber das Konto, unter dem der SQL Server-Dienst ausgeführt wird, verfügt über Berechtigungen. Verwenden Sie sp_addlinkedsrvlogin , um spezifische Anmeldungen mit Zugriffsrechten für die entsprechenden Verbindungsserver zu definieren. Dieses Steuerelement ist für Ad-hoc-Namen nicht verfügbar. Verwenden Sie daher Vorsicht beim Aktivieren eines OLE DB-Anbieters für ad-hoc-Zugriff.

Wenn möglich, verlagert SQL Server relationale Vorgänge wie Joins, Einschränkungen, Projektionen, Sortierungen und Gruppierungen auf die OLE DB-Datenquelle. SQL Server scannt die Basistabelle nicht standardmäßig in SQL Server und führt die relationalen Vorgänge selbst aus. SQL Server fragt den OLE DB-Anbieter ab, um zu ermitteln, welche Ebene der SQL-Grammatik er unterstützt, und sendet auf der Grundlage dieser Informationen so viele relationale Vorgänge wie möglich an den Anbieter.

SQL Server gibt einen Mechanismus an, mit dem ein OLE DB-Anbieter Statistiken zur Verteilung von Schlüsselwerten innerhalb der OLE DB-Datenquelle zurückgibt. So kann der SQL Server-Abfrageoptimierer das Datenmuster in der Datenquelle im Hinblick auf die Anforderungen jeder Transact-SQL-Anweisung besser analysieren, wodurch der Abfrageoptimierer besser in der Lage ist, optimale Ausführungspläne zu generieren.

Verbesserungen der Abfrageverarbeitung für partitionierte Tabellen und Indizes

SQL Server 2008 (10.0.x) verbesserte Abfrageverarbeitungsleistung für partitionierte Tabellen für viele parallele Pläne, Änderungen, wie parallele und serielle Pläne dargestellt werden, und verbesserten die Partitionierungsinformationen, die sowohl in Kompilierungs- als auch Laufzeitausführungsplänen bereitgestellt werden. Dieser Artikel beschreibt diese Verbesserungen, enthält Anleitungen zum Interpretieren der Abfrageausführungspläne von partitionierten Tabellen und Indizes und bietet bewährte Methoden zur Verbesserung der Abfrageleistung für partitionierte Objekte.

Hinweis

Bis SQL Server 2014 (12.x) werden partitionierte Tabellen und Indizes nur in den EDITIONen SQL Server Enterprise, Developer und Evaluation unterstützt. Ab SQL Server 2016 (13.x) SP1 werden partitionierte Tabellen und Indizes auch in SQL Server Standard Edition unterstützt.

Neuer partitionsfähiger Suchvorgang

In SQL Server wird die interne Darstellung einer partitionierten Tabelle so geändert, dass der Abfrageprozessor die Tabelle für einen mehrspaltigen Index mit PartitionID als führender Spalte hält. PartitionID ist eine verborgene berechnete Spalte, die intern die ID der Partition, die eine bestimmte Zeile enthält, repräsentiert. Beispiel: Die Tabelle T, die als T(a, b, c)definiert ist, wird in Spalte a partitioniert und enthält in Spalte b einen gruppierten Index. In SQL Server wird diese partitionierte Tabelle intern als nicht partitionierte Tabelle mit dem Schema T(PartitionID, a, b, c) und einem gruppierten Index im zusammengesetzten Schlüssel (PartitionID, b)behandelt. Auf diese Weise kann der Abfrageoptimierer Suchvorgänge basierend auf PartitionID in allen partitionierten Tabellen und Indizes durchführen.

Die Partitionsentfernung wird jetzt im Suchvorgang vorgenommen.

Außerdem wurde der Abfrageoptimierer so erweitert, dass jetzt zunächst ein Such- oder Scanvorgang mit einer Bedingung für PartitionID (als logischer führender Spalte) und ggf. für weitere Indexschlüsselspalten durchgeführt werden kann. Anschließend wird dann für jeden eindeutigen Wert, der die Kriterien des Suchvorgangs der ersten Ebene erfüllt hat, ein Suchvorgang der zweiten Ebene mit einer anderen Bedingung in einer oder mehreren zusätzlichen Spalten durchgeführt. Dies bedeutet, dass mit diesem Vorgang, der Skip-Scan genannt wird, der Abfrageoptimierer basierend auf einer Bedingung zunächst einen Such- bzw. Scanvorgang durchführen kann, mit dem die Partitionen ermittelt werden, auf die zugegriffen werden muss, und dann innerhalb dieses Operators einen Indexsuchvorgang der zweiten Ebene, durch den Zeilen in diesen Partitionen zurückgegeben werden, die eine andere Bedingung erfüllen. Sehen Sie sich zum Beispiel die folgende Abfrage an:

SELECT * FROM T WHERE a < 10 and b = 2;

Gehen Sie nun davon aus, dass die Tabelle T, die als T(a, b, c)definiert ist, in Spalte a partitioniert wird und in Spalte b einen gruppierten Index enthält. Die Partitionsgrenzen für Tabelle T werden mit der folgenden Partitionsfunktion definiert:

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (3, 7, 10);

Zur Auflösung der Abfrage führt der Abfrageprozessor zunächst einen Suchvorgang der ersten Ebene durch, in dem alle Partitionen mit Zeilen, die die Bedingung T.a < 10erfüllen, gesucht werden. Hierdurch werden die Partitionen identifiziert, auf die zugegriffen werden muss. In diesen identifizierten Partitionen führt der Prozessor dann einen Suchvorgang der zweiten Ebene im gruppierten Index der Spalte b durch, um die Zeilen zu suchen, die die Bedingung T.b = 2 und T.a < 10erfüllen.

Die folgende Abbildung ist eine logische Darstellung des Skip-Scan-Vorgangs. Sie zeigt die Tabelle T mit Daten in den Spalten a und b. Die Partitionen sind mit 1 bis 4 nummeriert, wobei die Partitionsgrenzen durch gestrichelte vertikale Linien angezeigt werden. Durch einen Suchvorgang der ersten Ebene in den Partitionen (nicht abgebildet) wurde ermittelt, dass die Partitionen 1, 2 und 3 die Suchbedingung, die durch die für die Tabelle definierte Partitionierung und das Prädikat für Spalte a vorgegeben wurde, erfüllen. Das heißt, sie erfüllen die Bedingung T.a < 10. Der vom Suchvorgang der zweiten Ebene innerhalb des Skip-Scan-Vorgangs durchlaufene Pfad ist anhand der Kurve zu erkennen. Im Wesentlichen wird beim Skip-Scan-Vorgang in diesen Partitionen nach Zeilen gesucht, die die Bedingung b = 2erfüllen. Die Gesamtkosten für den Skip-Scan-Vorgang entsprechen den Kosten, die durch drei separate Indexsuchvorgänge entstehen würden.

Partitionierungsinformationen in Abfrageausführungsplänen anzeigen

Sie können die Ausführungspläne für Abfragen in partitionierten Tabellen und Indizes überprüfen, indem Sie die Transact-SQL SET -Anweisung SET SHOWPLAN_XML bzw. SET STATISTICS XMLausführen oder den in SQL Server Management Studio ausgegebenen grafischen Ausführungsplan verwenden. Sie können z. B. den Kompilierungszeitausführungsplan anzeigen, indem Sie auf der Symbolleiste Abfrage-Editor und im Laufzeitplan die Option "Ist-Ausführungsplan einschließen" auswählen.

Mit diesen Tools können Sie die folgenden Informationen abrufen:

• Die Vorgänge, wie z.B. scans, seeks, inserts, updates, mergesund deletes , bei denen auf partitionierte Tabellen oder Indizes zugegriffen wird.
• Die Partitionen, auf die durch die Abfrage zugegriffen wird. So finden sich zum Beispiel in Ausführungsplänen für die Laufzeit Informationen zur Gesamtanzahl der Partitionen sowie zu den Bereichen angrenzender Partitionen, auf die zugegriffen wird.
• Wann Skip-Scan in einem Such- bzw. Scanvorgang verwendet wird, um Daten aus einer oder mehreren Partitionen abzurufen.

Verbesserungen der Partitionsinformationen

SQL Server stellt verbesserte Partitionierungsinformationen sowohl für Kompilierzeit- als auch für Laufzeitausführungspläne bereit. Die Ausführungspläne enthalten jetzt die folgenden Informationen:

• Ein optionales Partitioned -Attribut, das anzeigt, dass für eine partitionierte Tabelle ein Operator wie seek, scan, insert, update, mergeoder deleteausgeführt wird.
• Ein neues SeekPredicateNew -Element mit einem SeekKeys -Unterelement, das PartitionID als führende Indexschlüsselspalte sowie Filterbedingungen enthält, mit denen Bereichssuchen für PartitionIDfestgelegt werden. Das Vorhandensein von zwei SeekKeys -Unterelementen zeigt an, dass für PartitionID ein Skip-Scan-Vorgang verwendet wird.
• Zusammenfassende Informationen mit der Gesamtanzahl der Partitionen, auf die zugegriffen wird. Diese Informationen sind nur in Laufzeitplänen verfügbar.

Nehmen Sie die folgende Abfrage für die partitionierte Tabelle fact_salesals Beispiel zur Veranschaulichung, wie diese Informationen im grafischen Ausführungsplan und in der XML-Showplanausgabe angezeigt werden. Durch diese Abfrage werden Daten in zwei Partitionen aktualisiert.

UPDATE fact_sales
SET quantity = quantity - 2
WHERE date_id BETWEEN 20080802 AND 20080902;

Die folgende Abbildung zeigt die Eigenschaften des Clustered Index Seek-Operators im Laufzeitausführungsplan für diese Abfrage. Informationen zum Anzeigen der Definition der fact_sales Tabelle und der Partitionsdefinition finden Sie unter "Beispiel" in diesem Artikel.

Partitioniertes Attribut

Wenn ein Operator wie eine Indexsuche in einer partitionierten Tabelle oder einem Index ausgeführt wird, wird das Partitioned Attribut im Kompilierungszeit- und Laufzeitplan angezeigt und auf True (1) festgelegt. Das Attribut wird nicht angezeigt, wenn es auf False (0) festgelegt ist.

Das Partitioned -Attribut kann in den folgenden physischen und logischen Operatoren erscheinen:

• Table Scan
• Index Scan
• Index Seek
• Einfügen
• Update
• Löschen
• Merge

Wie in der obigen Abbildung zu sehen, wird das Attribut in den Eigenschaften des Operators, in dem es definiert ist, angezeigt. In der XML-Showplanausgabe erscheint das Attribut als Partitioned="1" im RelOp -Knoten des Operators, in dem es definiert ist.

Neues Seek-Prädikat

In der XML-Showplanausgabe wird das SeekPredicateNew -Element in dem Operator angezeigt, in dem es definiert ist. Sie kann bis zu zwei Vorkommen des SeekKeys Unterelements enthalten. Durch das erste SeekKeys -Element wird der Suchvorgang (SEEK) auf erster Ebene für die Partitions-ID des logischen Index angegeben. In diesem Suchvorgang werden die Partitionen ermittelt, auf die zugegriffen werden muss, damit die Bedingungen der Abfrage erfüllt werden können. Durch das zweite SeekKeys -Element wird der Suchvorgang auf zweiter Ebene innerhalb des Skip-Scan-Vorgangs festgelegt, der in allen Partitionen durchgeführt wird, die im ersten Suchvorgang identifiziert wurden.

Partitionszusammenfassungsinformationen

In Laufzeitausführungsplänen geben die zusammenfassenden Partitionsinformationen Auskunft darüber, auf wie viele und auf welche Partitionen zugegriffen wird. Anhand dieser Informationen können Sie überprüfen, ob in der Abfrage auf die richtigen Partitionen zugegriffen wird und ob alle anderen Partitionen vom Zugriff ausgenommen werden.

Die folgenden Informationen werden bereitgestellt: Actual Partition Countund Partitions Accessed.

Actual Partition Count ist die Gesamtzahl der Partitionen, auf die durch die Abfrage zugegriffen wird.

Partitions Accessedist in der XML-Showplanausgabe die Übersichtsinformation zur Partition, die im neuen RuntimePartitionSummary -Element im RelOp -Knoten des Operators, in dem sie definiert ist, erscheint. Das folgende Beispiel zeigt den Inhalt des RuntimePartitionSummary -Elements, durch den angegeben wird, dass auf insgesamt zwei Partitionen (Partition 2 und 3) zugegriffen wird.

<RunTimePartitionSummary>
    <PartitionsAccessed PartitionCount="2" >
        <PartitionRange Start="2" End="3" />
    </PartitionsAccessed>
</RunTimePartitionSummary>

Anzeigen von Partitionsinformationen mithilfe anderer Showplan-Methoden

Die Showplan-Methoden SHOWPLAN_ALL, SHOWPLAN_TEXTund STATISTICS PROFILE melden Sie die in diesem Artikel beschriebenen Partitionsinformationen nicht mit der folgenden Ausnahme. Als Teil des SEEK -Prädikats werden die Partitionen, auf die zugegriffen werden muss, durch ein Bereichsprädikat für die berechnete Spalte, die die Partitions-ID repräsentiert, identifiziert. Das folgende Beispiel zeigt das SEEK -Prädikat für einen Clustered Index Seek -Operator. Es wird auf die Partitionen 2 und 3 zugegriffen, und der SEEK-Operator filtert die Zeilen heraus, die die Bedingung date_id BETWEEN 20080802 AND 20080902erfüllen.

|--Clustered Index Seek(OBJECT:([db_sales_test].[dbo].[fact_sales].[ci]),
        SEEK:([PtnId1000] >= (2) AND [PtnId1000] \<= (3)
                AND [db_sales_test].[dbo].[fact_sales].[date_id] >= (20080802)
                AND [db_sales_test].[dbo].[fact_sales].[date_id] <= (20080902))
                ORDERED FORWARD)

Interpretieren von Ausführungsplänen für partitionierte Heaps

Ein partitionierter Heap wird als logischer Index für die Partitions-ID behandelt. Die Partitionsentfernung für einen partitionierten Heap wird in einem Ausführungsplan als Table Scan -Operator mit einem SEEK -Prädikat für die Partitions-ID dargestellt. Das folgende Beispiel zeigt die bereitgestellten Showplan-Informationen:

|-- Table Scan (OBJECT: ([db].[dbo].[T]), SEEK: ([PtnId1001]=[Expr1011]) ORDERED FORWARD)

Interpretieren von Ausführungsplänen für verbundene Verknüpfungen

Eine Anordnung von Joins kann eintreten, wenn zwei Tabellen mit derselben oder einer ähnlichen Partitionsfunktion partitioniert und die Partitionierungsspalten auf beiden Seiten des Joins in der Join-Bedingung der Abfrage angegeben werden. Der Abfrageoptimierer kann einen Plan erzeugen, in dem die Partitionen aller Tabellen mit identischer Partitions-ID separat verknüpft werden. Angeordnete Joins sind jedoch möglicherweise schneller als nicht angeordnete, da sie ggf. weniger Arbeitsspeicher und weniger Verarbeitungszeit benötigen. Die Entscheidung, ob ein Plan für nicht angeordnete oder angeordnete Joins erzeugt wird, fällt auf Grundlage der geschätzten Kosten.

Bei einem Plan für angeordnete Joins liest der Nested Loops -Join eine oder mehrere zusammengefasste Tabellen- oder Indexpartitionen auf der Innenseite. Die Zahlen in den Constant Scan -Operatoren repräsentieren die Partitionsnummern.

Wenn parallele Pläne für angeordnete Joins für partitionierte Tabellen oder Indizes erzeugt werden, wird ein Parallelism-Operator zwischen dem Constant Scan -Joinoperator und dem Nested Loops -Joinoperator eingefügt. In diesem Fall lesen und bearbeiten mehrere Arbeitsthreads auf der Außenseite des Joins jeweils eine andere Partition.

Die folgende Abbildung zeigt einen parallelen Abfrageplan für einen angeordneten Join.

Parallele Abfrageausführungsstrategie für partitionierte Objekte

Der Abfrageprozessor verwendet eine parallele Ausführungsstrategie für Abfragen bei partitionierten Objekten. Im Rahmen der Ausführungsstrategie bestimmt der Abfrageprozessor die für die Abfrage erforderlichen Tabellenpartitionen und den Anteil der Workerthreads, die jeder Partition zugeordnet werden sollen. In den meisten Fällen ordnet der Abfrageprozessor den einzelnen Partitionen eine etwa gleich große Anzahl an Arbeitsthreads zu und führt anschließend die Abfrage partitionsübergreifend parallel aus. In den folgenden Absätzen wird die Arbeitsthreadzuordnung näher erläutert.

Wenn die Arbeitsthreadanzahl kleiner ist als die Partitionsanzahl, ordnet der Abfrageprozessor jeden Arbeitsthread einer anderen Partition zu, und zunächst verbleiben eine oder mehrere Partitionen ohne Arbeitsthreadzuordnung. Wenn die Ausführung eines Arbeitsthreads für eine Partition abgeschlossen ist, weist der Abfrageprozessor diesen der nächsten Partition zu, bis jeder Partition ein Arbeitsthread zugewiesen wurde. Dies ist der einzige Fall, in dem der Abfrageprozessor Arbeitsthreads anderen Partitionen neu zuordnet.

Zeigt einen Arbeitsthread, der nach seinem Abschluss erneut zugeordnet wurde Wenn die Anzahl an Arbeitsthreads und an Partitionen gleich ist, wird jeder Partition ein Arbeitsthread zugewiesen. Wenn ein Arbeitsthread abgeschlossen ist, wird er nicht in eine andere Partition aufgeteilt.

Wenn die Arbeitsthreadanzahl größer ist als die Partitionsanzahl, wird jeder Partition dieselbe Anzahl an Arbeitsthreads zugewiesen. Wenn die Anzahl der Arbeitsthreads kein genaues Vielfaches der Anzahl der Partitionen ist, weist der Abfrageprozessor einigen Partitionen einen zusätzlichen Arbeitsthread zu, um alle verfügbaren Arbeitsthreads zu verwenden. Wenn nur eine Partition vorhanden ist, werden alle Arbeitsthreads dieser Partition zugewiesen. In der Abbildung unten sind vier Partitionen und 14 Arbeitsthreads verfügbar. Jeder Partition werden drei Arbeitsthreads zugewiesen, und zwei Partitionen wird jeweils ein zusätzlicher Arbeitsthread zugewiesen, sodass alle 14 Arbeitsthreads zugewiesen sind. Wenn ein Arbeitsthread abgeschlossen ist, wird er nicht einer anderen Partition zugewiesen.

Die oben aufgeführten Beispiele sind einfache Beschreibungen der Arbeitsthreadzuordnung. Die tatsächliche Strategie ist komplexer und umfasst weitere Variablen, die sich während der Abfrageausführung ergeben. Beispiel: Wenn die Tabelle partitioniert ist, in Spalte A einen gruppierten Index aufweist und eine Abfrage mit der Prädikatklausel WHERE A IN (13, 17, 25)verwendet wird, weist der Abfrageprozessor jedem dieser drei Suchwerte (A=13, A=17 und A=25) statt jeder Tabellenpartition einen oder mehrere Arbeitsthreads zu. Die Abfrage muss nur für die Partitionen ausgeführt werden, die diese Werte enthalten. Wenn sich alle Suchwerte in derselben Partition befinden, werden alle Arbeitsthreads dieser Partition zugewiesen.

Ein weiteres Beispiel: Die Tabelle weist vier Partitionen in Spalte A mit Grenzpunkten (10, 20, 30) sowie einen Index in Spalte B auf, und für die Abfrage wird folgende Prädikatklausel verwendet: WHERE B IN (50, 100, 150). Da die Tabellenpartitionen auf den A-Werten basieren, können die B-Werte in allen Tabellenpartitionen enthalten sein. Somit sucht der Abfrageprozessor in jeder der vier Tabellenpartitionen nach jedem der drei B-Werte (50, 100, 150). Der Abfrageprozessor weist Arbeitsthreads proportional zu, sodass alle zwölf Abfragesuchläufe parallel ausgeführt werden können.

Tabellenpartitionen auf Grundlage der Spalte A	Suche in allen Tabellenpartitionen nach B-Spaltenwerten
Tabellenpartition 1: A < 10	B=50, B=100, B=150
Tabellenpartition 2: A >= 10 AND A < 20	B=50, B=100, B=150
Tabellenpartition 3: A >= 20 AND A < 30	B=50, B=100, B=150
Tabellenpartition 4: A > 30	B=50, B=100, B=150

Bewährte Methoden

Wir empfehlen die folgenden bewährten Vorgehensweisen, um die Leistung von Abfragen zu verbessern, bei denen in großen partitionierten Tabellen und Indizes auf eine große Menge von Daten zugegriffen wird:

• Verteilen Sie alle Partitionen über viele Datenträger (Datenträgerstriping). Dies ist besonders bei Verwendung von Festplatten relevant.
• Verwenden Sie nach Möglichkeit einen Server mit genügend Standard Arbeitsspeicher, um häufig verwendete Partitionen oder alle Partitionen im Arbeitsspeicher zu passen, um die E/A-Kosten zu reduzieren.
• Wenn die daten, die Sie abfragen, nicht in den Arbeitsspeicher passen, komprimieren Sie die Tabellen und Indizes. Dies reduziert die E/A-Kosten.
• Verwenden Sie einen Server mit schnellen und möglichst vielen Prozessoren, um sich die Vorteile der parallelen Abfrageverarbeitung zu Nutze zu machen.
• Stellen Sie sicher, dass der Server über eine ausreichend große E/A-Controllerbandbreite verfügt.
• Erstellen Sie für jede große partitionierte Tabelle einen gruppierten Index, um den optimierten B-Strukturscan voll nutzen zu können.
• Beachten Sie die Empfehlungen für bewährte Vorgehensweisen im Whitepaper The Data Loading Performance Guide (Leistungsleitfaden für das Laden von Daten), wenn Sie mittels Massenladen Daten in partitionierte Tabellen laden.

Beispiel

Im folgenden Beispiel wird eine Testdatenbank mit einer Tabelle, die sieben Partitionen aufweist, erstellt. Verwenden Sie die zuvor in diesem Thema vorgestellten Tools, wenn Sie die Abfragen in diesem Beispiel durchführen, um Partitionierungsinformationen für den Kompilierungszeitplan und den Laufzeitplan anzuzeigen.

Hinweis

In diesem Beispiel werden über eine Millionen Zeilen in die Tabelle eingefügt. Das Ausführen dieses Beispiels kann je nach Hardware mehrere Minuten dauern. Stellen Sie vor dem Ausführen dieses Beispiels sicher, dass mehr als 1,5 GB Speicherplatz zur Verfügung stehen.

USE master;
GO
IF DB_ID (N'db_sales_test') IS NOT NULL
    DROP DATABASE db_sales_test;
GO
CREATE DATABASE db_sales_test;
GO
USE db_sales_test;
GO
CREATE PARTITION FUNCTION [pf_range_fact](int) AS RANGE RIGHT FOR VALUES
(20080801, 20080901, 20081001, 20081101, 20081201, 20090101);
GO
CREATE PARTITION SCHEME [ps_fact_sales] AS PARTITION [pf_range_fact]
ALL TO ([PRIMARY]);
GO
CREATE TABLE fact_sales(date_id int, product_id int, store_id int,
    quantity int, unit_price numeric(7,2), other_data char(1000))
ON ps_fact_sales(date_id);
GO
CREATE CLUSTERED INDEX ci ON fact_sales(date_id);
GO
PRINT 'Loading...';
SET NOCOUNT ON;
DECLARE @i int;
SET @i = 1;
WHILE (@i<1000000)
BEGIN
    INSERT INTO fact_sales VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() - 25, (@i%3) + 1, '');
    SET @i += 1;
END;
GO
DECLARE @i int;
SET @i = 1;
WHILE (@i<10000)
BEGIN
    INSERT INTO fact_sales VALUES(20080900 + (@i%30) + 1, @i%10000, @i%200, RAND() - 25, (@i%3) + 1, '');
    SET @i += 1;
END;
PRINT 'Done.';
GO
-- Two-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080802 AND 20080902
GROUP BY date_id ;
GO
SET STATISTICS XML OFF;
GO
-- Single-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080801 AND 20080831
GROUP BY date_id;
GO
SET STATISTICS XML OFF;
GO

Zugehöriger Inhalt

• Referenz zu logischen und physischen Showplanoperatoren
• Erweiterte Ereignisübersicht
• Bewährte Methoden für die Überwachung von Workloads mit Abfragespeicher
• Kardinalitätsschätzung (SQL Server)
• Intelligente Abfrageverarbeitung in SQL-Datenbanken
• Operatorrangfolge (Transact-SQL)
• Übersicht über den Ausführungsplan
• Leistungscenter für SQL Server-Datenbank-Engine und Azure SQL-Datenbank