Share via

Przykład w pamięci w usłudze Azure SQL Managed Instance

  • Artykuł

Dotyczy:Azure SQL Managed Instance

Technologie w pamięci w usłudze Azure SQL Managed Instance umożliwiają zwiększenie wydajności aplikacji i potencjalnie obniżenie kosztów bazy danych. Korzystając z technologii w pamięci w usłudze Azure SQL Managed Instance, można osiągnąć poprawę 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 Managed Instance.

Aby uzyskać więcej informacji, zobacz:

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

1. Przywracanie przykładowej bazy danych OLTP w pamięci

Przykładową AdventureWorksLT bazę danych można przywrócić za pomocą kilku kroków języka T-SQL w programie SQL Server Management Studio (SSMS). Aby uzyskać więcej informacji na temat przywracania bazy danych do wystąpienia zarządzanego SQL, zobacz Szybki start: przywracanie bazy danych do usługi Azure SQL Managed Instance przy użyciu programu SSMS.

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.

  1. Otwórz program SSMS i połącz się z wystąpieniem zarządzanym SQL.

    Uwaga

    Połączenie do usługi Azure SQL Managed Instance z lokalnej stacji roboczej lub maszyny wirtualnej platformy Azure można bezpiecznie wykonać bez otwierania dostępu publicznego. Rozważ szybki start: konfigurowanie połączenia punkt-lokacja z usługą Azure SQL Managed Instance ze środowiska lokalnego lub z przewodnika Szybki start: konfigurowanie maszyny wirtualnej platformy Azure w celu nawiązania połączenia z usługą Azure SQL Managed Instance.

  2. W Eksplorator obiektów kliknij prawym przyciskiem myszy wystąpienie zarządzane i wybierz pozycję Nowe zapytanie, aby otworzyć nowe okno zapytania.

  3. Uruchom następującą instrukcję języka T-SQL, która używa publicznie dostępnego wstępnie skonfigurowanego kontenera magazynu i klucza sygnatury dostępu współdzielonego w celu utworzenia poświadczeń w wystąpieniu zarządzanym SQL. W przypadku publicznie dostępnego magazynu nie jest wymagany podpis SAS.

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

  4. Uruchom następującą instrukcję, aby przywrócić przykładowej AdventureWorksLT bazy danych.

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

  5. Uruchom następującą instrukcję, aby śledzić stan procesu przywracania.

    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. Po zakończeniu procesu przywracania wyświetl AdventureWorksLT bazę danych w Eksplorator obiektów. Możesz sprawdzić, czy AdventureWorksLT baza danych została przywrócona przy użyciu widoku sys.dm_operation_status .

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. Maszynę wirtualną platformy Azure utworzysz w tym samym regionie świadczenia usługi Azure co wystąpienie zarządzane SQL. Można jednak uruchomić ostress.exe na lokalnej stacji roboczej, o ile można nawiązać połączenie z wystąpieniem zarządzanym usługi Azure SQL.

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. Przywróć nową AdventureWorksLT bazę danych do wystąpienia zarządzanego SQL, zastępując istniejącą zainstalowaną wcześniej bazę danych przy użyciu polecenia WITH REPLACE.

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

  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 zależności od konfiguracji wystąpienia zarządzanego SQL można oczekiwać znacznych wzrostów wydajności dla tego zapytania przy użyciu klastrowanego indeksu magazynu kolumn w porównaniu z tradycyjnym indeksem.

Powiązana zawartość