Anleitung zur Abfrageverarbeitung für speicheroptimierte Tabellen

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

Mit In-Memory OLTP werden speicheroptimierte Tabellen und systemintern kompilierte gespeicherte Prozeduren in SQL Server eingeführt. Dieser Artikel gibt eine Übersicht über die Abfrageverarbeitung für speicheroptimierte Tabellen und systemintern kompilierte gespeicherte Prozeduren.

In diesem Dokument wird erläutert, wie Abfragen in speicheroptimierten Tabellen kompiliert und ausgeführt werden. Die Themen umfassen:

• Abfrageverarbeitungspipeline in SQL Server für datenträgerbasierte Tabellen.

• Abfrageoptimierung; die Rolle der Statistiken für speicheroptimierte Tabellen sowie der Richtlinien für die Problembehandlung von ungültigen Abfrageplänen

• Verwendung von interpretiertem Transact-SQL für den Zugriff auf speicheroptimierte Tabellen.

• Überlegungen zur Abfrageoptimierung für den Zugriff auf speicheroptimierte Tabellen

• Kompilierung und Verarbeitung nativ kompilierter gespeicherter Prozeduren

• Statistiken, die der Abfrageoptimierer zur Kostenschätzung verwendet.

• Möglichkeiten zur Reparatur ungültiger Abfragepläne

Beispielabfrage

Im folgenden Beispiel werden die Abfrageverarbeitungskonzepte veranschaulicht, die in diesem Artikel erläutert werden.

Wir gehen von zwei Tabellen aus: "Customer" und "Order". Das folgende Transact-SQL-Skript enthält die Definitionen für diese beiden Tabellen und zugehörige Indizes in ihrer (herkömmlichen) datenträgerbasierten Form:

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY,  
  ContactName nvarchar (30) NOT NULL   
)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY,  
  CustomerID nchar (5) NOT NULL,  
  OrderDate date NOT NULL  
)  
GO  
CREATE INDEX IX_CustomerID ON dbo.[Order](CustomerID)  
GO  
CREATE INDEX IX_OrderDate ON dbo.[Order](OrderDate)  
GO  

Zum Erstellen der in diesem Artikel dargestellten Abfragepläne werden die beiden Tabellen mit Beispieldaten aus der Northwind-Beispieldatenbank aufgefüllt. Diese können Sie von Northwind and pubs Sample Databases for SQL Server 2000(Northwind and pubs-Beispieldatenbanken für SQL Server 2000) herunterladen.

Betrachten wir die folgende Abfrage, in der die Tabellen "Customer" und "Order" verknüpft sind und die die Bestell-ID und die zugehörigen Kundeninformationen zurückgibt:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

Der geschätzte Ausführungsplan wird von SQL Server Management Studio wie folgt angezeigt

Abfrageplan für einen Join datenträgerbasierter Tabellen.

Informationen zu diesem Abfrageplan:

• Die Zeilen der Customer-Tabelle werden aus dem gruppierten Index abgerufen, der die primäre Datenstruktur darstellt und die vollständigen Tabellendaten enthält.

• Daten aus der Tabelle „Order“ werden mithilfe des nicht gruppierten Index auf der Spalte CustomerID abgerufen. Dieser Index enthält die Spalte CustomerID, die für diesen Join verwendet wird, und die Primärschlüsselspalte OrderID, die an den Benutzer zurückgegeben wird. Das Zurückgeben zusätzlicher Spalten aus der Order-Tabelle würde Suchen im gruppierten Index für die Order-Tabelle erfordern.

• Der logische Operator Inner Join wird vom physischen Operator Merge Joinimplementiert. Die anderen physischen Jointypen sind Nested Loops und Hash Join. Der Merge Join -Operator nutzt die Tatsache, dass beide Indizes nach der Joinspalte CustomerID sortiert werden.

Sehen Sie sich diese leichte Abwandlung dieser Abfrage an, die alle Spalten aus der Order-Tabelle und nicht nur die Spalte „OrderID“ zurückgibt:

SELECT o.*, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

Der geschätzte Plan für diese Abfrage ist:

