Übersicht über In-Memory OLTP und Verwendungsszenarien

  • Artikel

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

In-Memory OLTP ist die führende Technologie, die in SQL Server und SQL-Datenbank verfügbar ist, um die Leistung von Transaktionsverarbeitung, Datenerfassung, Datenladevorgang und vorübergehenden Datenszenarien zu optimieren. Dieser Artikel bietet eine Übersicht über die Technologie und beschreibt Verwendungsszenarios für In-Memory-OLTP. Nutzen Sie diese Informationen, um festzustellen, ob In-Memory-OLTP für Ihre Anwendung geeignet ist. Der Artikel endet mit einem Beispiel, das In-Memory-OLTP-Objekte zeigt. Er enthält zudem einen Verweis auf eine leistungsbezogene Demo und Verweise auf Ressourcen, die Sie für die nächsten Schritte nutzen können.

In diesem Artikel wird die In-Memory OLTP-Technologie sowohl in SQL Server als auch in SQL-Datenbank behandelt. Weitere Spezifische Informationen zu In-Memory-Daten in Azure SQL finden Sie unter Optimieren der Leistung mithilfe von In-Memory-Technologien in Azure SQL-Datenbank und Azure SQL Managed Instance und Blog: In-Memory OLTP in Azure SQL-Datenbank.

In-Memory-OLTP: Übersicht

In-Memory-OLTP kann bei den entsprechenden Workloads für große Leistungszuwächse sorgen. Während andere Kunden in einigen Fällen eine Leistungszunahme um das 30-fache verzeichnen konnten, hängt Ihr Zuwachs vornehmlich von der Workload ab.

Woher rührt nun diese Leistungszunahme? Im Wesentlichen verbessert In-Memory OLTP die Leistung der Transaktionsverarbeitung, indem der Datenzugriff und die Transaktionsausführung effizienter gestaltet und Sperr- und Latchkonflikte zwischen gleichzeitig ausgeführten Transaktionen entfernt werden. In-Memory OLTP ist nicht schnell, da es sich im Arbeitsspeicher befindet. es ist schnell, da es für die Daten optimiert ist, die sich im Arbeitsspeicher befinden. Datenspeicherung, Zugriff und Verarbeitungsalgorithmen wurden von Grund auf neu gestaltet, um in den Genuss der neuesten Weiterentwicklungen bei der Datenverarbeitung im Arbeitsspeicher und hoher Parallelität zu kommen.

Nur weil Sich Daten im Arbeitsspeicher befinden, bedeutet dies nicht, dass Sie sie verlieren, wenn ein Fehler auftritt. Alle Transaktionen sind standardmäßig vollständig dauerhaft, was heißt, dass Sie dieselben Zusagen hinsichtlich Dauerhaftigkeit wie für andere Tabellen in SQL Server erhalten. Als Teil des Transaktionscommits werden alle Änderungen in das Transaktionsprotokoll auf dem Datenträger geschrieben. Wenn es zu einem beliebigen Zeitpunkt nach dem Commit der Transaktion zu einem Fehler kommt, sind Ihre Daten vorhanden, nachdem die Datenbank wieder online geschaltet wurde. Darüber hinaus arbeitet In-Memory OLTP mit allen Hochverfügbarkeits- und Notfallwiederherstellungsfunktionen von SQL Server, z. B. Always On Verfügbarkeitsgruppen, Always On Failoverclusterinstanzen (SQL Server), Sicherung/Wiederherstellung usw.

