Teilen über

Beispiel für In-Memory-OLTP in Azure SQL-Datenbank

  • Artikel

Gilt für: Azure SQL-Datenbank

In-Memory-Technologien in Azure SQL-Datenbank können zur Verbesserung der Leistung Ihrer Anwendung beitragen und potenziell Kosten für Ihre Datenbank verringern. Durch Verwenden von In-Memory-Technologien in Azure SQL-Datenbank können Sie Leistungsverbesserungen mit verschiedenen Workloads erzielen.

Zwei Beispiele in diesem Artikel veranschaulichen die Verwendung von In-Memory-OLTP sowie von Columnstore-Indizes in Azure SQL-Datenbank.

Weitere Informationen finden Sie unter:

Eine Einführungsdemo von In-Memory OLTP finden Sie unter:

1. Installieren des Beispiels für In-Memory OLTP

Sie können im Azure-Portal die Beispieldatenbank AdventureWorksLT mit wenigen Schritten erstellen. Führen Sie dann die Schritte in diesem Abschnitt aus, um In-Memory OLTP-Objekte zu Ihrer AdventureWorksLT-Datenbank hinzuzufügen und die Leistungsvorteile zu demonstrieren.

Installationsschritte

  1. Erstellen Sie im Azure-Portal auf einem logischen Server eine Datenbank vom Typ „Premium“ (DTU) oder „Unternehmenskritisch“ (vCore). Legen Sie die Beispieldatenbank AdventureWorksLT als Quelle fest. Ausführliche Anweisungen finden Sie unter Erstellen Ihrer ersten Datenbank in Azure SQL-Datenbank.

  2. Herstellen einer Verbindung mit der Datenbank mit SQL Server Management Studio (SSMS).

  3. Kopieren Sie das In-Memory OLTP Transact-SQL-Skript in die Zwischenablage. Das T-SQL-Skript erstellt die erforderlichen In-Memory-Objekte in der in Schritt 1 erstellten Beispieldatenbank AdventureWorksLT.

  4. Fügen Sie das T-SQL-Skript in SSMS ein, und führen Sie es aus. Die MEMORY_OPTIMIZED = ON-Klausel in den CREATE TABLE-Anweisungen ist entscheidend. Zum Beispiel:

    CREATE TABLE [SalesLT].[SalesOrderHeader_inmem](
    [SalesOrderID] int IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED ...,
    ...
) WITH (MEMORY_OPTIMIZED = ON);

Fehler 40536

Wenn bei Ausführen des T-SQL-Skripts der Fehler 40536 angezeigt wird, führen Sie das folgende T-SQL-Skript aus, um zu prüfen, ob die Datenbank In-Memory-Objekte unterstützt:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsXTPSupported');

Das Ergebnis 0 bedeutet, dass In-Memory-OLTP nicht unterstützt wird. 1 bedeutet Unterstützung. In-Memory-OLTP sind in den Tarifen Azure SQL Database Premium (DTU) und Unternehmenskritisch (vCore) verfügbar.

Informationen zu speicheroptimierten Elementen

Tabellen: Das Beispiel enthält die folgenden speicheroptimierten Tabellen:

  • SalesLT.Product_inmem
  • SalesLT.SalesOrderHeader_inmem
  • SalesLT.SalesOrderDetail_inmem
  • Demo.DemoSalesOrderHeaderSeed
  • Demo.DemoSalesOrderDetailSeed

Sie können filtern, um nur speicheroptimierte Tabellen im Object-Explorer in SSMS anzuzeigen. Wenn Sie mit der rechten Maustaste auf Tabellen klicken, navigieren Sie zu >Filter>Filtereinstellungen>Ist speicheroptimiert. Der Wert ist gleich 1.

Oder Sie können die Katalogsichten abfragen wie z. B.:

SELECT is_memory_optimized, name, type_desc, durability_desc
    FROM sys.tables
    WHERE is_memory_optimized = 1;

Nativ kompilierte gespeicherte Prozedur: Sie können SalesLT.usp_InsertSalesOrder_inmem über eine Katalogsichtabfrage untersuchen:

SELECT uses_native_compilation, OBJECT_NAME(object_id) AS module_name, definition
    FROM sys.sql_modules
    WHERE uses_native_compilation = 1;

