In-Memory-Beispiel in Azure SQL Managed Instance

  • Artikel

Gilt für:Azure SQL Managed Instance

In-Memory-Technologien in Azure SQL Managed Instance 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 Managed Instance 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 Managed Instance veranschaulichen.

Weitere Informationen finden Sie unter:

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

1. Wiederherstellen der OLTP-Beispieldatenbank im Arbeitsspeicher

Sie können die AdventureWorksLT-Beispieldatenbank mit ein paar T-SQL-Schritten in SQL Server Management Studio (SSMS) wiederherstellen. Weitere Informationen zur Wiederherstellung einer Datenbank auf Ihrer SQL Managed Instance finden Sie unter Quickstart: Wiederherstellen einer Datenbank auf einer Azure SQL Managed Instance mit SSMS.

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.

  1. Öffnen Sie SSMS, und stellen Sie eine Verbindung mit Ihrer SQL Managed Instance her.

    Hinweis

    Verbindungen zu Ihrer Azure SQL Managed Instance von Ihrer lokalen Workstation oder einer Azure VM können sicher hergestellt werden, ohne den öffentlichen Zugang zu öffnen. Erwägen Sie Quickstart: Konfigurieren Sie eine Point-to-Site-Verbindung zu Azure SQL Managed Instance von vor Ort aus oder Quickstart: Konfigurieren Sie eine Azure-VM für die Verbindung mit Azure SQL Managed Instance.

  2. Klicken Sie im Objekt-Explorer mit der rechten Maustaste auf Ihre verwaltete Instanz, und wählen Sie Neue Abfrage aus, um ein neues Abfragefenster zu öffnen.

  3. Führen Sie die folgende T-SQL-Anweisung aus, die öffentlich zugängliche vorkonfigurierte Speichercontainer und einen gemeinsamen Zugriffssignaturschlüssel verwendet, um ein Credential in Ihrer SQL Managed Instance zu erstellen. Bei öffentlich verfügbaren Speicher ist keine SAS-Signatur erforderlich.

    CREATE CREDENTIAL [https://mitutorials.blob.core.windows.net/examples/]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE';

  4. Führen Sie die folgende Anweisung aus, um die Beispieldatenbank AdventureWorksLT wiederherzustellen.

    RESTORE DATABASE [AdventureWorksLT] 
FROM URL = 'https://mitutorials.blob.core.windows.net/examples/AdventureWorksLT2022.bak';

  5. Führen Sie die folgende Anweisung aus, um den Status Ihres Wiederherstellungsvorgangs nachzuverfolgen.

    SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete
   , dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE');

  6. Wenn der Wiederherstellungsvorgang abgeschlossen ist, zeigen Sie die AdventureWorksLT-Datenbank im Objekt-Explorer an. Sie können überprüfen, ob die AdventureWorksLT-Datenbank wiederhergestellt wurde, indem Sie die Ansicht sys.dm_operation_status verwenden.

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 erstellen eine Azure VM in derselben Azure-Region wie die von Ihnen SQL Managed Instance. Sie können aber stattdessen ostress.exe auf Ihrer lokalen Workstation ausführen, sofern Sie eine Verbindung mit Ihrer von Azure SQL Managed Instance 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. Stellen Sie eine neue AdventureWorksLT-Datenbank in Ihrer SQL Managed Instance wieder her und überschreiben Sie die vorhandene Datenbank, die Sie zuvor installiert haben, mit WITH REPLACE.

    RESTORE DATABASE [AdventureWorksLT] 
FROM URL = 'https://mitutorials.blob.core.windows.net/examples/AdventureWorksLT2022.bak'
WITH REPLACE;

  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

Abhängig von der Konfiguration Ihrer SQL Managed Instance können Sie für diese Abfrage erhebliche Leistungssteigerungen erwarten, wenn Sie den geclusterten Columnstore-Index im Vergleich zum herkömmlichen Index verwenden.

Zugehöriger Inhalt