Schnellere temporäre Tabellen und Tabellenvariablen durch Speicheroptimierung

2025-05-31

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

Wenn Sie temporäre Tabellen, Tabellenvariablen oder Tabellenwertparameter verwenden, sollten Sie Konvertierungen dieser Parameter in Betracht ziehen, um speicheroptimierte Tabellen und Tabellenvariablen zu verwenden, um die Leistung zu verbessern. Die Codeänderungen sind normalerweise minimal.

Dieser Artikel beschreibt Folgendes:

Szenarios, die für die Konvertierung zu In-Memory sprechen.
Technische Schritte für die Implementierung der Konvertierungen zu In-Memory.
Erforderliche Komponenten vor der Konvertierung zu In-Memory.
Ein Codebeispiel, das die Leistungsvorteile der Speicheroptimierung hervorhebt.

Ein. Grundlagen von speicheroptimierten Tabellenvariablen

Eine speicheroptimierte Tabellenvariable ist höchst effizient bei der Verwendung des gleichen speicheroptimierten Algorithmus und der gleichen Datenstrukturen, die von speicheroptimierten Tabellen verwendet werden. Die Effizienz wird maximiert, wenn innerhalb eines nativ kompilierten Moduls auf die Tabellenvariable zugegriffen werden kann.

Eine speicheroptimierte Tabellenvariable:

Wird nur im Arbeitsspeicher gespeichert und verfügt über keine Komponente auf dem Datenträger.
Umfasst keine E/A-Aktivität.
Beinhaltet keine tempdb Auslastung oder Konflikte.
Kann an eine gespeicherte Prozedur als ein Tabellenwertparameter (TVP) übergeben werden.
Benötigt mindestens einen Index, entweder einen Hashindex oder einen nicht gruppierten Index.
- Für einen Hash-Index sollte die Bucketanzahl idealerweise auf einen Wert zwischen der einfachen und doppelten Anzahl von Indexschlüsselwerten festgelegt werden. Ein zu hoher Wert (ein bis zu zehnfacher Wert) stellt in der Regel aber auch kein Problem dar. Weitere Informationen zu finden Sie unter Indizes in speicheroptimierten Tabellen.

Objekttypen

In-Memory-OLTP stellt die folgenden Objekte bereit, die für speicheroptimierte temporäre Tabellen und Tabellenvariablen verwendet werden können.

Speicheroptimierte Tabellen
- Haltbarkeit = SCHEMA_ONLY
Speicheroptimierte Tabellenvariablen
- Muss in zwei Schritten (statt inline) deklariert werden:
  - CREATE TYPE my_type AS TABLE ...; , dann
  - DECLARE @mytablevariable my_type;.

B. Szenario: Ersetzen der globalen temporären Tabelle

Das Ersetzen einer globalen temporären Tabelle mit einer speicheroptimierten SCHEMA_ONLY-Tabelle ist relativ unkompliziert. Die größte Veränderung ist, dass man die Tabelle zur Bereitstellungszeit und nicht zur Laufzeit erstellt. Die Erstellung von speicheroptimierten Tabellen dauert länger als die Erstellung herkömmlicher Tabellen, aufgrund der Optimierungen zur Kompilierzeit. Das Erstellen und Löschen von speicheroptimierten Tabellen als Teil der Online-Workload wirkt sich auf die Leistung der Workload sowie auf die Leistung von Redo-Vorgängen in Always On Availability Groups auf Sekundärknoten und die Datenbankwiederherstellung aus.

Angenommen, Sie verfügen über die folgende globale temporäre Tabelle.

CREATE TABLE ##tempGlobalB
(
    Column1 INT NOT NULL,
    Column2 NVARCHAR (4000)
);

Ersetzen Sie die globale temporäre Tabelle mit der folgenden speicheroptimierten Tabelle, für die DURABILITY = SCHEMA_ONLY festgelegt wurde.