Abfrageplan für einen Hashjoin datenträgerbasierter Tabellen.

In dieser Abfrage werden Zeilen aus der Order-Tabelle mithilfe des gruppierten Indexes abgerufen. Der physische Operator Hash Match wird jetzt für Inner Joinverwendet. Der gruppierte Index für Order wird nicht nach CustomerID sortiert. Deshalb würde Merge Join einen Sortieroperator erfordern, der sich auf die Leistung auswirkt. Beachten Sie die relativen Kosten des Hash Match -Operators (75%), verglichen mit den Kosten des Merge Join -Operators im vorherigen Beispiel (46%). Der Optimierer hätte den Hash Match -Operator auch im vorherigen Beispiel in Betracht gezogen, hat aber festgestellt, dass der Merge Join -Operator eine bessere Leistung bietet.

SQL Server Abfrageverarbeitung für datenträgerbasierte Tabellen

Das folgende Diagramm zeigt den Abfrageverarbeitungsfluss in SQL Server für Ad-hoc-Abfragen:

Abfrageverarbeitungspipeline in SQL Server.

Szenario:

1. Der Benutzer gibt eine Abfrage aus.

2. Der Parser und die Algebraisierung erstellen eine Abfragestruktur mit logischen Operatoren auf Grundlage des Transact-SQL-Texts, der vom Benutzer bereitgestellt wurde.

3. Der Optimierer erstellt einen optimierten Abfrageplan, der physische Operatoren enthält (beispielsweise einen Nested-Loops-Join). Nach der Optimierung kann der Plan im Plancache gespeichert werden. Dieser Schritt wird umgangen, wenn der Plancache bereits einen Plan für diese Abfrage enthält.

4. Die Abfrageausführungs-Engine verarbeitet eine Interpretation des Abfrageplans.

5. Für jeden Indexsuch-, Indexscan- und Tabellenscan-Operator fordert die Ausführungs-Engine von Access Methods Zeilen aus den jeweiligen Index- und Tabellenstrukturen an.

6. Über Zugriffsmethoden werden die Zeilen aus den Index- und Datenseiten im Pufferpool abgerufen und Seiten nach Bedarf vom Datenträger in den Pufferpool geladen.

Für die erste Beispielabfrage fordert das Ausführungsmodul bei den Zugriffsmethoden Zeilen aus dem Clustered Index auf Customer und dem Nonclustered Index auf Order an. Die Zugriffsmethode durchläuft die B-Baum-Indexstrukturen, um die angeforderten Zeilen abzurufen. In diesem Fall werden alle Zeilen abgerufen, da der Plan vollständige Indexscans fordert.

Zugriff auf speicheroptimierte Tabellen mit interpretiertem Transact-SQL

Transact-SQL ad-hoc-Batches und gespeicherte Prozeduren werden auch als interpretiertes Transact-SQL betrachtet. "Interpretiert" bezieht sich auf die Tatsache, dass der Abfrageplan von der Abfrageausführungs-Engine für jeden Operator im Abfrageplan interpretiert wird. Die Ausführungs-Engine liest den Operator und die Parameter und führt den Vorgang aus.

Interpretiertes Transact-SQL kann verwendet werden, um auf speicheroptimierte und datenträgerbasierte Tabellen zuzugreifen. Die folgende Abbildung veranschaulicht die Abfrageverarbeitung für den interpretierten Transact-SQL-Zugriff auf speicheroptimierte Tabellen:

Abfrageverarbeitungspipeline für interpretierten Transact-SQL-Zugriff auf speicheroptimierte Tabellen.

Wie in der Abbildung veranschaulicht, bleibt die Abfrageverarbeitungspipeline größtenteils unverändert:

• Der Parser und der Algebraisierer erstellen den Abfragebaum.

• Der Optimierer erstellt den Ausführungsplan.

• Die Abfrageausführungs-Engine interpretiert den Ausführungsplan.

