Przykład w pamięci w usłudze Azure SQL Database

  • Artykuł

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ń.

W tym artykule przedstawiono dwa przykłady ilustrujące użycie olTP w pamięci, a także indeksy magazynu kolumn w usłudze Azure SQL Database.

Aby uzyskać więcej informacji, zobacz:

Aby uzyskać bardziej uproszczony, ale bardziej wizualnie atrakcyjny pokaz wydajności dla olTP w pamięci, zobacz:

1. Zainstaluj przykład OLTP w pamięci

Przykładową AdventureWorksLT bazę danych można utworzyć, wykonując kilka kroków w witrynie Azure Portal. Następnie kroki opisane w tej sekcji wyjaśniają, jak można wzbogacić AdventureWorksLT bazę danych o obiekty OLTP w pamięci i zademonstrować korzyści z wydajności.

Kroki instalacji

  1. W witrynie Azure Portal utwórz bazę danych w warstwie Premium (DTU) lub Krytyczne dla działania firmy (rdzenie wirtualne) na serwerze. 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.

  2. Połączenie do bazy danych za pomocą polecenia SQL Server Management Studio (SSMS).

  3. Skopiuj skrypt TRANSact-SQL OLTP w pamięci do schowka. Skrypt T-SQL tworzy niezbędne obiekty w pamięci w przykładowej bazie danych utworzonej AdventureWorksLT w kroku 1.

  4. Wklej skrypt T-SQL do programu SSMS, a następnie wykonaj skrypt. Klauzula MEMORY_OPTIMIZED = ON w instrukcjach CREATE TABLE ma kluczowe znaczenie. Na przykład:

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

Błąd 40536

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 w pamięci nie jest obsługiwana i 1 oznacza, że jest obsługiwana. Technologie w pamięci są dostępne w warstwach Premium (DTU) i Krytyczne dla działania firmy (rdzenie wirtualne).

Informacje o utworzonych elementach zoptymalizowanych pod kątem pamięci

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 Filtr filtru>Ustawienia> Optymalizuj pamięć. Wartość jest 1ró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), definition
    FROM sys.sql_modules
    WHERE uses_native_compilation = 1;

2. Uruchamianie przykładowego obciążenia OLTP

Jedyną różnicą między następującymi dwiema procedurami składowanymi jest to, że pierwsza procedura używa zoptymalizowanych pod kątem pamięci wersji tabel, 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ć przydatnego narzędzia ostress.exe do wykonywania dwóch procedur składowanych na poziomach stresujących. Możesz porównać, jak długo trwa zakończenie dwóch przebiegów obciążenia.

Instalowanie narzędzi języka RML i narzędzia ostress

W idealnym przypadku należy uruchomić ostress.exe na maszynie wirtualnej platformy Azure. Utworzysz maszynę wirtualną platformy Azure w tym samym regionie AdventureWorksLT świadczenia usługi Azure bazy danych. Można jednak uruchomić ostress.exe na lokalnej stacji roboczej, o ile można nawiązać połączenie z bazą danych Azure SQL Database.

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:

Skrypt dla ostress.exe

W tej sekcji zostanie wyświetlony skrypt języka T-SQL osadzony w naszym wierszu polecenia ostress.exe. Skrypt używa elementów utworzonych przez zainstalowany wcześniej skrypt języka T-SQL.

Podczas uruchamiania ostress.exe 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 _ondisk wersję poprzedniego skryptu języka T-SQL dla ostress.exe, należy zastąpić oba wystąpienia podciągów _inmem _ondisk. Te zamiany mają wpływ na nazwy tabel i procedur składowanych.

Najpierw uruchom obciążenie _inmem stress

Aby uruchomić nasz ostress.exe wiersza polecenia, możesz użyć okna wiersza polecenia 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 wiersz polecenia ostress.exe:

  1. 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;

  2. Skopiuj tekst poprzedniego wiersza polecenia ostress.exe do schowka.

  3. Zastąp <placeholders> wartości parametrów -S -U -P -d poprawnymi rzeczywistymi wartościami.

  4. Uruchom edytowany wiersz polecenia w oknie cmd języka RML.

Wynik to czas trwania

Po zakończeniu ostress.exe 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

Resetuj, edytuj dla _ondisk, a następnie uruchom ponownie

Po uruchomieniu _inmem wyniku wykonaj następujące kroki dla _ondisk uruchomienia:

  1. Zresetuj bazę danych, uruchamiając następujące polecenie w programie SSMS, aby usunąć wszystkie dane wstawione przez poprzedni przebieg:

    EXECUTE Demo.usp_DemoReset;

  2. Edytuj wiersz polecenia ostress.exe, aby zastąpić wszystkie _inmem _ondisk.

  3. Uruchom ponownie ostress.exe po raz drugi i przechwyć wynik czasu trwania.

  4. Ponownie zresetuj bazę danych (w celu odpowiedzialnego usunięcia danych testowych).

Oczekiwane wyniki porównania

Nasze testy w pamięci wykazały, że wydajność poprawiła się o dziewięć razy dla tego uproszczonego obciążenia z ostress uruchomionym na maszynie wirtualnej platformy Azure w tym samym regionie świadczenia usługi Azure co baza danych.

3. Instalowanie przykładu analizy w pamięci

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.

Przygotowywanie testu analitycznego magazynu kolumn

  1. Użyj witryny Azure Portal, aby utworzyć nową AdventureWorksLT bazę danych na podstawie przykładu.

    • Użyj tej dokładnej nazwy.
    • Wybierz dowolną warstwę usługi Premium.

  2. Skopiuj memory_analytics_sample sql_in do schowka.

    • Skrypt T-SQL tworzy niezbędne obiekty w pamięci 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.
    • Ukończenie skryptu może potrwać 15 minut.

  3. 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 ...;

  4. Ustaw AdventureWorksLT na najnowszy poziom zgodności, SQL Server 2022 (160): ALTER DATABASE AdventureworksLT SET compatibility_level = 160;

Tabele kluczy i indeksy magazynu kolumn

  • dbo.FactResellerSalesXL_CCI to tabela zawierająca indeks klastrowanego magazynu kolumn, który ma zaawansowaną kompresję na poziomie danych .

  • dbo.FactResellerSalesXL_PageCompressed jest tabelą, która ma równoważny indeks klastrowany regularny, który jest kompresowany tylko na poziomie strony .

4. Kluczowe zapytania dotyczące porównywania indeksu magazynu kolumn

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 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

W bazie danych z warstwą cenową 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. W przypadku rozwiązania P15 można oczekiwać około 57-krotny wzrost wydajności przy użyciu indeksu magazynu kolumn.

Powiązana zawartość