2. Ausführen der OLTP-Beispielworkload

Der einzige Unterschied zwischen den beiden folgenden gespeicherten Prozeduren ist, dass die erste Prozedur speicheroptimierte Tabellen verwendet, während die zweite Prozedur die herkömmlichen Tabellen auf dem Datenträger nutzt:

  • SalesLT.usp_InsertSalesOrder_inmem
  • SalesLT.usp_InsertSalesOrder_ondisk

In diesem Abschnitt wird veranschaulicht, wie Sie das praktische Hilfsprogramm ostress.exe zum Ausführen der beiden gespeicherten Prozeduren verwenden. Sie können vergleichen, wie lange es dauert, bis die beiden Belastungstests abgeschlossen sind.

Installieren von RML-Hilfsprogrammen und ostress

Vorzugsweise sollten Sie ostress.exe auf einem virtuellen Azure-Computer (VM) ausführen. Sie würden eine Azure-VM in derselben Azure-Region erstellen, in der sich Ihre AdventureWorksLT-Datenbank befindet. Sie können stattdessen auch auf Ihrem lokalen Computer ausgeführt ostress.exe werden, wenn Sie eine Verbindung mit Ihrer Azure SQL-Datenbank herstellen können. Die Netzwerklatenz zwischen Ihrem Computer und der Datenbank in Azure kann jedoch die Leistungsvorteile von In-Memory-OLTP verringern.

Installieren Sie auf der VM oder dem gewählten Host die RML-Hilfsprogramme (Replay Markup Language). Die Dienstprogramme enthalten ostress.exe.

Weitere Informationen finden Sie unter:

Skript für „ostress.exe“

Dieser Abschnitt zeigt das T-SQL-Skript, das in unsere ostress.exe-Befehlszeile eingebettet ist. Das Skript verwendet Elemente, die von dem zuvor von Ihnen installierten T-SQL-Skript erstellt wurden.

Wenn Sie ostress.exe ausführen, empfehlen wir, dass Sie Parameterwerte übergeben, die den Workload mit den beiden folgenden Strategien belasten:

  • Führen Sie eine große Anzahl gleichzeitiger Verbindungen mit -n100 aus.
  • Lassen Sie jede Verbindung Hunderte von Malen wiederholen, indem Sie -r500 angeben.

Allerdings möchten Sie möglicherweise mit wesentlich kleineren Werten wie -n10 und -r50 starten, um sicherzustellen, dass alles funktioniert.

Das folgende Skript fügt einen Beispielauftrag mit fünf Positionen in die folgenden speicheroptimierten Tabellenein:

  • SalesLT.SalesOrderHeader_inmem
  • SalesLT.SalesOrderDetail_inmem
DECLARE
    @i int = 0,
    @od SalesLT.SalesOrderDetailType_inmem,
    @SalesOrderID int,
    @DueDate datetime2 = sysdatetime(),
    @CustomerID int = rand() * 8000,
    @BillToAddressID int = rand() * 10000,
    @ShipToAddressID int = rand() * 10000;

INSERT INTO @od
    SELECT OrderQty, ProductID
    FROM Demo.DemoSalesOrderDetailSeed
    WHERE OrderID= cast((rand()*60) as int);

WHILE (@i < 20)
BEGIN;
    EXECUTE SalesLT.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT,
        @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @od;
    SET @i = @i + 1;
END

Um die _ondisk-Version des vorherigen T-SQL-Skripts für ostress.exe zu erstellen, müssen Sie beide Vorkommen der Teilzeichenfolge _inmem durch _ondisk ersetzen. Diese Austauschvorgänge wirken sich auf die Namen von Tabellen und gespeicherten Prozeduren aus.

Zuerst Belastungsworkload _inmem ausführen

Sie können ein RML Cmd-Eingabeaufforderungsfenster zum Ausführen von ostress.exe verwenden. Die Befehlszeilenparameter weisen „ostress“ zu Folgendem an:

  • Ausführen von 100 Verbindungen gleichzeitig (-n100).
  • 50-maliges Ausführen des T-SQL-Skripts auf jeder Verbindung (-r50).
