Udostępnij za pośrednictwem

Badanie obszarów początkowych w In-Memory OLTP

  • Artykuł

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Ten artykuł jest przeznaczony dla dewelopera, który jest w pośpiechu, aby poznać podstawy In-Memory funkcji wydajności OLTP programu Microsoft SQL Server i usługi Azure SQL Database.

W przypadku In-Memory OLTP ten artykuł zawiera następujące informacje:

  • Krótkie wyjaśnienia funkcji.
  • Podstawowe przykłady kodu, które implementują funkcje.

Programy SQL Server i SQL Database mają tylko niewielkie różnice w obsłudze technologii In-Memory.

W rzeczywistych warunkach niektórzy blogerzy odnoszą się do In-Memory OLTP jako Hekaton.

Zalety funkcji In-Memory

Program SQL Server udostępnia funkcje In-Memory, które mogą znacznie poprawić wydajność wielu systemów aplikacji. W tej sekcji opisano najbardziej proste zagadnienia.

Funkcje OLTP (przetwarzanie transakcji online)

Systemy, które muszą przetwarzać dużą liczbę operacji INSERTs SQL jednocześnie, są doskonałymi kandydatami do funkcji OLTP.

  • Nasze testy porównawcze pokazują, że ulepszenia prędkości z 5 razy do 20 razy szybsze są osiągalne dzięki wdrożeniu funkcji In-Memory.

Systemy, które przetwarzają duże obliczenia w Transact-SQL są doskonałymi kandydatami.

  • Procedura składowana przeznaczona do wykonywania dużych obliczeń może działać do 99 razy szybciej.

Później możesz odwiedzić następujące artykuły, które demonstrują poprawę wydajności z In-Memory OLTP.

Funkcje analizy operacyjnej

In-Memory Analytics odnosi się do SELEKtów SQL, które agregują dane transakcyjne, zazwyczaj przez dołączenie klauzuli GROUP BY. Typ indeksu o nazwie magazynu kolumn jest centralnym elementem analizy operacyjnej.

Istnieją dwa główne scenariusze:

  • Batch Operational Analytics odnosi się do procesów agregacji uruchamianych po godzinach pracy lub na pomocniczym sprzęcie, który zawiera kopie danych transakcyjnych.
    • usługi Azure Synapse Analytics odnosi się również do wsadowej analizy operacyjnej.
  • Analiza operacyjna w czasie rzeczywistym odnosi się do procesów agregacji, które działają w godzinach pracy i na podstawowym sprzęcie używanym do obciążeń transakcyjnych.

Obecny artykuł koncentruje się na OLTP, a nie na analizie. Aby uzyskać informacje na temat sposobu, w jaki indeksy magazynu kolumn wprowadzają analitykę do SQL, zobacz:

Magazyn kolumn

Seria doskonałych wpisów na blogu elegancko wyjaśnia indeksy columnstore z kilku perspektyw. Większość wpisów rozwija koncepcję analityki operacyjnej w czasie rzeczywistym, którą obsługuje magazyn kolumnowy. Te wpisy zostały utworzone przez Sunil Agarwal, kierownika programu w firmie Microsoft, w marcu 2016 roku.

Analiza operacyjna w czasie rzeczywistym

  1. Real-Time analiza operacyjna przy użyciu technologii In-Memory
  2. Real-Time Operational Analytics — omówienie nieklastrowanego indeksu magazynowego kolumn (NCCI)
  3. Real-Time Analiza Operacyjna: prosty przykład użycia nieklastrowanego klastrowanego indeksu kolumnowego (NCCI) w SQL Server 2016
  4. Real-Time Operational Analytics: operacje DML i nieklastrowany indeks magazynu kolumn (NCCI) w programie SQL Server 2016
  5. Real-Time Operational Analytics: filtrowany indeks nieklastrowany kolumnowy (NCCI)
  6. Real-Time Analiza operacyjna: opcja opóźnienia kompresji dla nieklastrowanego indeksu magazynu kolumn (NCCI)
  7. Real-Time Analiza operacyjna: opcja Opóźnienie kompresji z NCCI i wydajność
  8. Real-Time Analiza operacyjna: Memory-Optimized Tabele i indeks magazynujący kolumny