Um In-Memory OLTP in Ihrer Datenbank zu verwenden, verwenden Sie mindestens einen der folgenden Objekttypen:

  • Speicheroptimierte Tabellen dienen zum Speichern von Benutzerdaten. Sie deklarieren eine Tabelle bei ihrer Erstellung als speicheroptimiert.
  • Nicht dauerhafte Tabellen dienen für kurzlebige Daten, entweder zum Zwischenspeichern oder für ein temporäres Resultset (als Ersatz für herkömmliche temporäre Tabellen). Eine nicht dauerhafte Tabelle ist eine speicheroptimierte Tabelle, die mit DURABILITY=SCHEMA_ONLY deklariert wird, was bedeutet, dass Änderungen an diesen Tabellen keine E/A-Vorgänge verursachen. Dadurch wird die Nutzung von E/A-Protokollressourcen in Fällen vermieden, in denen Dauerhaftigkeit nicht unbedingt erforderlich ist.
  • Speicheroptimierte Tabellentypen werden für Tabellenwertparameter (TVPs) sowie für temporäre Resultsets in gespeicherten Prozeduren verwendet. Diese können anstelle herkömmlicher Tabellentypen verwendet werden. Tabellenvariablen und TVPs, die mit einem speicheroptimierten Tabellentyp deklariert werden, übernehmen die Vorteile nicht dauerhafter speicheroptimierter Tabellen: effizienter Datenzugriff und keine E/A.
  • Nativ kompilierte T-SQL-Module dienen zum weiteren Verringern des Zeitaufwands einzelner Transaktionen durch Reduzieren der CPU-Zyklen, die für das Verarbeiten von Vorgängen erforderlich sind. Sie deklarieren bei seiner Erstellung, dass ein Transact-SQL-Modul nativ kompiliert werden soll. Derzeit können die folgenden T-SQL-Module nativ kompiliert werden: gespeicherte Prozeduren, Trigger und benutzerdefinierte Skalarfunktionen.

In-Memory OLTP ist in SQL Server und SQL-Datenbank integriert. Da sich diese Objekte ähnlich verhalten wie herkömmliche Entsprechungen, können Sie häufig Leistungsvorteile erzielen, während Sie nur minimale Änderungen an der Datenbank und der Anwendung vornehmen. Darüber hinaus können Sie über speicheroptimierte und herkömmliche datenträgerbasierte Tabellen in derselben Datenbank verfügen und Abfragen auf beide anwenden. Am Ende dieses Artikels finden Sie ein Transact-SQL-Skript mit einem Beispiel für jeden dieser Objekttypen.

Verwendungsszenarien für In-Memory OLTP

In-Memory OLTP ist keine magische Go-Fast-Schaltfläche und eignet sich nicht für alle Workloads. Speicheroptimierte Tabellen verringern Ihre CPU-Auslastung nicht, wenn bei den meisten Abfragen eine Aggregation über große Datenbereiche erfolgt. Columnstore-Indizes helfen bei diesem Szenario.

Im Folgenden finden Sie eine Liste der Szenarien und Anwendungsmuster, in denen Kunden mit In-Memory OLTP erfolgreich waren.

Transaktionsverarbeitung mit hohem Durchsatz und niedriger Latenz

Dies ist das wichtigste Szenario, für das wir In-Memory-OLTP entwickelt haben: die Unterstützung großer Mengen von Transaktionen mit konsistent niedriger Latenz für einzelne Transaktionen.

Gängige Workloadszenarien sind u.a. Wertpapierhandel, Sportwetten, Spiele auf Mobilgeräten und Übermittlung von Werbeanzeigen. Ein weiteres häufiges Muster ist ein „Katalog“, der häufig gelesen und/oder aktualisiert wird. Beispiel: Sie verfügen über große Dateien, die jeweils auf mehrere Clusterknoten verteilt sind, und katalogisieren den Speicherort jedes Shards jeder Datei in einer speicheroptimierten Tabelle.

Überlegungen zur Implementierung

Verwenden Sie speicheroptimierte Tabellen für Ihre wichtigsten Transaktionstabellen, d.h. die Tabelle mit den meisten leistungskritischen Transaktionen. Verwenden Sie nativ kompilierte gespeicherte Prozeduren zum Optimieren der Ausführung der Logik, die den Geschäftstransaktion zugeordnet ist. Je mehr Logik Sie an gespeicherte Prozeduren in der Datenbank weitergeben können, desto mehr können Sie von In-Memory-OLTP profitieren.

So fangen Sie bei einer vorhandenen Anwendung an

  1. Verwenden Sie den Bericht zur Transaktionsleistungsanalyse, um die Objekte zu bestimmen, die Sie migrieren möchten.
  2. Verwenden Sie die Ratgeber für Speicheroptimierung und native Kompilierung, die Sie bei der Migration unterstützen.

Datenerfassung, einschließlich IoT (Internet der Dinge)