CREATE TABLE dbo.soGlobalB
(
    Column1 INT NOT NULL INDEX ix1 NONCLUSTERED,
    Column2 NVARCHAR (4000)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

Schritte

Die folgenden Schritte beschreiben die Konvertierung von globalen temporären Tabellen zu SCHEMA_ONLY.

Erstellen Sie die Tabelle einmal, genau wie jede herkömmliche On-Disk-Tabelle.Create the dbo.soGlobalB table, one time, just as you would any traditional on-disk table.
Entfernen Sie aus Transact-SQL (T-SQL) die ##tempGlobalB Anweisung zur Tabellenerstellung. Es ist wichtig, die speicheroptimierte Tabelle zur Bereitstellungszeit zu erstellen, nicht zur Laufzeit, um den Kompilierungsaufwand zu vermeiden, der im Lieferumfang der Tabellenerstellung enthalten ist.
Ersetzen Sie in Ihrem T-SQL alle Erwähnungen von ##tempGlobalB durch dbo.soGlobalB.

C. Szenario: Ersetzen der temporären Sitzungstabelle

Die erforderlichen Schritte zum Ersetzen einer Sitzung temporäre Tabelle umfassen zusätzliches T-SQL als für die globale temporäre Tabelle Szenario. Glücklicherweise bedeutet das zusätzliche T-SQL nicht mehr Aufwand, um die Konvertierung zu erreichen.

Wie bei dem Szenario mit der globalen temporären Tabelle, ist die größte Veränderung, die Tabelle zur Bereitstellungszeit und nicht zur Laufzeit zu erstellen, um den Kompilierungsaufwand zu vermeiden.

Angenommen, Sie verfügen über die folgende temporäre Sitzungstabelle.

CREATE TABLE #tempSessionC
(
    Column1 INT NOT NULL,
    Column2 NVARCHAR (4000)
);

Erstellen Sie zunächst die folgende Tabellenwertfunktion, um nach @@spid zu filtern. Die Funktion kann von allen SCHEMA_ONLY Tabellen verwendet werden, die Sie aus temporären Sitzungstabellen konvertieren.

CREATE FUNCTION dbo.fn_SpidFilter
(@SpidFilter SMALLINT)
RETURNS TABLE
WITH SCHEMABINDING, NATIVE_COMPILATION
AS
RETURN
    SELECT 1 AS fn_SpidFilter
    WHERE @SpidFilter = @@spid

Zweitens: Erstellen der SCHEMA_ONLY-Tabelle sowie eine Sicherheitsrichtlinie für die Tabelle ein.

Jede speicheroptimierte Tabelle muss mindestens einen Index aufweisen.

Für die Tabelle dbo.soSessionC ist ein HASH-Index möglicherweise die bessere Wahl, wenn wir den geeigneten BUCKET_COUNT-Wert berechnen. Verwenden wir der Einfachheit halber einen NONCLUSTERED-Index.

CREATE TABLE dbo.soSessionC
(
    Column1 INT NOT NULL,
    Column2 NVARCHAR (4000) NULL,
    SpidFilter SMALLINT DEFAULT (@@spid) NOT NULL,
    CONSTRAINT CHK_soSessionC_SpidFilter CHECK (SpidFilter = @@spid),
    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter)
    -- INDEX ix_SpidFilter HASH
    --    (SpidFilter) WITH (BUCKET_COUNT = 64),
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO

CREATE SECURITY POLICY dbo.soSessionC_SpidFilter_Policy
    ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter) ON dbo.soSessionC
    WITH (STATE = ON);
GO

Drittens: Im allgemeinen T-SQL-Code:

Ändern Sie alle Verweise auf die temporäre Tabelle in den Transact-SQL-Anweisungen in die neue speicheroptimierte Tabelle:
- Alt: #tempSessionC
- Neu: dbo.soSessionC
Ersetzen Sie die CREATE TABLE #tempSessionC Anweisungen in Ihrem Code durch DELETE FROM dbo.soSessionC, um sicherzustellen, dass eine Sitzung nicht für Tabelleninhalte verfügbar gemacht wird, die von einer vorherigen Sitzung mit demselben session_id eingefügt wurden. Es ist wichtig, die speicheroptimierte Tabelle zur Bereitstellungszeit zu erstellen, nicht zur Laufzeit, um den Kompilierungsaufwand zu vermeiden, der im Lieferumfang der Tabellenerstellung enthalten ist.
Entfernen Sie die DROP TABLE #tempSessionC Anweisungen aus Ihrem Code. Optional können Sie eine DELETE FROM dbo.soSessionC Anweisung einfügen, falls die Arbeitsspeichergröße ein potenzielles Problem darstellt.

D: Szenario: Eine Tabellenvariable kann die WITH-Klausel MEMORY_OPTIMIZED=ON enthalten.