Defragmentacja indeksu magazynu kolumn

  1. Defragmentacja indeksu magazynu kolumn przy użyciu polecenia REORGANIZE
  2. Polityka scalania indeksu kolumnowego dla REORGANIZE

Zbiorcze importowanie danych

  1. Klastrowany Magazyn Kolumn: Ładowanie Zbiorcze
  2. Indeks kolumnowy klastrowany: Optymalizacje ładowania danych — minimalne rejestrowanie
  3. Klastrowany indeks magazynu kolumn: Optymalizacja załadunku danych — równoległy import zbiorczy

Funkcje In-Memory OLTP

Przyjrzyjmy się głównym funkcjom In-Memory OLTP.

Tabele zoptymalizowane pod kątem pamięci

Słowo kluczowe języka T-SQL MEMORY_OPTIMIZED w instrukcji CREATE TABLE to sposób tworzenia tabeli w aktywnej pamięci, a nie na dysku.

Tabele zoptymalizowane pod kątem pamięci mają jedną reprezentację siebie w aktywnej pamięci i kopię pomocniczą na dysku.

  • Kopia dysku jest do rutynowego odzyskiwania po zamknięciu i ponownym uruchomieniu serwera lub bazy danych. Ta podwójność pamięci i dysku jest całkowicie ukryta przed Tobą i twoim kodem.

Moduły kompilowane natywnie

Słowo kluczowe języka T-SQL NATIVE_COMPILATION w instrukcji CREATE PROCEDURE to sposób tworzenia natywnie skompilowanej procedury składowanej. Instrukcje języka T-SQL są kompilowane do kodu maszynowego przy pierwszym użyciu procedury natywnej za każdym razem, gdy baza danych jest ponownie uruchamiana w trybie online. Instrukcje języka T-SQL nie obsługują już powolnej interpretacji każdej instrukcji.

  • Widzieliśmy, że efektem natywnej kompilacji są czasy trwania, które są 1/100 czasu trwania interpretacji.

Moduł macierzysty może odwoływać się tylko do tabel zoptymalizowanych pod kątem pamięci i nie może odwoływać się do tabel opartych na dyskach.

Istnieją trzy typy modułów skompilowanych natywnie:

Dostępność w usłudze Azure SQL Database

In-Memory OLTP i Magazyn kolumn są dostępne w usłudze Azure SQL Database. Aby uzyskać szczegółowe informacje, zobacz Optymalizowanie wydajności przy użyciu technologii In-Memory w usłudze SQL Database.

1. Upewnij się, że poziom zgodności >= 130

Ta sekcja rozpoczyna sekwencję ponumerowanych sekcji, które razem pokazują składnię Transact-SQL, której można użyć do zaimplementowania In-Memory funkcji OLTP.

Najpierw ważne jest, aby baza danych była ustawiona na poziom zgodności co najmniej 130. Następnie jest kod T-SQL, aby wyświetlić bieżący poziom zgodności, na który ustawiono bieżącą bazę danych.

SELECT d.compatibility_level
    FROM sys.databases as d
    WHERE d.name = Db_Name();

Następnie jest kod T-SQL, aby zaktualizować poziom, w razie potrzeby.

ALTER DATABASE CURRENT
    SET COMPATIBILITY_LEVEL = 130;

2. Podnieś do poziomu stanu migawki

Gdy transakcja obejmuje zarówno tabelę opartą na dysku, jak i tabelę zoptymalizowaną pod kątem pamięci, nazywamy to transakcją między kontenerami. W takiej transakcji istotne jest, aby część transakcji zoptymalizowana pod kątem pamięci działała na poziomie izolacji transakcji o nazwie SNAPSHOT.

Aby niezawodnie wymusić ten poziom dla tabel zoptymalizowanych pod kątem pamięci w transakcji między kontenerami, zmień ustawienie bazy danych poprzez wykonanie następującego polecenia T-SQL.

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

3. Tworzenie zoptymalizowanej grupy PLIKÓW

Przed utworzeniem tabeli zoptymalizowanej pod kątem pamięci w programie Microsoft SQL Server należy najpierw utworzyć grupę PLIKÓW zadeklarowaną przez użytkownika CONTAINS MEMORY_OPTIMIZED_DATA. Grupa PLIKÓW jest przypisana do bazy danych. Aby uzyskać szczegółowe informacje, zobacz:

