Przykład w pamięci w usłudze Azure SQL Database
Dotyczy: Azure SQL Database
Technologie w pamięci w usłudze Azure SQL Database umożliwiają zwiększenie wydajności aplikacji i potencjalnie zmniejszenie kosztów bazy danych. Korzystając z technologii w pamięci w usłudze Azure SQL Database, można osiągnąć ulepszenia wydajności przy użyciu różnych obciążeń.
Dwa przykłady w tym artykule ilustrują użycie olTP w pamięci, a także indeksy magazynu kolumn w usłudze Azure SQL Database.
Aby uzyskać więcej informacji, zobacz:
- Omówienie i scenariusze użycia OLTP w pamięci (w tym odwołania do analiz przypadków klientów i informacje na temat rozpoczynania pracy)
- Dokumentacja olTP w pamięci
- Przewodnik po indeksach magazynu kolumn
- Hybrydowe przetwarzanie transakcyjne/analityczne (HTAP), nazywane również analizą operacyjną w czasie rzeczywistym
Aby zapoznać się z pokazem wprowadzającym olTP w pamięci, zobacz:
- Wydanie: in-memory-oltp-demo-v1.0
- Kod źródłowy: in-memory-oltp-demo-source-code
Przykładową AdventureWorksLT
bazę danych można utworzyć, wykonując kilka kroków w witrynie Azure Portal. Następnie wykonaj kroki opisane w tej sekcji, aby dodać obiekty OLTP w pamięci do AdventureWorksLT
bazy danych i zademonstrować korzyści z wydajności.
W witrynie Azure Portal utwórz bazę danych w warstwie Premium (DTU) lub Krytyczne dla działania firmy (rdzenie wirtualne) na serwerze logicznym. Ustaw pozycję Źródło na przykładową
AdventureWorksLT
bazę danych. Aby uzyskać szczegółowe instrukcje, zobacz Tworzenie pierwszej bazy danych w usłudze Azure SQL Database.Połącz się z bazą danych za pomocą programu SQL Server Management Studio (SSMS).
Skopiuj skrypt Transact-SQL w pamięci OLTP do schowka. Skrypt T-SQL tworzy niezbędne obiekty w pamięci w przykładowej bazie danych utworzonej
AdventureWorksLT
w kroku 1.Wklej skrypt T-SQL do programu SSMS, a następnie wykonaj skrypt. Klauzula
MEMORY_OPTIMIZED = ON
w instrukcjachCREATE TABLE
ma kluczowe znaczenie. Na przykład:CREATE TABLE [SalesLT].[SalesOrderHeader_inmem]( [SalesOrderID] int IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED ..., ... ) WITH (MEMORY_OPTIMIZED = ON);
Jeśli podczas uruchamiania skryptu języka T-SQL wystąpi błąd 40536, uruchom następujący skrypt języka T-SQL, aby sprawdzić, czy baza danych obsługuje obiekty w pamięci:
SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsXTPSupported');
Wynik 0
oznacza, że funkcja OLTP w pamięci nie jest obsługiwana i 1
oznacza, że jest obsługiwana. Funkcja OLTP w pamięci jest dostępna w warstwach Premium (DTU) i Krytyczne dla działania firmy (rdzeni wirtualnych).
Tabele: Przykład zawiera następujące tabele zoptymalizowane pod kątem pamięci:
SalesLT.Product_inmem
SalesLT.SalesOrderHeader_inmem
SalesLT.SalesOrderDetail_inmem
Demo.DemoSalesOrderHeaderSeed
Demo.DemoSalesOrderDetailSeed
Można filtrować, aby wyświetlać tylko tabele zoptymalizowane pod kątem pamięci w programie Eksplorator obiektów w programie SSMS. Po kliknięciu prawym przyciskiem myszy pozycji Tabele przejdź do >pozycji Ustawienia>filtru>filtru jest zoptymalizowane pod kątem pamięci. Wartość jest 1
równa .
Możesz też wykonywać zapytania dotyczące widoków wykazu, takich jak:
SELECT is_memory_optimized, name, type_desc, durability_desc
FROM sys.tables
WHERE is_memory_optimized = 1;
Natywnie skompilowana procedura składowana: możesz przeprowadzić inspekcję SalesLT.usp_InsertSalesOrder_inmem
za pomocą zapytania widoku wykazu:
SELECT uses_native_compilation, OBJECT_NAME(object_id) AS module_name, definition
FROM sys.sql_modules
WHERE uses_native_compilation = 1;
Jedyną różnicą między następującymi dwiema procedurami składowanymi jest to, że pierwsza procedura używa tabel zoptymalizowanych pod kątem pamięci, podczas gdy druga procedura używa zwykłych tabel na dysku:
SalesLT.usp_InsertSalesOrder_inmem
SalesLT.usp_InsertSalesOrder_ondisk
W tej sekcji zobaczysz, jak używać ostress.exe
narzędzia do wykonywania dwóch procedur składowanych. Możesz porównać, jak długo trwa zakończenie dwóch przebiegów obciążenia.
Najlepiej jest uruchomić ostress.exe
polecenie na maszynie wirtualnej platformy Azure. Maszynę wirtualną platformy Azure utworzysz w tym samym regionie świadczenia usługi Azure, w którym znajduje się baza AdventureWorksLT
danych. Możesz również uruchomić ostress.exe
polecenie na komputerze lokalnym, jeśli możesz nawiązać połączenie z bazą danych Azure SQL Database. Jednak opóźnienie sieci między maszyną a bazą danych na platformie Azure może zmniejszyć wydajność korzystania z olTP w pamięci.
Na maszynie wirtualnej lub na wybranym hoście zainstaluj narzędzia Replay Markup Language (RML). Narzędzia obejmują .ostress.exe
Aby uzyskać więcej informacji, zobacz:
- Omówienie
ostress.exe
w przykładowej bazie danych dla olTP w pamięci. - Przykładowa baza danych dla olTP w pamięci.
W tej sekcji zostanie wyświetlony skrypt języka T-SQL osadzony w naszym ostress.exe
wierszu polecenia. Skrypt używa elementów utworzonych przez zainstalowany wcześniej skrypt języka T-SQL.
Podczas uruchamiania ostress.exe
polecenia zalecamy przekazanie wartości parametrów mających na celu przeciążenie obciążenia przy użyciu obu następujących strategii:
- Uruchom dużą liczbę współbieżnych połączeń przy użyciu polecenia
-n100
. - Każde połączenie powtarza się setki razy przy użyciu polecenia
-r500
.
Możesz jednak zacząć od znacznie mniejszych wartości, takich jak -n10
i -r50
, aby upewnić się, że wszystko działa.
Poniższy skrypt wstawia przykładowe zamówienie sprzedaży z pięcioma elementami wierszy do następujących tabel zoptymalizowanych pod kątem pamięci:
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
Aby ustawić _ondisk wersję poprzedniego skryptu języka T-SQL dla ostress.exe
elementu , należy zastąpić oba wystąpienia podciągów _inmem _ondisk. Te zamiany mają wpływ na nazwy tabel i procedur składowanych.
Aby uruchomić ostress.exe
polecenie , możesz użyć okna wiersza polecenia języka RML. Parametry wiersza polecenia kierują polecenie ostress do:
- Równoczesne uruchamianie 100 połączeń (-n100).
- Każde połączenie uruchamia skrypt języka T-SQL 50 razy (-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"
Aby uruchomić poprzedni ostress.exe
wiersz polecenia:
Zresetuj zawartość danych bazy danych, uruchamiając następujące polecenie w programie SSMS, aby usunąć wszystkie dane wstawione przez wszystkie poprzednie uruchomienia:
EXECUTE Demo.usp_DemoReset;
Skopiuj tekst poprzedniego
ostress.exe
wiersza polecenia do schowka.Zastąp
<placeholders>
wartości parametrów-S -U -P -d
poprawnymi wartościami.Uruchom edytowany wiersz polecenia w oknie cmd języka RML.
Po ostress.exe
zakończeniu zapisuje czas trwania przebiegu jako ostatni wiersz danych wyjściowych w oknie cmd języka RML. Na przykład krótszy przebieg testu trwał około 1,5 minut:
11/12/15 00:35:00.873 [0x000030A8] OSTRESS exiting normally, elapsed time: 00:01:31.867
Po uruchomieniu _inmem wyniku wykonaj następujące kroki dla _ondisk uruchomienia:
Zresetuj bazę danych, uruchamiając następujące polecenie w programie SSMS, aby usunąć wszystkie dane wstawione przez poprzedni przebieg:
EXECUTE Demo.usp_DemoReset;
Edytuj wiersz polecenia,
ostress.exe
aby zastąpić wszystkie _inmem _ondisk.ostress.exe
Uruchom ponownie po raz drugi i przechwyć wynik czasu trwania.Ponownie zresetuj bazę danych.
Nasze testy OLTP w pamięci wykazały, że wydajność wzrosła o dziewięć razy dla tego uproszczonego obciążenia z ostress.exe
uruchomionym na maszynie wirtualnej platformy Azure w tym samym regionie świadczenia usługi Azure co baza danych.
W tej sekcji porównasz wyniki operacji we/wy i statystyk podczas korzystania z indeksu magazynu kolumn w porównaniu z tradycyjnym indeksem b-tree.
W przypadku analizy w czasie rzeczywistym na obciążeniu OLTP najlepiej jest używać indeksu magazynu kolumn nieklastrowanego. Aby uzyskać szczegółowe informacje, zobacz Opis indeksów magazynu kolumn.
Użyj witryny Azure Portal, aby utworzyć nową
AdventureWorksLT
bazę danych na podstawie przykładu. Użyj dowolnego celu usługi obsługującego indeksy magazynu kolumn.Skopiuj memory_analytics_sample sql_in do schowka.
- Skrypt języka T-SQL tworzy niezbędne obiekty w przykładowej bazie danych utworzonej
AdventureWorksLT
w kroku 1. - Skrypt tworzy tabele wymiarów i dwie tabele faktów. Tabele faktów są wypełniane 3,5 mln wierszy.
- W przypadku mniejszych celów usługi wykonanie skryptu może potrwać 15 minut lub dłużej.
- Skrypt języka T-SQL tworzy niezbędne obiekty w przykładowej bazie danych utworzonej
Wklej skrypt T-SQL do programu SSMS, a następnie wykonaj skrypt. Słowo kluczowe COLUMNSTORE w instrukcji
CREATE INDEX
ma kluczowe znaczenie:CREATE NONCLUSTERED COLUMNSTORE INDEX ...;
Ustaw
AdventureWorksLT
na najnowszy poziom zgodności, SQL Server 2022 (160):ALTER DATABASE AdventureworksLT SET compatibility_level = 160;
dbo.FactResellerSalesXL_CCI
to tabela zawierająca indeks klastrowanego magazynu kolumn, który ma zaawansowaną kompresję na poziomie danych .dbo.FactResellerSalesXL_PageCompressed
to tabela, która ma równoważny indeks klastrowany w klastrze, skompresowany tylko na poziomie strony .
Istnieje kilka typów zapytań T-SQL, które można uruchomić, aby zobaczyć ulepszenia wydajności. W kroku 2 skryptu języka T-SQL zwróć uwagę na tę parę zapytań. Różnią się tylko w jednym wierszu:
FROM FactResellerSalesXL_PageCompressed AS a
FROM FactResellerSalesXL_CCI AS a
Indeks klastrowanego magazynu kolumn znajduje się w FactResellerSalesXL_CCI
tabeli.
Poniższy skrypt języka T-SQL wyświetla logiczne statystyki dotyczące aktywności we/wy i czasu przy użyciu funkcji SET STATISTICS IO i SET STATISTICS TIME dla każdego zapytania.
/*********************************************************************
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
W bazie danych korzystającej z celu usługi P2 można oczekiwać około dziewięciu razy wzrostu wydajności dla tego zapytania przy użyciu klastrowanego indeksu magazynu kolumn w porównaniu z tradycyjnym indeksem magazynu wierszy. Dzięki celowi usługi P15 można oczekiwać około 57-krotny wzrost wydajności przy użyciu indeksu magazynu kolumn.
- Szybki start 1: technologie OLTP w pamięci, aby uzyskać szybszą wydajność języka T-SQL
- Używanie olTP w pamięci w celu zwiększenia wydajności aplikacji
- Monitorowanie magazynu OLTP w pamięci
- Blog: OlTP w pamięci w usłudze Azure SQL Database
- OlTP w pamięci
- Indeksy magazynu kolumn
- Analiza operacyjna w czasie rzeczywistym z indeksami magazynu kolumn
- Artykuł techniczny: OlTP w pamięci — typowe wzorce obciążeń i zagadnienia dotyczące migracji w programie SQL Server 2014