Eine herkömmliche Tabellenvariable stellt eine Tabelle in der tempdb Datenbank dar. Um eine wesentlich schnellere Leistung zu erzielen, können Sie die Tabellenvariable arbeitsspeicheroptimieren.

Dies ist der T-SQL für eine herkömmliche Tabellenvariable. Der Bereich endet, wenn der Batch oder die Sitzung beendet wird.

DECLARE @tvTableD TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR (10));

Von Inline zu explizit konvertieren

Die vorherigen Syntax soll die Tabellenvariable inlineerstellen. Die Inlinesyntax unterstützt keine Speicheroptimierung. Lassen Sie uns also die Inline-Syntax zur Explicit-Syntax für TYPE konvertieren.

Umfang: Die vom ersten, go-getrennten Batch erstellte Typdefinition bleibt auch nach dem Herunterfahren und Neustart des Servers erhalten. Nach dem ersten „go“-unterteilten Batch bleibt die deklarierte Tabelle @tvTableC nur bis zum nächsten „go“-unterteilten Batch bestehen und der Batch endet.

CREATE TYPE dbo.typeTableD AS TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR (10));
GO

SET NOCOUNT ON;

DECLARE @tvTableD AS dbo.typeTableD;
INSERT INTO @tvTableD (Column1) VALUES (1), (2);
SELECT * FROM @tvTableD;
GO

D.2 Konvertieren einer explizit auf dem Datenträger gespeicherten Tabellenvariable in eine speicheroptimierte Tabellenvariable

Eine speicheroptimierte Tabellenvariable befindet sich nicht in tempdb. Die Speicheroptimierung führt zu einer Zunahme der Geschwindigkeit, die zehnmal schneller ist oder sogar noch schneller.

Die Konvertierung in eine speicheroptimierte Tabelle erfolgt in nur einem Schritt. Optimieren Sie die explizite TYPE-Erstellung wie folgt, sodass Folgendes hinzugefügt wird:

Ein Index. Jede speicheroptimierte Tabelle muss mindestens einen Index haben.
MEMORY_OPTIMIZED = EIN.

CREATE TYPE dbo.typeTableD AS TABLE (
    Column1 INT NOT NULL INDEX ix1,
    Column2 CHAR (10))
    WITH (MEMORY_OPTIMIZED = ON);

Fertig.

E. FILEGROUP als Voraussetzung für SQL Server

Für die Verwendung speicheroptimierter Features in Microsoft SQL Server muss Ihre Datenbank über eine FILEGROUP verfügen, die mit MEMORY_OPTIMIZED_DATAdeklariert wird.

Für die Azure SQL-Datenbank ist das Erstellen dieser FILEGROUP nicht erforderlich.

Voraussetzung: Der folgende Transact-SQL-Code für FILEGROUP ist eine Voraussetzung für die langen T-SQL-Codebeispiele in späteren Abschnitten dieses Artikels.

Verwenden Sie SSMS.exe oder ein anderes Tool, das T-SQL übermitteln kann.
Fügen Sie den T-SQL-Beispielcode FILEGROUP in SSMS ein.
Bearbeiten Sie das T-SQL, um die bestimmten Namen und Verzeichnispfade wie gewünscht zu ändern.

Alle Verzeichnisse im Wert FILENAME müssen bereits vorhanden sein, außer die unterste Ebene im Verzeichnis.

Führen Sie das bearbeitete T-SQL aus.

Es ist nicht erforderlich, die FILEGROUP T-SQL mehr als einmal auszuführen, auch wenn Sie den Geschwindigkeitsvergleich T-SQL im nächsten Unterabschnitt wiederholt anpassen und erneut ausführen.

ALTER DATABASE InMemTest2
    ADD FILEGROUP FgMemOptim3 CONTAINS MEMORY_OPTIMIZED_DATA;
GO

ALTER DATABASE InMemTest2
    ADD FILE (NAME = N'FileMemOptim3a', FILENAME = N'C:\DATA\FileMemOptim3a'
    -- C:\DATA\ preexisted.
) TO FILEGROUP FgMemOptim3;
GO

Das folgende Skript erstellt eine Dateigruppe für Sie und konfiguriert empfohlene Datenbankeinstellungen: enable-in-memory-oltp.sql

Weitere Informationen zu ALTER DATABASE ... ADD FILE und FILEGROUP finden Sie unter:

F. Schnelltest zum Beweisen der Verbesserung der Geschwindigkeit