Der Hauptunterschied zur herkömmlichen Abfrageverarbeitungspipeline (Abbildung 2) ist, dass Zeilen für speicheroptimierte Tabellen nicht über Zugriffsmethoden aus dem Pufferpool abgerufen werden. Stattdessen werden Zeilen mit der In-Memory-OLTP-Engine aus den speicherresidenten Datenstrukturen abgerufen. Aufgrund der Unterschiede in den Datenstrukturen wählt der Abfrageoptimierer in manchen Fällen verschiedene Pläne aus, wie im folgenden Beispiel veranschaulicht wird.

Das folgende Transact-SQL-Skript enthält speicheroptimierte Versionen der Order- und Customer-Tabellen, wobei Hashindizes verwendet werden:

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY NONCLUSTERED,  
  ContactName nvarchar (30) NOT NULL   
) WITH (MEMORY_OPTIMIZED=ON)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY NONCLUSTERED,  
  CustomerID nchar (5) NOT NULL INDEX IX_CustomerID HASH(CustomerID) WITH (BUCKET_COUNT=100000),  
  OrderDate date NOT NULL INDEX IX_OrderDate HASH(OrderDate) WITH (BUCKET_COUNT=100000)  
) WITH (MEMORY_OPTIMIZED=ON)  
GO  

Betrachten wir die gleiche Abfrage bei der Ausführung mit speicheroptimierten Tabellen:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

Der geschätzte Plan lautet wie folgt:

Abfrageplan für den Join von speicheroptimierten Tabellen.

Beachten Sie die folgenden Unterschiede beim Plan für die gleiche Abfrage mit datenträgerbasierten Tabellen (Abbildung 1):

• Dieser Plan enthält einen Tabellenscan anstelle eines Scans eines gruppierten Indexes für die Tabelle Customer:

  • Die Definition der Tabelle enthält keinen gruppierten Index.

  • Gruppierte Indizes werden nicht bei speicheroptimierten Tabellen unterstützt. Stattdessen muss jede speicheroptimierte Tabelle mindestens über einen nicht gruppierten Index verfügen, und alle Indizes für speicheroptimierte Tabellen können effizient auf alle Spalten in der Tabelle zugreifen, ohne dass sie im Index gespeichert werden müssen oder dass auf einen gruppierten Index zurückgegriffen werden muss.

• Dieser Plan enthält ein Hash Match anstelle eines Merge Join. Die Indizes der Tabellen Order und Customer sind Hashindizes und daher nicht geordnet. Ein Merge Join würde Sortieroperatoren erfordern, die die Leistung verringern würden.

Nativ kompilierte gespeicherte Prozeduren

Systemintern kompilierte gespeicherte Prozeduren sind gespeicherte Transact-SQL-Prozeduren, die in Computercode kompiliert werden, statt durch die Abfrageausführungs-Engine interpretiert zu werden. Das folgende Skript erstellt eine systemintern kompilierte gespeicherte Prozedur, die die Beispielabfrage ausführt (aus dem Abschnitt Beispielabfrage).

CREATE PROCEDURE usp_SampleJoin  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH   
(  TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
  LANGUAGE = 'english')  
  
  SELECT o.OrderID, c.CustomerID, c.ContactName   
FROM dbo.[Order] o INNER JOIN dbo.[Customer] c   
  ON c.CustomerID = o.CustomerID  
  
END  

Systemintern kompilierte gespeicherte Prozeduren werden zur Erstellungszeit kompiliert, während interpretierte gespeicherte Prozeduren bei der ersten Ausführung kompiliert werden. (Ein Teil der Kompilierung, insbesondere Analyse und Algebraisierung, findet bei der Erstellung statt. Bei interpretierten gespeicherten Prozeduren erfolgt die Optimierung der Abfragepläne jedoch bei der ersten Ausführung.) Die Neukompilierungslogik ist ähnlich. Systemintern kompilierte gespeicherte Prozeduren werden bei der ersten Ausführung der Prozedur erneut kompiliert, wenn der Server neu gestartet wird. Interpretierte gespeicherte Prozeduren werden erneut kompiliert, wenn sich der Plan nicht mehr im Plancache befindet. Die folgende Tabelle fasst die Kompilierungs- und Neukompilierungsfälle sowohl für nativ kompilierte als auch für interpretierte gespeicherte Prozeduren zusammen:

Kompilierung und Abfrageverarbeitung

Das folgende Diagramm veranschaulicht den Kompilierungsprozess für nativ kompilierte gespeicherte Prozeduren:

Systeminterne Kompilierung gespeicherter Prozeduren.

Der Prozess lässt sich folgendermaßen beschreiben:

1. Der Benutzer gibt eine CREATE PROCEDURE Anweisung für SQL Server aus.

2. Der Parser und die Algebraisierung erstellen den Verarbeitungsfluss für die Prozedur sowie die Abfragestrukturen für die Transact-SQL-Abfragen in der gespeicherten Prozedur.

3. Der Abfrageoptimierer erstellt optimierte Abfrageausführungspläne für alle Abfragen in der gespeicherten Prozedur.

4. Der In-Memory OLTP-Compiler nimmt den Verarbeitungsfluss mit eingebetteten optimierten Abfrageplänen auf und generiert eine DLL, die den Computercode für die Ausführung der gespeicherten Prozedur enthält.

5. Die generierte DLL wird in den Arbeitsspeicher geladen.

Der Aufruf einer systemintern kompilierten gespeicherten Prozedur wird in einen Funktionsaufruf in der DLL übersetzt.

Ausführung systemintern kompilierter gespeicherten Prozeduren.

Der Aufruf einer systemintern kompilierten gespeicherten Prozedur lässt sich folgendermaßen beschreiben:

1. Der Benutzer gibt eine EXECusp_myproc-Anweisung aus.

2. Der Parser extrahiert den Namen und die Parameter der gespeicherten Prozedur.

   Wenn die Anweisung vorbereitet wurde, zum Beispiel mit sp_prep_exec, muss der Parser den Prozedurnamen und die Parameter bei der Ausführung nicht extrahieren.

3. Die In-Memory OLTP-Laufzeit sucht den DLL-Einstiegspunkt für die gespeicherte Prozedur.

4. Der Computercode in der DLL wird ausgeführt, und die Ergebnisse werden an den Client zurückgegeben.

Parameterermittlung

Interpretierte gespeicherte Transact-SQL-Prozeduren werden im Gegensatz zu systemintern kompilierten gespeicherten Prozeduren, die zur Erstellungszeit kompiliert werden, bei der ersten Ausführung kompiliert. Wenn interpretierte gespeicherte Prozeduren beim Aufruf kompiliert werden, werden die Werte der Parameter, die für diesen Aufruf angegeben werden, bei der Erstellung des Ausführungsplans vom Abfrageoptimierer verwendet. Diese Verwendung der Parameter bei der Kompilierung wird als Parameter Sniffing bezeichnet.

Die Parameterermittlung wird nicht zum Kompilieren von systemintern kompilierten gespeicherten Prozeduren verwendet. Es wird angenommen, dass alle Parameter für die gespeicherte Prozedur UNBEKANNTE Werte haben. Systemintern kompilierte gespeicherte Prozeduren unterstützen genauso wie interpretierte gespeicherte Prozeduren den OPTIMIZE FOR -Hinweis. Weitere Informationen finden Sie unter Abfragehinweise (Transact-SQL).

Abrufen eines Abfrageausführungsplans für systemintern kompilierte gespeicherte Prozeduren

Der Abfrageausführungsplan für eine nativ kompilierte gespeicherte Prozedur kann mithilfe des geschätzten Ausführungsplans in Management Studio oder mithilfe der SHOWPLAN_XML Option in Transact-SQL abgerufen werden. Zum Beispiel:

SET SHOWPLAN_XML ON  
GO  
EXEC dbo.usp_myproc  
GO  
SET SHOWPLAN_XML OFF  
GO  