W usłudze Azure SQL Database nie trzeba tworzyć takiej grupy PLIKÓW i nie można jej utworzyć.

Poniższy przykładowy skrypt języka T-SQL umożliwia bazę danych dla In-Memory OLTP i konfiguruje wszystkie zalecane ustawienia. Działa zarówno z programem SQL Server, jak i usługą Azure SQL Database: enable-in-memory-oltp.sql.

Należy pamiętać, że nie wszystkie funkcje programu SQL Server są obsługiwane w przypadku baz danych z grupą plików MEMORY_OPTIMIZED_DATA. Aby uzyskać szczegółowe informacje na temat ograniczeń, zobacz: Nieobsługiwane funkcje programu SQL Server dla In-Memory OLTP

4. Tworzenie tabeli zoptymalizowanej pod kątem pamięci

Kluczowe słowo kluczowe Transact-SQL to słowo kluczowe MEMORY_OPTIMIZED.

CREATE TABLE dbo.SalesOrder
    (
        SalesOrderId   integer   not null   IDENTITY
            PRIMARY KEY NONCLUSTERED,
        CustomerId   integer    not null,
        OrderDate    datetime   not null
    )
        WITH
            (MEMORY_OPTIMIZED = ON,
            DURABILITY = SCHEMA_AND_DATA);

Transact-SQL instrukcje INSERT i SELECT względem tabeli zoptymalizowanej pod kątem pamięci są takie same jak w przypadku regularnej tabeli.

ALTER TABLE dla tabel Memory-Optimized

ALTER TABLE... Funkcja ADD/DROP może dodawać lub usuwać kolumnę z tabeli zoptymalizowanej pod kątem pamięci lub indeksu.

  • Nie można uruchomić polecenia CREATE INDEX i DROP INDEX względem tabeli zoptymalizowanej pod kątem pamięci, zamiast tego użyj polecenia ALTER TABLE ... DODAJ/USUŃ INDEKS.
  • Aby uzyskać szczegółowe informacje, zobacz Modyfikowanie Memory-Optimized Tabele.

Planowanie tabel i indeksów zoptymalizowanych pod kątem pamięci

5. Utwórz natywnie skompilowaną procedurę składowaną (natywna procedura)

Kluczowe słowo kluczowe to NATIVE_COMPILATION.

CREATE PROCEDURE ncspRetrieveLatestSalesOrderIdForCustomerId  
        @_CustomerId   INT  
        WITH  
            NATIVE_COMPILATION,  
            SCHEMABINDING  
    AS  
    BEGIN ATOMIC  
        WITH  
            (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
            LANGUAGE = N'us_english')  
      
        DECLARE @SalesOrderId int, @OrderDate datetime;
      
        SELECT TOP 1  
                @SalesOrderId = s.SalesOrderId,  
                @OrderDate    = s.OrderDate  
            FROM dbo.SalesOrder AS s  
            WHERE s.CustomerId = @_CustomerId  
            ORDER BY s.OrderDate DESC;  
      
        RETURN @SalesOrderId;  
    END;

Słowo kluczowe SCHEMABINDING oznacza, że tabele, do których odwołuje się macierzysta proc, nie mogą zostać porzucone, chyba że natywny proc zostanie porzucony jako pierwszy. Aby uzyskać szczegółowe informacje, zobacz Tworzenie natywnie skompilowanych procedur składowanych.

Pamiętaj, że nie trzeba tworzyć natywnie skompilowanej procedury składowanej w celu uzyskania dostępu do tabeli zoptymalizowanej pod kątem pamięci. Można również odwoływać się do tabel zoptymalizowanych pod kątem pamięci z tradycyjnych procedur składowanych i partii ad hoc.

6. Wykonaj natywną procedurę

Wypełnij tabelę dwoma wierszami danych.

INSERT into dbo.SalesOrder  
        ( CustomerId, OrderDate )  
    VALUES  
        ( 42, '2013-01-13 03:35:59' ),
        ( 42, '2015-01-15 15:35:59' );

Następuje wywołanie natywnie skompilowanej procedury składowanej.

DECLARE @LatestSalesOrderId int, @mesg nvarchar(128);
      
EXECUTE @LatestSalesOrderId =  
    ncspRetrieveLatestSalesOrderIdForCustomerId 42;
      