In-Memory-OLTP ist für die gleichzeitige Erfassung großer Datenmengen aus vielen verschiedenen Quellen geeignet. Außerdem ist es häufig vorteilhaft, Daten im Vergleich zu anderen Zielen in einer SQL Server-Datenbank zu erfassen, da SQL Server die Ausführung von Abfragen für die Daten schnell macht und Ihnen Echtzeiteinblicke ermöglicht.

Folgende Anwendungsmuster werden häufig verwendet:

  • Erfassen von Sensormesswerten und -ereignissen sowie das Zulassen von Benachrichtigungen und Verlaufsanalysen.
  • Verwalten von Batchaktualisierungen, auch aus mehreren Quellen, bei gleichzeitiger Minimierung der Auswirkungen auf die gleichzeitige Leseworkload.

Überlegungen zur Implementierung

Verwenden Sie für die Datenerfassung eine speicheroptimierte Tabelle. Wenn die Erfassung hauptsächlich aus Einfügungen (anstelle von Aktualisierungen) besteht und der In-Memory-OLTP-Speicherbedarf von Belang ist, haben Sie diese Optionen:

  • Verwenden Sie einen Auftrag zum regelmäßigen Batchauslagern von Daten in eine datenträgerbasierte Tabelle mit einem gruppierten Columnstore-Index. INSERT INTO <disk-based table> SELECT FROM <memory-optimized table>Oder
  • Arbeiten Sie mit einer temporalen speicheroptimierten Tabelle zum Verwalten von Verlaufsdaten. In diesem Modus befinden sich die Verlaufsdaten auf dem Datenträger, und die Datenverschiebung wird vom System verwaltet.

Das SQL Server-Repository mit Beispielen enthält eine Smart Grid-Anwendung, die eine temporale speicheroptimierte Tabelle, einen speicheroptimierten Tabellentyp und eine nativ kompilierte gespeicherte Prozedur verwendet, um die Datenerfassung zu beschleunigen, während der In-Memory-OLTP-Speicherbedarf der Sensordaten verwaltet wird:

Zwischenspeicherung und Sitzungszustand

Die In-Memory OLTP-Technologie macht die Datenbank-Engine in SQL Server oder Azure SQL Datenbanken zu einer attraktiven Plattform für die Aufrechterhaltung des Sitzungszustands (z. B. für eine ASP.NET-Anwendung) und zum Zwischenspeichern.

ASP.NET Sitzungszustand ist ein erfolgreicher Anwendungsfall für In-Memory OLTP. Mithilfe von SQL Server konnte ein Kunde 1,2 Mio. Anforderungen pro Sekunde erreichen. Mittlerweile hat das Unternehmen begonnen, In-Memory-OLTP für die Cacheanforderungen aller Mid-Tier-Anwendungen zu nutzen. Details: Wie bwin SQL Server 2016 (13.x) In-Memory OLTP verwendet, um eine beispiellose Leistung und Skalierung zu erzielen

Überlegungen zur Implementierung

Sie können nicht dauerhafte speicheroptimierte Tabellen als einfachen Schlüssel-Wert-Speicher nutzen, indem Sie ein Blob in Spalten des Typs „varbinary(max)“ speichern. Alternativ können Sie einen teilweise strukturierten Cache mit JSON-Unterstützung in SQL Server und SQL-Datenbank implementieren. Schließlich können Sie einen vollständig relationalen Cache mithilfe nicht dauerhafter Tabellen mit einem vollständig relationalen Schema einschließlich verschiedener Datentypen und Einschränkungen erstellen.

Erste Schritte mit der Speicheroptimierung ASP.NET Sitzungszustands mithilfe der auf GitHub veröffentlichten Skripts, um die Objekte zu ersetzen, die vom integrierten SQL Server Sitzungszustandsanbieter erstellt wurden: aspnet-session-state

Kundenfallstudie

Tempdb-Objektersetzung

Verwenden Sie nicht dauerhafte Tabellen und speicheroptimierte Tabellentypen, um ihre herkömmlichen tempdb basierten Strukturen wie temporäre Tabellen, Tabellenvariablen und Tabellenwertparameter (Table-Valued Parameters, TVPs) zu ersetzen.

Speicheroptimierte Tabellenvariablen und nicht dauerhafte Tabellen sorgen im Vergleich mit herkömmlichen Tabellenvariablen und der Tabelle „#temp“ meist für eine Senkung der CPU-Last und die vollständige Beseitigung von Ein- und Ausgaben für Protokolle.

