In-Memory-Beispiel 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.

In diesem Artikel finden Sie zwei Beispiele, die die Verwendung von In-Memory-OLTP und Columnstore-Indizes in Azure SQL-Datenbank veranschaulichen.

Weitere Informationen finden Sie unter

Eine einfachere, aber visuell ansprechendere Demo der Leistung von In-Memory-OLTP finden Sie hier:

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

Sie können im Azure-Portal die Beispieldatenbank AdventureWorksLT mit wenigen Schritten erstellen. Anhand der Schritte in diesem Abschnitt wird erläutert, wie Sie anschließend Ihre AdventureWorksLT-Datenbank mit In-Memory-OLTP-Objekten erweitern können, und es werden Leistungsvorteile demonstriert.

Installationsschritte

  1. Erstellen Sie im Azure-Portal auf einem 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 nicht unterstützt wird. 1 bedeutet Unterstützung. In-Memory-Technologien 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), 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 Versionen der 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 bei hohen Belastungsgraden verwenden. Sie können vergleichen, wie lange es dauert, bis die beiden Belastungstests abgeschlossen sind.

Installieren von RML-Hilfsprogrammen und ostress

Im Idealfall planen Sie die Ausführung von „ostress.exe“ auf einer Azure-VM. Sie würden eine Azure-VM in derselben Azure-Region Ihrer AdventureWorksLT-Datenbank erstellen. Sie können aber stattdessen ostress.exe auf Ihrer lokalen Workstation ausführen, sofern Sie eine Verbindung mit Ihrer von Azure SQL-Datenbank herstellen können.

Installieren Sie auf der VM oder dem gewählten Host die RML-Hilfsprogramme (Replay Markup Language). Dazu zählt auch „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 Fenster mit einer RML-Eingabeaufforderung zum Ausführen der „ostress.exe“-Befehlszeile 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 tatsächlichen 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, um auf verantwortliche Weise eine u.U. große Menge an Testdaten zu löschen.

Erwartete Vergleichsergebnisse

Unsere In-Memory-Tests haben für diese einfache Workload eine neunmal höhere Leistung ergeben, wenn ostress 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 denselben Namen.
    • Wählen Sie die Dienstebene „Premium“ aus.

  2. Kopieren Sie sql_in-memory_analytics_sample in die Zwischenablage.

    • Das T-SQL-Skript erstellt die erforderlichen In-Memory-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.
    • Die Ausführung des Skripts kann 15 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 v/s Columnstore table performance differences
-- Enable actual Query Plan in order to see Plan differences when Executing
*/
-- Ensure Database is in 130 compatibility mode
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 Prior query on a table with a clustered columnstore index CCI
-- The comparison numbers are even more dramatic the larger the table is (this is an 11 million row table only)
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-Tarif können Sie etwa einen neunfachen Leistungszuwachs für diese Abfrage bei Verwenden des gruppierten Columnstore-Indexes im Vergleich zum traditionellen Index erwarten. Mit P15 kann bei Verwenden des Columnstore-Indexes ein Leistungszuwachs um das 57-fache erwartet werden.

Zugehöriger Inhalt