SET @mesg = CONCAT(@LatestSalesOrderId,  
    ' = Latest SalesOrderId, for CustomerId = ', 42);
PRINT @mesg;

To jest rzeczywisty wydruk:

-- 2 = Latest SalesOrderId, for CustomerId = 42

Przewodnik po dokumentacji i następnych krokach

Powyższe proste przykłady stanowią podstawę do poznania bardziej zaawansowanych funkcji In-Memory OLTP. Poniższe sekcje zawierają wskazówki dotyczące szczególnych zagadnień, które mogą być potrzebne do poznania, oraz do tego, gdzie można zobaczyć szczegółowe informacje o każdej z nich.

Jak In-Memory funkcje OLTP działają znacznie szybciej

Poniższe podsekcje krótko opisują, jak In-Memory funkcje OLTP działają od środka, aby zapewnić lepszą wydajność.

Jak tabele zoptymalizowane pod kątem pamięci działają szybciej

Podwójny charakter: Tabela zoptymalizowana pod kątem pamięci ma podwójny charakter: jedną reprezentację w aktywnej pamięci, a drugą na dysku twardym. Każda transakcja jest zatwierdzana dla obu reprezentacji tabeli. Transakcje działają względem znacznie szybszej reprezentacji aktywnej pamięci. Tabele zoptymalizowane pod kątem pamięci korzystają z większej szybkości aktywnej pamięci w porównaniu z dyskiem. Ponadto większa zwinność aktywnej pamięci sprawia, że bardziej zaawansowana struktura tabeli jest zoptymalizowana pod kątem szybkości. Zaawansowana struktura jest również bezstronicowa, więc unika narzutów i rywalizacji o zatrzaski i spinlocki.

Brak blokad: tabela zoptymalizowana pod kątem pamięci opiera się na optymistycznym podejściu do konkurencyjnych celów integralności danych w porównaniu ze współbieżnością i wysoką przepływnością. Podczas transakcji tabela nie umieszcza blokad w żadnej wersji zaktualizowanych wierszy danych. Może to znacznie zmniejszyć konflikt w niektórych systemach o dużym obciążeniu.

wersje wierszy: Zamiast blokad, tabela zoptymalizowana pod kątem pamięci dodaje nową wersję zaktualizowanego wiersza w samej tabeli, a nie w bazie danych tempdb. Oryginalny wiersz jest przechowywany do momentu zatwierdzenia transakcji. Podczas transakcji inne procesy mogą odczytywać oryginalną wersję wiersza.

  • Po utworzeniu wielu wersji wiersza dla tabeli opartej na dysku wersje wierszy są tymczasowo przechowywane w bazie danych tempdb.

Mniej rejestrowania: Wersje wierszy przed i po aktualizacji są przechowywane w tabeli zoptymalizowanej pod kątem pamięci. Para wierszy zawiera wiele informacji, które są tradycyjnie zapisywane w pliku dziennika. Dzięki temu system może zapisywać mniej informacji i rzadziej zapisywać w dzienniku. Jednak zapewniana jest integralność transakcyjna.

Jak szybciej działają natywne procedury

Konwertowanie regularnej procedury składowanej interpretowanej na natywnie skompilowaną procedurę składowaną znacznie zmniejsza liczbę instrukcji do wykonania w czasie wykonywania.

Kompromisy dotyczące funkcji In-Memory

Podobnie jak powszechnie w dziedzinie informatyki, wzrost wydajności zapewniany przez funkcje In-Memory są kompromisem. Lepsze funkcje przynoszą korzyści, które są cenniejsze niż dodatkowe koszty funkcji. Kompleksowe wskazówki dotyczące kompromisów można znaleźć na stronie:

W pozostałej części tej sekcji wymieniono niektóre główne zagadnienia dotyczące planowania i kompromisu.

Kompromisy tabel zoptymalizowanych pod kątem pamięci

Szacowanie pamięci: Musisz oszacować ilość aktywnej pamięci zużywanej przez tabelę zoptymalizowaną pod kątem pamięci. System komputerowy musi mieć odpowiednią pojemność pamięci do hostowania tabeli zoptymalizowanej pod kątem pamięci. Aby uzyskać szczegółowe informacje, zobacz:

