In-Memory-Beispiel in Azure SQL Managed Instance
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:
- In-Memory-OLTP: Übersicht und Verwendungsszenarien (einschließlich Verweisen auf Fallstudien und Informationen für Einsteiger)
- Dokumentation für In-Memory-OLTP
- Beschreibung von Columnstore-Indizes
- HTAP (Hybrid Transactional/Analytical Processing), auch als Real-time Operational Analytics bekannt
Eine einfachere, aber visuell ansprechendere Demo der Leistung von In-Memory-OLTP finden Sie hier:
- Version: in-memory-oltp-demo-v1.0
- Quellcode: in-memory-oltp-demo-source-code
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.
Ö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.
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.
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';
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';
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');
Wenn der Wiederherstellungsvorgang abgeschlossen ist, zeigen Sie die
AdventureWorksLT
-Datenbank im Objekt-Explorer an. Sie können überprüfen, ob dieAdventureWorksLT
-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
- Diskussion zu „ostress.exe“ unter Beispieldatenbank für In-Memory-OLTP.
- Beispieldatenbank für In-Memory-OLTP.
- Blog zur Installation von „ostress.exe“.
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:
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;
Kopieren Sie den Text der vorhergehenden „ostress.exe“-Befehlszeile in die Zwischenablage.
Ersetzen Sie
<placeholders>
für die Parameter-S -U -P -d
durch die ordnungsgemäßen tatsächlichen Werte.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:
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;
Bearbeiten Sie die ostress.exe-Befehlszeile so, dass alle Vorkommen von _inmem durch _ondisk ersetzt werden.
Führen Sie „ostress.exe“ ein zweites Mal aus, und erfassen Sie die Dauer.
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
Stellen Sie eine neue
AdventureWorksLT
-Datenbank in Ihrer SQL Managed Instance wieder her und überschreiben Sie die vorhandene Datenbank, die Sie zuvor installiert haben, mitWITH REPLACE
.RESTORE DATABASE [AdventureWorksLT] FROM URL = 'https://mitutorials.blob.core.windows.net/examples/AdventureWorksLT2022.bak' WITH REPLACE;
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.
- Das T-SQL-Skript erstellt die erforderlichen In-Memory-Objekte in der in Schritt 1 erstellten Beispieldatenbank
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 ...;
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
- Schnellstart 1: In-Memory-OLTP-Technologien für höhere T-SQL-Leistung
- Verwenden von In-Memory-OLTP zur Verbesserung der Anwendungsleistung in SQL-Datenbank
- Überwachen des In-Memory-OLTP-Speichers
- In-Memory-OLTP
- ColumnStore-Indizes
- Operative Echtzeitanalyse mit Columnstore-Indizes
- Technischer Artikel: In-Memory-OLTP – Allgemeine Arbeitsauslastungsmuster und Überlegungen zur Migration in SQL Server 2014
Feedback
https://aka.ms/ContentUserFeedback.
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Tickets als Feedbackmechanismus für Inhalte auslaufen lassen und es durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter:Einreichen und Feedback anzeigen für