Der Ausführungsplan, der vom Abfrageoptimierer erstellt wird, besteht aus einer Struktur mit Abfrageoperatoren für die Knoten und Blätter der Struktur. Die Struktur des Baums legt die Interaktion (den Fluss von Zeilen von einem Operator zum anderen) zwischen den Operatoren fest. In der grafischen Darstellung von SQL Server Management Studio verläuft der Fluss von rechts nach links. Beispielsweise enthält der Abfrageplan in Abbildung 1 zwei Indexscanoperatoren, die einem Mergejoin-Operator Zeilen bereitstellen. Der Merge Join-Operator übergibt Zeilen an den Select-Operator. Der Select-Operator gibt schließlich die Zeilen an den Client zurück.

Abfrageoperatoren in systemintern kompilierten gespeicherten Prozeduren

In der folgenden Tabelle werden die unterstützten Abfrageoperatoren in systemintern kompilierten gespeicherten Prozeduren zusammengefasst:

Spaltenstatistiken und Verknüpfungen

In SQL Server werden Statistiken für Werte in den Indexschlüsselspalten beibehalten, damit die Kosten für bestimmte Vorgänge wie Indexscans und Indexsuchen geschätzt werden können. (Von SQL Server werden auch Statistiken zu Nicht-Indexschlüsselspalten erstellt, wenn Sie sie explizit erstellen, oder wenn sie vom Abfrageoptimierer in Reaktion auf eine Abfrage mit einem Prädikat erstellt werden.) Das wesentliche Maß für die Kostenschätzung ist die Anzahl der Zeilen, die von einem einzelnen Operator verarbeitet werden. Beachten Sie, dass für datenträgerbasierte Tabellen die Anzahl der Seiten, auf die von einem bestimmten Operator zugegriffen wird, für die Kostenschätzung maßgeblich ist. Da die Seitenanzahl bei speicheroptimierten Tabellen jedoch nicht von Bedeutung ist (da sie immer 0 ist), steht hier die Zeilenanzahl im Vordergrund. Die Schätzung beginnt mit den Operatoren für Index Seek und Index Scan im Plan und wird dann auf weitere Operatoren erweitert, beispielsweise den Join-Operator. Die geschätzte Anzahl der von einem Joinoperator zu verarbeitenden Zeilen basiert auf den Schätzungen für die zugrunde liegenden Index-, Seek- und Scan-Operatoren. Beim interpretierten Transact-SQL-Zugriff auf speicheroptimierte Tabellen können Sie den tatsächlichen Ausführungsplan beobachten, um den Unterschied zwischen der geschätzten und der tatsächlichen Zeilenanzahl für die Operatoren im Plan herauszufinden.

Für das Beispiel in Abbildung 1 gilt:

• Der Scan des gruppierten Indexes für Customer ergibt 91 geschätzte und 91 tatsächliche Zeilen.
• Der Scan des nichtgruppierten Indexes auf CustomerID hat geschätzt 830; tatsächlich 830.
• Der Merge Join-Operator ergibt 815 geschätzte und 830 tatsächliche Zeilen.

Die Schätzungen für die Indexscans sind genau. SQL Server behält die Zeilenanzahl für datenträgerbasierte Tabellen bei. Schätzungen für vollständige Tabellen- und Indexscans sind immer genau. Auch die Schätzung für die Verknüpfung ist recht genau.

Wenn sich diese Schätzungen ändern, ändern sich auch die Kostenüberlegungen für verschiedene Planalternativen. Wenn beispielsweise eine der Join-Seiten eine geschätzte Zeilenanzahl von 1 oder nur wenigen Zeilen hat, ist die Verwendung eines Nested-Loops-Joins weniger aufwendig. Betrachten Sie die folgende Abfrage:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

Nach Löschung aller Zeilen bis auf eine in der Customer-Tabelle wird der folgende Abfrageplan erzeugt:

Bezüglich dieses Abfrageplans:

• Der Hash Match wurde durch den physischen Join-Operator „Nested Loops“ ersetzt.
• Der vollständige Indexscan für IX_CustomerID wurde durch eine Indexsuche ersetzt. Dies führte zum Scannen von 5 Zeilen anstelle der für den vollständigen Indexscan erforderlichen 830 Zeilen.

Siehe auch

Speicheroptimierte Tabellen