Partycjonowanie dużej tabeli: Jednym ze sposobów spełnienia zapotrzebowania na dużą ilość aktywnej pamięci jest podzielenie dużej tabeli na części w pamięci, które przechowują gorących ostatnich wierszy danych w porównaniu z innymi częściami na dysku, które przechowują zimnych starszych wierszy (takich jak zamówienia sprzedaży, które zostały w pełni wysłane i ukończone). Ta partycjonowanie jest procesem ręcznym projektowania i implementacji. Zobacz:

Kompromisy natywnych procesów

  • Natywnie skompilowana procedura składowana nie może uzyskać dostępu do tabeli opartej na dysku. Natywny proc może uzyskiwać dostęp tylko do tabel zoptymalizowanych pod kątem pamięci.
  • Gdy natywny proc jest uruchamiany po raz pierwszy po tym, jak serwer lub baza danych został ostatnio przywrócony do trybu online, natywny proc musi zostać ponownie skompilowany raz. Powoduje to opóźnienie przed uruchomieniem natywnego procesu.

Zaawansowane zagadnienia dotyczące tabel zoptymalizowanych pod kątem pamięci

indeksy dla tabel Memory-Optimized różnią się pod pewnymi względami od indeksów w tradycyjnych tabelach na dysku. Indeksy haszujące są dostępne tylko w tabelach zoptymalizowanych pod kątem pamięci.

Należy zaplanować zapewnienie wystarczającej ilości aktywnej pamięci dla zaplanowanej tabeli zoptymalizowanej pod kątem pamięci i jej indeksów. Zobacz:

Tabela zoptymalizowana pod kątem pamięci może być zadeklarowana z użyciem DURABILITY = SCHEMA_ONLY:

  • Ta składnia nakazuje systemowi odrzucenie wszystkich danych z tabeli zoptymalizowanej pod kątem pamięci, gdy baza danych zostanie przełączona w tryb offline. Utrwalona jest tylko definicja tabeli.
  • Gdy baza danych zostanie przywrócona do trybu online, tabela zoptymalizowana pod kątem pamięci zostanie załadowana z powrotem do aktywnej pamięci, pustej danych.
  • SCHEMA_ONLY tabele mogą być lepszą alternatywą dla #tymczasowe tabel w bazie danych tempdb, gdy mamy do czynienia z wieloma tysiącami wierszy.

Zmienne tabeli można również zadeklarować jako zoptymalizowane pod kątem pamięci. Patrz:

Zaawansowane zagadnienia dotyczące natywnie skompilowanych modułów

Typy modułów skompilowanych natywnie dostępne za pośrednictwem Transact-SQL to:

Natywnie skompilowana funkcja zdefiniowana przez użytkownika (UDF) działa szybciej niż interpretowana funkcja zdefiniowana przez użytkownika. Oto kilka kwestii, które należy wziąć pod uwagę w przypadku funkcji zdefiniowanych przez użytkownika:

  • Gdy instrukcja T-SQL SELECT używa funkcji zdefiniowanej przez użytkownika (UDF), funkcja ta jest zawsze wywoływana raz na każdy zwrócony wiersz.
    • Funkcje UDF nigdy nie są uruchamiane wewnętrznie, a zamiast tego zawsze są wywoływane.
    • Złożona różnica jest mniej istotna niż obciążenie związane z powtarzającymi się wywołaniami, które są inherentne dla wszystkich UDF-ów.
    • Mimo to obciążenie wywołań funkcji UDF jest często akceptowalne na poziomie praktycznym.

Aby zapoznać się z danymi testowymi i wyjaśnieniami na temat wydajności natywnych funkcji zdefiniowanych przez użytkownika, zobacz:

Przewodnik dokumentacji dla tabel zoptymalizowanych pod kątem pamięci

Zapoznaj się z tymi innymi artykułami, które omawiają specjalne zagadnienia dotyczące tabel zoptymalizowanych pod kątem pamięci:

Przewodnik dokumentacji dla natywnych procedur

Poniższy artykuł i jego artykuły podrzędne w spisie treści (TOC) wyjaśniają szczegółowe informacje o natywnie skompilowanych procedurach składowanych.

Oto artykuły, które oferują kod, aby zademonstrować wzrost wydajności, które można osiągnąć przy użyciu In-Memory OLTP: