Teilen über

Übersicht über In-Memory OLTP und Verwendungsszenarien

  • Artikel

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

In-Memory-OLTP ist in SQL Server und SQL-Datenbank die wichtigste verfügbare Technologie für die Optimierung der Transaktionsverarbeitung, das Erfassen und Laden von Daten sowie für vorübergehende Datenszenarien. 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-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 dank mehr Effizienz bei Datenzugriff und Transaktionsausführung sowie durch Beseitigen von Sperr- und Latchkonflikten zwischen gleichzeitig ausgeführten Transaktionen. In-Memory-OLTP ist nicht schnell, weil es sich im Arbeitsspeicher befindet, sondern wegen der Optimierung der In-Memory-Daten. 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 die Daten im Arbeitsspeicher befinden, heißt dies nicht, dass Sie sie bei einem Ausfall verlieren. 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 funktioniert In-Memory-OLTP mit allen Funktionen von SQL Server für Hochverfügbarkeit und Notfallwiederherstellung, wie z. B. Verfügbarkeitsgruppen, Failoverclusterinstanzen, Sicherung/Wiederherstellung usw.

Verwenden Sie zum Nutzen von In-Memory-OLTP in Ihrer Datenbank eine oder mehrere der folgenden Typen von Objekten:

  • 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 ist, was bedeutet, dass bei Änderungen an diesen Tabellen keine Ein-/Ausgaben (E/A) erfolgen. 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) und temporäre Resultsets in gespeicherten Prozeduren verwendet. Speicheroptimierte Tabellentypen 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 wie ihre herkömmlichen Gegenstücke verhalten, können Sie die Leistung oft erhöhen, obwohl nur minimale Änderungen an der Datenbank und Anwendung vorgenommen werden. Darüber hinaus können Sie über speicheroptimierte und herkömmliche datenträgerbasierte Tabellen in derselben Datenbank verfügen und Abfragen auf beide anwenden. Weitere Informationen zu jedem dieser Objekttypen finden Sie unter Transact-SQL-Beispielskript weiter unten in diesem Artikel.

Verwendungsszenarien für In-Memory-OLTP

In-Memory-OLTP ist kein magischer Schnellschalter und auch nicht für alle Workloads geeignet. 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.

Achtung

Bekanntes Problem: Bei Datenbanken mit speicheroptimierten Tabellen kann die Durchführung einer Transaktionsprotokollsicherung ohne Wiederherstellung und später das Ausführen einer Wiederherstellung eines Transaktionsprotokolls mit Wiederherstellung zu einem nicht reagierenden Datenbankwiederherstellungsprozess führen. Dieses Problem kann sich auch auf die Protokollversandfunktionalität auswirken. Um dieses Problem zu umgehen, kann die SQL-Server-Instanz vor dem Initiieren des Wiederherstellungsvorgangs neu gestartet werden.

Es folgt eine Liste von Szenarien und Anwendungsmustern, bei 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 den Ratgeber für Speicheroptimierung und den Ratgeber für 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. Im Vergleich mit anderen Zielen ist es oft vorteilhaft, Daten in einer SQL Server-Datenbank zu erfassen, da SQL Server das Anwenden von Abfragen auf die Daten stark beschleunigt und Ihnen dadurch Einblicke in Echtzeit 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 Auslagern von Daten per Batchvorgang in eine datenträgerbasierte Tabelle mit einem gruppierten Columnstore-Index anhand eines Auftrags, der INSERT INTO <disk-based table> SELECT FROM <memory-optimized table> ausführt, 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 zum Aufrechterhalten des Sitzungszustands (z B. für eine ASP.NET-Anwendung) und zum Zwischenspeichern.

Der 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 In-Memory-OLTP in SQL Server 2016 (13.x) zum Erreichen einer beispiellosen Leistung und Skalierung nutzt

Ü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.

Machen Sie die ersten Schritte mit einem speicheroptimierten ASP.NET-Sitzungszustand, indem Sie die auf GitHub veröffentlichten Skripts nutzen, um die vom integrierten SQL Server-Sitzungszustandsanbieter erstellten Objekte zu ersetzen: aspnet-session-state

Kundenfallstudie

Ersetzen des Objekts tempdb

Nutzen Sie nicht dauerhafte Tabellen und speicheroptimierte Tabellentypen, um Ihre herkömmlichen auf tempdb basierenden Strukturen wie temporäre Tabellen, Tabellenvariablen und Tabellenwertparameter (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

Zum Einstieg: Verbessern 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 der Datenbank erstellen können.

Konfigurieren Sie zunächst 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

Zugehöriger Inhalt