Überlegungen zur Implementierung

Informationen zu den ersten Schritten finden Sie unter: Improving temp table and table variable performance using memory optimization (Verbesserung der temporären Tabelle und Tabellenvariablenleistung mithilfe der Speicheroptimierung).

Kundenfallstudie

ETL (Extrahieren, Transformieren, Laden)

ETL-Workflows sehen häufig das Laden von Daten in eine Stagingtabelle, Transformationen der Daten und das Laden in die endgültigen Tabellen vor.

Verwenden Sie für das Staging von Daten nicht dauerhafte speicheroptimierte Tabellen. Bei diesen Tabellen fallen keine E/A-Vorgänge an, wodurch der Datenzugriff effizienter wird.

Überlegungen zur Implementierung

Wenn Sie im Rahmen des Workflows Transformationen auf die Stagingtabelle anwenden, können Sie zum Beschleunigen dieser Transformationen nativ kompilierte gespeicherte Prozeduren verwenden. Wenn diese Transformationen parallel erfolgen können, verschaffen Sie sich durch die Speicheroptimierung weitere Skalierungsvorteile.

Beispielskript

Bevor Sie In-Memory-OLTP verwenden können, müssen Sie die Dateigruppe MEMORY_OPTIMIZED_DATA erstellen. Außerdem empfehlen wir den Datenbank-Kompatibilitätsgrad 130 (oder höher) und das Festlegen der Datenbankoption MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT auf ON.

Sie können mithilfe des Skripts am folgenden Speicherort die Dateigruppe im standardmäßigen Datenordner erstellen und die empfohlenen Einstellungen konfigurieren:

Das folgende Beispielskript veranschaulicht In-Memory OLTP-Objekte, die Sie in Ihrer Datenbank erstellen können.

Zunächst konfigurieren Sie die Datenbank für In-Memory OLTP.

-- configure recommended DB option
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
GO

Sie können Tabellen mit unterschiedlicher Dauerhaftigkeit erstellen:

-- memory-optimized table
CREATE TABLE dbo.table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON);
GO
-- non-durable table
CREATE TABLE dbo.temp_table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON,
      DURABILITY=SCHEMA_ONLY);
GO

Sie können einen Tabellentyp als In-Memory-Tabelle erstellen.

-- memory-optimized table type
CREATE TYPE dbo.tt_table1 AS TABLE
( c1 INT IDENTITY,
  c2 NVARCHAR(MAX),
  is_transient BIT NOT NULL DEFAULT (0),
  INDEX ix_c1 HASH (c1) WITH (BUCKET_COUNT=1024))
WITH (MEMORY_OPTIMIZED=ON);
GO

Sie können eine nativ kompilierte gespeicherte Prozedur erstellen. Weitere Informationen finden Sie unter Aufrufen von nativ kompilierten gespeicherten Prozeduren über Datenzugriffsanwendungen.

-- natively compiled stored procedure
CREATE PROCEDURE dbo.usp_ingest_table1
  @table1 dbo.tt_table1 READONLY
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT,
          LANGUAGE=N'us_english')

  DECLARE @i INT = 1

  WHILE @i > 0
  BEGIN
    INSERT dbo.table1
    SELECT c2
    FROM @table1
    WHERE c1 = @i AND is_transient=0

    IF @@ROWCOUNT > 0
      SET @i += 1
    ELSE
    BEGIN
      INSERT dbo.temp_table1
      SELECT c2
      FROM @table1
      WHERE c1 = @i AND is_transient=1

      IF @@ROWCOUNT > 0
        SET @i += 1
      ELSE
        SET @i = 0
    END
  END

END
GO
-- sample execution of the proc
DECLARE @table1 dbo.tt_table1;
INSERT @table1 (c2, is_transient) VALUES (N'sample durable', 0);
INSERT @table1 (c2, is_transient) VALUES (N'sample non-durable', 1);
EXECUTE dbo.usp_ingest_table1 @table1=@table1;
SELECT c1, c2 from dbo.table1;
SELECT c1, c2 from dbo.temp_table1;
GO

Weiterführende Ressourcen

Weitere Informationen

Nächste Schritte