Dieser Abschnitt enthält Transact-SQL-Code, der ausgeführt werden kann, um zu vergleichen, welcher Geschwindigkeitszuwachs bei INSERT-DELETE durch die Verwendung speicheroptimierter Tabellen erreicht werden kann. Der Code besteht aus zwei Hälften, die nahezu identisch sind. In der ersten Hälfte ist der Tabellentyp jedoch speicheroptimiert.

Der Vergleichstest dauert ca. 7 Sekunden. So führen Sie das Beispiel aus:

Voraussetzung: Sie müssen bereits FILEGROUP T-SQL aus dem vorherigen Abschnitt ausgeführt haben.
Führen Sie das folgende T-SQL-INSERT-DELETE-Skript aus.

Beachten Sie die GO 5001 Anweisung, die die T-SQL 5.001-Mal erneut sendet. Sie können die Anzahl anpassen und den Vorgang erneut ausführen.

Wenn Sie das Skript in einer Azure SQL-Datenbank ausführen, stellen Sie sicher, dass Sie es auf einem virtuellen Computer in der gleichen Region ausführen.

PRINT ' ';
PRINT '---- Next, memory-optimized, faster. ----';
DROP TYPE IF EXISTS dbo.typeTableC_mem;
GO

CREATE TYPE dbo.typeTableC_mem -- !!  Memory-optimized.
AS TABLE (
    Column1 INT NOT NULL INDEX ix1,
    Column2 CHAR(10)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

DECLARE @dateString_Begin NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_Begin,
    ' = Begin time, _mem.'
);
GO

SET NOCOUNT ON;

DECLARE @tvTableC dbo.typeTableC_mem;-- !!

INSERT INTO @tvTableC (Column1) VALUES (1), (2);
INSERT INTO @tvTableC (Column1) VALUES (3), (4);

DELETE @tvTableC;GO 5001

DECLARE @dateString_End NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_End,
    ' = End time, _mem.'
);
GO

DROP TYPE IF EXISTS dbo.typeTableC_mem;
GO

---- End memory-optimized.
-------------------------------------------------
---- Start traditional on-disk.
PRINT ' ';
PRINT '---- Next, tempdb based, slower. ----';

DROP TYPE IF EXISTS dbo.typeTableC_tempdb;
GO

CREATE TYPE dbo.typeTableC_tempdb -- !!  Traditional tempdb.
AS TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR(10)
);
GO

DECLARE @dateString_Begin NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_Begin,
    ' = Begin time, _tempdb.'
);
GO

SET NOCOUNT ON;

DECLARE @tvTableC dbo.typeTableC_tempdb;-- !!

INSERT INTO @tvTableC (Column1) VALUES (1), (2);
INSERT INTO @tvTableC (Column1) VALUES (3), (4);

DELETE @tvTableC;
GO 5001

DECLARE @dateString_End NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_End,
    ' = End time, _tempdb.'
);
GO

DROP TYPE IF EXISTS dbo.typeTableC_tempdb;
GO

PRINT '---- Tests done. ----';
GO

Hier sehen Sie das Ergebnis.

---- Next, memory-optimized, faster. ----
2016-04-20 00:26:58.033  = Begin time, _mem.
Beginning execution loop
Batch execution completed 5001 times.
2016-04-20 00:26:58.733  = End time, _mem.

---- Next, tempdb based, slower. ----
2016-04-20 00:26:58.750  = Begin time, _tempdb.
Beginning execution loop
Batch execution completed 5001 times.
2016-04-20 00:27:05.440  = End time, _tempdb.
---- Tests done. ----

G. Vorhersagen von aktiver Arbeitsspeichernutzung

Sie können lernen, wie sie die Bedürfnisse des aktiven Arbeitsspeichers für Ihre speicheroptimierten Tabellen mit den folgenden Ressourcen vorhersagen können:

Nicht gruppierte Indizes nutzen für größere Tabellenvariablen mehr Arbeitsspeicher als für speicheroptimierte Tabellen. Je größer die Anzahl der Zeilen und der Indexschlüssel, desto größer wird der Unterschied.

Wenn auf die speicheroptimierten Tabellenvariablen nur mit einem exakten Schlüsselwert pro Zugriff zugegriffen wird, sollten Sie eher ein Hashindex als einen nicht gruppierten Index verwenden. Wenn Sie jedoch die entsprechende BUCKET_COUNT nicht schätzen können, ist ein NONCLUSTERED-Index eine gute zweite Wahl.

Freigeben über