Anleitung zur Abfrageverarbeitung für speicheroptimierte Tabellen

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

In-Memory OLTP führt speicheroptimierte Tabellen und systemeigene kompilierte gespeicherte Prozeduren in SQL Server ein. 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:

• Die 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

• Die Verwendung interpretierter Transact-SQL für den Zugriff auf speicheroptimierte Tabellen.

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

• Kompilierung und Verarbeitung systemintern kompilierter gespeicherter Prozeduren

• Statistiken, die vom Abfrageoptimierer für die Kostenschätzung verwendet werden.

• 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 zugeordneten 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 von SQL Server Management Studio angezeigte geschätzte Ausführungsplan lautet wie folgt:

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 Indexes für die CustomerID-Spalte 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 beschreibt 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 Algebrizer erstellen eine Abfragestruktur mit logischen Operatoren basierend auf dem vom Benutzer übermittelten Transact-SQL-Text.

3. Der Abfrageoptimierer erstellt einen optimierten Abfrageplan, der physische Operatoren enthält (beispielsweise einen Join geschachtelter Schleifen). 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 Index Seek-, Index Scan- und Table Scan-Operator fordert die Ausführungs-Engine Zeilen aus den entsprechenden Index- und Tabellenstrukturen von Zugriffsmethoden 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 die Ausführungs-Engine von Zugriffsmethoden Zeilen im gruppierten Index für Customer und im nicht gruppierten Index für Order an. Zugriffsmethoden durchläuft die B-Struktur-Indexstrukturen, um die angeforderten Zeilen abzurufen. In diesem Fall werden alle Zeilen abgerufen, da der Plan vollständige Indexscans fordert.

Interpretierter Transact-SQL-Zugriff auf speicheroptimierte Tabellen

Transact-SQL-Ad-hoc-Batches und gespeicherte Prozeduren werden auch als interpretierte Transact-SQL bezeichnet. "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.

Interpretierte Transact-SQL kann für den Zugriff auf speicheroptimierte und datenträgerbasierte Tabellen verwendet werden. Die folgende Abbildung zeigt die Abfrageverarbeitung für 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 die Algebraisierung erstellen die Abfragestruktur.

• Der Abfrageoptimierer 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 Tabellen "Order" und "Customer" mithilfe von Hashindizes:

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 speicheroptimierter 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 statt eines Scans des gruppierten Indexes für die Customer-Tabelle:

  • 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 Order- und Customer-Tabelle sind Hashindizes und werden daher nicht sortiert. Ein Merge Join würde Sortieroperatoren erfordern, die die Leistung verringern würden.

Nativ kompilierte gespeicherte Prozeduren

Native kompilierte gespeicherte Prozeduren sind Transact-SQL-gespeicherte Prozeduren, die in Computercode kompiliert wurden, anstatt vom Abfrageausführungsmodul 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 Algebrization, 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. In der folgenden Tabelle wird zusammengefasst, wann kompilierte und interpretierte gespeicherte Prozeduren kompiliert bzw. erneut kompiliert werden:

Kompilierung und Abfrageverarbeitung

Das folgende Diagramm veranschaulicht den Kompilierungsprozess systemintern kompilierte gespeicherte Prozeduren:

Systeminterne Kompilierung gespeicherter Prozeduren.

Der Prozess lässt sich folgendermaßen beschreiben:

1. Der Benutzer gibt eine CREATE PROCEDURE-Anweisung auf SQL Server aus.

2. Der Parser und Algebrizer erstellen den Verarbeitungsfluss für die Prozedur sowie 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, beispielsweise mithilfe von sp_prep_exec, muss der Parser den Prozedurnamen und die Parameter nicht zur Ausführungszeit 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 bei der ersten Ausführung kompiliert, im Gegensatz zu nativ kompilierten gespeicherten Prozeduren, die zur Erstellungszeit kompiliert werden. 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 von Parametern während der Kompilierung wird als Parameterermittlung 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 option SHOWPLAN_XML in Transact-SQL abgerufen werden. 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. Der Aufbau der Struktur bestimmt die Interaktion (den Fluss von Zeilen von einem Operator zu anderen) zwischen Operatoren. In der grafischen Ansicht von SQL Server Management Studio ist der Fluss von rechts nach links. Beispielsweise enthält der Abfrageplan in Abbildung 1 zwei Index Scan-Operatoren, die Zeilen für einen Merge Join-Operator ausgeben. Der Merge Join-Operator stellt Zeilen für den Select-Operator bereit. 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 Joins

SQL Server verwaltet Statistiken zu Werten in Indexschlüsselspalten, um die Kosten bestimmter Vorgänge wie Indexscans und Indexsuchen zu schätzen. ( SQL Server erstellt auch Statistiken zu Nicht-Indexschlüsselspalten, wenn Sie sie explizit erstellen oder wenn der Abfrageoptimierer sie als Reaktion auf eine Abfrage mit einem Prädikat erstellt.) Die Hauptmetrik in der 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 Index Seek- und Index Scan-Operatoren im Plan und schließt danach die anderen Operatoren wie den Join-Operator ein. Die geschätzte Anzahl der von einem Joinoperator zu verarbeitenden Zeilen basiert auf der Schätzung für den zugrunde liegenden Index-, Seek- und Scan-Operator. Für 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 anzuzeigen.

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 nicht gruppierten Indexes für CustomerID ergibt 830 geschätzte und tatsächliche Zeilen.
• Der Merge Join-Operator ergibt 815 geschätzte und 830 tatsächliche Zeilen.

Die Schätzungen für die Indexscans sind genau. SQL Server verwaltet die Zeilenanzahl für datenträgerbasierte Tabellen. Schätzungen für vollständige Tabellen- und Indexscans sind immer genau. Die Schätzung für den Join ist auch ziemlich genau.

Wenn sich diese Schätzungen ändern, ändern sich auch die Kostenüberlegungen für verschiedene Planalternativen. Wenn beispielsweise eine der Seiten des Joins eine geschätzte Zeilenanzahl von 1 oder nur einige Zeilen hat, ist die Verwendung von Joins geschachtelter Schleifen kostengünstiger. 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:

Bei diesem Abfrageplan:

• Die Hashübereinstimmung wurde durch einen physischen Joinoperator für geschachtelte Schleifen 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.

Weitere Informationen

Speicheroptimierte Tabellen