ostress.exe -n100 -r50 -S<servername>.database.windows.net -U<login> -P<password> -d<database> -q -Q"DECLARE @i int = 0, @od SalesLT.SalesOrderDetailType_inmem, @SalesOrderID int, @DueDate datetime2 = sysdatetime(), @CustomerID int = rand() * 8000, @BillToAddressID int = rand() * 10000, @ShipToAddressID int = rand()* 10000; INSERT INTO @od SELECT OrderQty, ProductID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID= cast((rand()*60) as int); WHILE (@i < 20) begin; EXECUTE SalesLT.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @od; set @i += 1; end"

So führen Sie die vorherige ostress.exe-Befehlszeile aus:

  1. Setzen Sie den Dateninhalt der Datenbank durch Ausführen des folgenden Befehls in SSMS zurück, um alle Daten zu löschen, die bei vorherigen Ausführungen eingefügt wurden:

    EXECUTE Demo.usp_DemoReset;

  2. Kopieren Sie den Text der vorhergehenden ostress.exe-Befehlszeile in die Zwischenablage.

  3. Ersetzen Sie <placeholders> für die Parameter -S -U -P -d durch die ordnungsgemäßen Werte.

  4. Führen Sie die bearbeitete Befehlszeile in einem RML-Befehlsfenster aus.

Das Ergebnis ist eine Dauer

Wenn ostress.exe abgeschlossen ist, wird die Testlaufdauer im RML-Befehlsfenster als letzte Zeile ausgegeben. Ein kürzerer Testlauf dauerte beispielsweise ca. 1,5 Minuten:

11/12/15 00:35:00.873 [0x000030A8] OSTRESS exiting normally, elapsed time: 00:01:31.867

Zurücksetzen, für _ondisk bearbeiten, danach erneut ausführen

Sobald Sie über das Ergebnis der Ausführung von _inmem verfügen, führen Sie die folgenden Schritte für die Ausführung von _ondisk durch:

  1. Setzen Sie die Datenbank durch Ausführen des folgenden Befehls in SSMS zurück, um alle Daten zu löschen, die bei der vorherigen Ausführung eingefügt wurden:

    EXECUTE Demo.usp_DemoReset;

  2. Bearbeiten Sie die ostress.exe-Befehlszeile so, dass alle Vorkommen von _inmem durch _ondisk ersetzt werden.

  3. Führen Sie ostress.exe ein zweites Mal aus, und erfassen Sie die Dauer.

  4. Setzen Sie die Datenbank erneut zurück.

Erwartete Vergleichsergebnisse

Unsere In-Memory-OLTP-Tests haben für diese einfache Workload eine neunmal höhere Leistung ergeben, wenn ostress.exe auf einem virtuellen Azure-Computer ausgeführt wird, der sich in der gleichen Azure-Region wie die Datenbank befindet.

3. Installieren des In-Memory Analytics-Beispiels

In diesem Abschnitt vergleichen Sie die E/A- und Statistikergebnisse mit dem Verwenden eines Columnstore-Indexes im Vergleich mit einem herkömmlichen B-Struktur-Index.

Für Echtzeitanalysen einer OLTP-Workload empfiehlt es sich häufig, einen nicht gruppierten Columnstore-Index zu verwenden. Weitere Informationen finden Sie unter Beschreibung von Columnstore-Indizes.

Vorbereiten des Columnstore-Analysetests

  1. Erstellen Sie im Azure-Portal anhand des Beispiels eine neue AdventureWorksLT-Datenbank. Verwenden Sie ein beliebiges Dienstziel, das Spaltenspeicherindizes unterstützt.

  2. Kopieren Sie sql_in-memory_analytics_sample in die Zwischenablage.

    • Das T-SQL-Skript erstellt die erforderlichen Objekte in der in Schritt 1 erstellten Beispieldatenbank AdventureWorksLT.
    • Das Skript erstellt die Dimensionstabelle und zwei Faktentabellen. Beide Faktentabellen werden mit 3,5 Mio. Zeilen aufgefüllt.
    • Bei kleineren Dienstzielen kann das Skript 15 Minuten oder länger dauern.

  3. Fügen Sie das T-SQL-Skript in SSMS ein, und führen Sie es aus. Unerlässlich ist das Schlüsselwort COLUMNSTORE in einer CREATE INDEX-Anweisung wie in: CREATE NONCLUSTERED COLUMNSTORE INDEX ...;

  4. Setzen Sie AdventureWorksLT auf die neueste Kompatibilitätsstufe, SQL Server 2022 (160): ALTER DATABASE AdventureworksLT SET compatibility_level = 160;

