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

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:

Aby zapoznać się z pokazem wprowadzającym olTP w pamięci, zobacz:

1. Instalowanie przykładu OLTP w pamięci

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.

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

  2. Połącz się z bazą danych za pomocą programu SQL Server Management Studio (SSMS).

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

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

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 Ustawienia>filtru>filtru jest zoptymalizowane pod kątem pamięci. 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) AS module_name, 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 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.

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

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:

Skrypt dla ostress.exe

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.exepolecenia 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.exeelementu , 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ć ostress.exepolecenie , 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:

  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 ostress.exe wiersza polecenia do schowka.

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

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

Wynik to czas trwania

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

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. ostress.exe Uruchom ponownie po raz drugi i przechwyć wynik czasu trwania.

  4. Ponownie zresetuj bazę danych.

Oczekiwane wyniki porównania

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.

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 dowolnego celu usługi obsługującego indeksy magazynu kolumn.

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

  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 to tabela, która ma równoważny indeks klastrowany w klastrze, skompresowany 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 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.

Powiązana zawartość