Wichtige Tabellen und Columnstore-Indizes

  • dbo.FactResellerSalesXL_CCI ist eine Tabelle mit einem gruppierten Columnstore-Index, der auf Datenebene eine erweiterte Komprimierung aufweist.

  • dbo.FactResellerSalesXL_PageCompressed ist eine Tabelle mit einem vergleichbaren herkömmlichen gruppierten Index, der nur auf Seitenebene komprimiert ist.

4. Wichtige Abfragen zum Vergleichen des Columnstore-Indexes

Hier finden Sie mehrere T-SQL-Abfragetypen, die Sie ausführen können, um Leistungssteigerungen zu erkennen. Achten Sie in Schritt 2 im T-SQL-Skript auf dieses Paar von Abfragen. Die beiden Abfragen unterscheiden sich nur in einer Zeile:

  • FROM FactResellerSalesXL_PageCompressed AS a
  • FROM FactResellerSalesXL_CCI AS a

In der Tabelle FactResellerSalesXL_CCI befindet sich ein gruppierter Columnstore-Index.

Das folgende T-SQL-Skript gibt die logischen E/A-Aktivitäts- und Zeitstatistiken aus, indem es SET STATISTICS IO und SET STATISTICS TIME für jede Abfrage verwendet.

/*********************************************************************
Step 2 -- Overview
-- Page compressed BTree table vs Columnstore table performance differences
-- Enable actual query plan in order to see Plan differences when executing.
*/
-- Ensure the database uses the latest compatibility level
ALTER DATABASE AdventureworksLT SET compatibility_level = 160
GO

-- Execute a typical query that joins the fact table with dimension tables.
-- Note this query will run on the page compressed table. Note down the time.
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

SELECT c.Year
    ,e.ProductCategoryKey
    ,FirstName + ' ' + LastName AS FullName
    ,COUNT(SalesOrderNumber) AS NumSales
    ,SUM(SalesAmount) AS TotalSalesAmt
    ,AVG(SalesAmount) AS AvgSalesAmt
    ,COUNT(DISTINCT SalesOrderNumber) AS NumOrders
    ,COUNT(DISTINCT a.CustomerKey) AS CountCustomers
FROM FactResellerSalesXL_PageCompressed AS a
INNER JOIN DimProduct AS b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer AS d ON d.CustomerKey = a.CustomerKey
INNER JOIN DimProductSubCategory AS e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate AS c ON c.DateKey = a.OrderDateKey
GROUP BY e.ProductCategoryKey,c.Year,d.CustomerKey,d.FirstName,d.LastName
GO

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO


-- This is the same query on a table with a clustered columnstore index (CCI).
-- The comparison numbers are the more pronounced the larger the table is (this is an 11 million row table).
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT c.Year
    ,e.ProductCategoryKey
    ,FirstName + ' ' + LastName AS FullName
    ,COUNT(SalesOrderNumber) AS NumSales
    ,SUM(SalesAmount) AS TotalSalesAmt
    ,AVG(SalesAmount) AS AvgSalesAmt
    ,COUNT(DISTINCT SalesOrderNumber) AS NumOrders
    ,COUNT(DISTINCT a.CustomerKey) AS CountCustomers
FROM FactResellerSalesXL_CCI AS a
INNER JOIN DimProduct AS b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer AS d ON d.CustomerKey = a.CustomerKey
INNER JOIN DimProductSubCategory AS e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate AS c ON c.DateKey = a.OrderDateKey
GROUP BY e.ProductCategoryKey,c.Year,d.CustomerKey,d.FirstName,d.LastName
GO

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

In einer Datenbank mit dem P2-Dienstziel können Sie etwa einen neunfachen Leistungszuwachs für diese Abfrage bei Verwenden des gruppierten Columnstore-Indexes im Vergleich zum traditionellen Rowstore-Index erwarten. Mit dem P15-Dienstziel kann bei Verwenden des Columnstore-Indexes ein Leistungszuwachs um das 57-fache erwartet werden.

Zugehöriger Inhalt