Udostępnij za pośrednictwem

Szybsza tabela tymczasowa i zmienna tabelowa przy użyciu optymalizacji pamięci

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Jeśli używasz tabel tymczasowych, zmiennych tabeli lub parametrów z wartościami tabeli, rozważ ich konwersje, aby użyć tabel zoptymalizowanych pod kątem pamięci i zmiennych tabeli w celu zwiększenia wydajności. Zmiany kodu są zwykle minimalne.

W tym artykule opisano:

  • Scenariusze, które przemawiają za konwersją na przetwarzanie w pamięci.
  • Działania techniczne dotyczące wdrażania konwersji do pamięci operacyjnej.
  • Wymagania wstępne przed konwersją na w pamięci.
  • Przykładowy kod, który wyróżnia zalety wydajności optymalizacji pamięci

Odp. Podstawy zmiennych tabeli zoptymalizowanych pod kątem pamięci

Zmienna tabeli zoptymalizowana pod kątem pamięci zapewnia doskonałą wydajność przy użyciu tego samego algorytmu zoptymalizowanego pod kątem pamięci i struktur danych, które są używane przez tabele zoptymalizowane pod kątem pamięci. Wydajność jest zmaksymalizowana, gdy zmienna tabeli jest uzyskiwana z poziomu natywnie skompilowanego modułu.

Zmienna tabeli zoptymalizowana pod kątem pamięci:

  • Jest przechowywany tylko w pamięci i nie ma składnika na dysku.
  • Nie obejmuje żadnych działań we/wy.
  • Nie obejmuje tempdb użycia ani rywalizacji.
  • Można przekazać do procedury składowanej jako parametr o typie tabelarycznym (TVP).
  • Musi mieć co najmniej jeden indeks, skrót lub nieklastrowany.
    • W przypadku indeksu skrótu liczba wiaderek powinna optymalnie być 1–2 razy większa od liczby oczekiwanych unikatowych kluczy indeksu, ale przeszacowanie liczby wiaderek jest zazwyczaj akceptowalne (do 10X). Aby uzyskać więcej informacji, zobacz Indeksy dotyczące tabel Memory-Optimized.

Typy obiektów

In-Memory OLTP udostępnia następujące obiekty, których można użyć do optymalizacji pamięci tabel tymczasowych i zmiennych tabeli:

  • Tabele zoptymalizowane pod kątem pamięci
    • Trwałość = SCHEMA_ONLY
  • Zmienne tabeli zoptymalizowane pod kątem pamięci
    • Należy zadeklarować w dwóch krokach (zamiast wbudowanych):
      • CREATE TYPE my_type AS TABLE ...; wtedy
      • DECLARE @mytablevariable my_type;.

B. Scenariusz: zamiana globalnej tabeli tymczasowej

Zastąpienie globalnej tabeli tymczasowej tabelą zoptymalizowaną pod kątem pamięci, typu SCHEMA_ONLY, jest dość proste. Największą zmianą jest utworzenie tabeli w czasie wdrażania, a nie w czasie wykonywania. Tworzenie tabel zoptymalizowanych pod kątem pamięci trwa dłużej niż tworzenie tradycyjnych tabel ze względu na optymalizacje czasu kompilacji. Tworzenie i usuwanie tabel zoptymalizowanych pod kątem pamięci jako część obciążenia operacyjnego online może wpływać na wydajność tego obciążenia, jak również na wydajność operacji redo w drugorzędnych replikach Grupy dostępności Always On i podczas odzyskiwania bazy danych.

Załóżmy, że masz następującą globalną tabelę tymczasową.

CREATE TABLE ##tempGlobalB
(
    Column1 INT NOT NULL,
    Column2 NVARCHAR (4000)
);

Rozważ zastąpienie globalnej tabeli tymczasowej następującą tabelą zoptymalizowaną pod kątem pamięci, która ma trwałość = SCHEMA_ONLY.

CREATE TABLE dbo.soGlobalB
(
    Column1 INT NOT NULL INDEX ix1 NONCLUSTERED,
    Column2 NVARCHAR (4000)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

Kroki

Konwersja z globalnego tymczasowego na SCHEMA_ONLY jest następująca:

  1. Utwórz tabelę dbo.soGlobalB jednorazowo tak samo jak w przypadku dowolnej tradycyjnej tabeli na dysku.
  2. Usuń tworzenie tabeli ##tempGlobalB z poziomu Transact-SQL (T-SQL). Ważne jest, aby utworzyć tabelę zoptymalizowaną pod kątem pamięci w czasie wdrażania, a nie w czasie wykonywania, aby uniknąć obciążenia kompilacyjnego związanego z tworzeniem tabeli.
  3. W swoim T-SQL zastąp wszystkie wzmianki o ##tempGlobalB na dbo.soGlobalB.

C. Scenariusz: Zastępowanie tabeli tymczasowej sesji

Przygotowania do zastąpienia tabeli tymczasowej sesji wymagają użycia większej ilości kodu T-SQL niż w przypadku wcześniejszego scenariusza z tabelą globalną tymczasową. Na szczęście dodatkowy kod T-SQL nie oznacza, że do przeprowadzenia konwersji jest potrzebny żaden większy nakład pracy.

Podobnie jak w przypadku scenariusza globalnej tabeli tymczasowej, największą zmianą jest utworzenie tabeli w czasie wdrażania, a nie w czasie wykonania, aby uniknąć nakładu związanego z kompilacją.

Załóżmy, że masz następującą tabelę tymczasową sesji.

CREATE TABLE #tempSessionC
(
    Column1 INT NOT NULL,
    Column2 NVARCHAR (4000)
);

Najpierw utwórz następującą funkcję tabelaryczną, aby filtrować na @@spid. Funkcja jest dostępna dla wszystkich tabel SCHEMA_ONLY, które przekonwertujesz z tabel tymczasowych sesji.

CREATE FUNCTION dbo.fn_SpidFilter
(@SpidFilter SMALLINT)
RETURNS TABLE
WITH SCHEMABINDING, NATIVE_COMPILATION
AS
RETURN
    SELECT 1 AS fn_SpidFilter
    WHERE @SpidFilter = @@spid

Następnie utwórz tabelę SCHEMA_ONLY oraz zasady zabezpieczeń w tabeli.

Każda tabela zoptymalizowana pod kątem pamięci musi mieć co najmniej jeden indeks.

  • W przypadku tabeli dbo.soSessionC indeks HASH może być lepszy, jeśli obliczymy odpowiednie BUCKET_COUNT. Jednak w tym przykładzie uprościmy do indeksu typu NONCLUSTERED.
CREATE TABLE dbo.soSessionC
(
    Column1 INT NOT NULL,
    Column2 NVARCHAR (4000) NULL,
    SpidFilter SMALLINT DEFAULT (@@spid) NOT NULL,
    CONSTRAINT CHK_soSessionC_SpidFilter CHECK (SpidFilter = @@spid),
    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter)
    -- INDEX ix_SpidFilter HASH
    --    (SpidFilter) WITH (BUCKET_COUNT = 64),
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO

CREATE SECURITY POLICY dbo.soSessionC_SpidFilter_Policy
    ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter) ON dbo.soSessionC
    WITH (STATE = ON);
GO

Po trzecie, w ogólnym kodzie T-SQL:

  1. Zmień wszystkie odwołania do tabeli tymczasowej w instrukcjach Transact-SQL na nową tabelę zoptymalizowaną pod kątem pamięci:
    • Stary:#tempSessionC
    • nowy:dbo.soSessionC
  2. Zastąp instrukcje CREATE TABLE #tempSessionC w swoim kodzie instrukcjami DELETE FROM dbo.soSessionC, aby upewnić się, że sesja nie jest narażona na zawartość tabeli wstawioną przez poprzednią sesję z tym samym session_id. Ważne jest, aby utworzyć tabelę zoptymalizowaną pod kątem pamięci w czasie wdrażania, a nie w czasie wykonywania, aby uniknąć obciążenia kompilacyjnego związanego z tworzeniem tabeli.
  3. Usuń instrukcje DROP TABLE #tempSessionC z kodu. Opcjonalnie możesz wstawić instrukcję DELETE FROM dbo.soSessionC w przypadku, gdy rozmiar pamięci jest potencjalnym problemem.

D. Scenariusz: zmienna tabeli może być MEMORY_OPTIMIZED=ON

Tradycyjna zmienna tabeli reprezentuje tabelę w tempdb bazie danych. Aby uzyskać znacznie większą wydajność, można zoptymalizować zmienną tabeli w pamięci.

Oto język T-SQL dla tradycyjnej zmiennej tabeli. Jego zakres kończy się, gdy skończy się partia lub sesja.

DECLARE @tvTableD TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR (10));

Konwertowanie wbudowanego na jawne

Mówi się, że poprzednia składnia tworzy zmienną tabeli inline. Składnia śródliniowa nie obsługuje optymalizacji pamięci. Przekonwertujmy składnię śródliniową na jawną składnię typu.

Zakres: Definicja TYPU utworzona przez pierwszą partię rozdzielaną jest zachowywana nawet po zamknięciu i ponownym uruchomieniu serwera. Ale po pierwszym ograniczniku go zadeklarowana tabela @tvTableC utrzymuje się tylko do momentu osiągnięcia następnego kroku i zakończenia partii.

CREATE TYPE dbo.typeTableD AS TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR (10));
GO

SET NOCOUNT ON;

DECLARE @tvTableD AS dbo.typeTableD;
INSERT INTO @tvTableD (Column1) VALUES (1), (2);
SELECT * FROM @tvTableD;
GO

D.2 Przekształć dane zapisane na dysku na zoptymalizowane pod kątem pamięci

Zmienna tabeli zoptymalizowana pod kątem pamięci nie znajduje się w obiekcie tempdb. Optymalizacja pamięci powoduje zwiększenie szybkości, które są często 10 razy szybsze lub więcej.

Konwersja do zoptymalizowanej pamięci jest realizowana w zaledwie jednym kroku. Rozszerz jawne tworzenie typu w następujący sposób, co dodaje:

  • Indeks. Ponownie każda tabela zoptymalizowana pod kątem pamięci musi mieć co najmniej jeden indeks.
  • MEMORY_OPTIMIZED = WŁĄCZONE.
CREATE TYPE dbo.typeTableD AS TABLE (
    Column1 INT NOT NULL INDEX ix1,
    Column2 CHAR (10))
    WITH (MEMORY_OPTIMIZED = ON);

Gotowy.

E. Wymagania wstępne FILEGROUP dla programu SQL Server

W programie Microsoft SQL Server, aby korzystać z funkcji zoptymalizowanych pod kątem pamięci, baza danych musi mieć grupę plików zadeklarowaną za pomocą MEMORY_OPTIMIZED_DATA.

  • Usługa Azure SQL Database nie wymaga utworzenia tej grupy PLIKÓW.

Warunek wstępny: Poniższy kod Transact-SQL dla elementu FILEGROUP jest wymaganiem wstępnym dla długich przykładów kodu języka T-SQL w kolejnych sekcjach tego artykułu.

  1. Należy użyć SSMS.exe lub innego narzędzia, które może przesyłać język T-SQL.
  2. Wklej przykładowy kod T-SQL FILEGROUP do programu SSMS.
  3. Zmodyfikuj język T-SQL, aby zmienić określone nazwy i ścieżki katalogów na odpowiednie.
  • Wszystkie katalogi w ścieżce NAZWA PLIKU muszą już istnieć, z wyjątkiem ostatniego katalogu, który nie może istnieć.
  1. Uruchom edytowany kod T-SQL.
  • Nie trzeba uruchamiać pliku FILEGROUP T-SQL więcej niż jeden raz, nawet jeśli wielokrotnie dostosowujesz i ponownie uruchamiasz porównanie szybkości T-SQL w następnej podsekcji.
ALTER DATABASE InMemTest2
    ADD FILEGROUP FgMemOptim3 CONTAINS MEMORY_OPTIMIZED_DATA;
GO

ALTER DATABASE InMemTest2
    ADD FILE (NAME = N'FileMemOptim3a', FILENAME = N'C:\DATA\FileMemOptim3a'
    -- C:\DATA\ preexisted.
) TO FILEGROUP FgMemOptim3;
GO

Poniższy skrypt tworzy dla Ciebie grupę plików i konfiguruje zalecane ustawienia bazy danych: enable-in-memory-oltp.sql

Aby uzyskać więcej informacji na temat ALTER DATABASE ... ADD funkcji FILE i FILEGROUP, zobacz:

F. Szybki test w celu udowodnienia poprawy szybkości

Ta sekcja zawiera kod Transact-SQL, który można uruchomić w celu przetestowania i porównania szybkości zwiększenia dla INSERT-DELETE przy użyciu zmiennej tabeli zoptymalizowanej dla pamięci. Kod składa się z dwóch połówek, które są prawie takie same, z tym że w pierwszej połówce typ tabeli jest optymalizowany dla pamięci.

Test porównawczy trwa około 7 sekund. Aby uruchomić próbkę:

  1. Warunek wstępny: Musisz już uruchomić plik FILEGROUP T-SQL z poprzedniej sekcji.
  2. Uruchom następujący skrypt języka T-SQL INSERT-DELETE.
  • Zwróć uwagę na instrukcję GO 5001 , która ponownie przesyła kod T-SQL 5001 razy. Możesz dostosować liczbę i ponownie uruchomić.

Podczas uruchamiania skryptu w usłudze Azure SQL Database upewnij się, że uruchomiono z maszyny wirtualnej w tym samym regionie.

PRINT ' ';
PRINT '---- Next, memory-optimized, faster. ----';
DROP TYPE IF EXISTS dbo.typeTableC_mem;
GO

CREATE TYPE dbo.typeTableC_mem -- !!  Memory-optimized.
AS TABLE (
    Column1 INT NOT NULL INDEX ix1,
    Column2 CHAR(10)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

DECLARE @dateString_Begin NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_Begin,
    ' = Begin time, _mem.'
);
GO

SET NOCOUNT ON;

DECLARE @tvTableC dbo.typeTableC_mem;-- !!

INSERT INTO @tvTableC (Column1) VALUES (1), (2);
INSERT INTO @tvTableC (Column1) VALUES (3), (4);

DELETE @tvTableC;GO 5001

DECLARE @dateString_End NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_End,
    ' = End time, _mem.'
);
GO

DROP TYPE IF EXISTS dbo.typeTableC_mem;
GO

---- End memory-optimized.
-------------------------------------------------
---- Start traditional on-disk.
PRINT ' ';
PRINT '---- Next, tempdb based, slower. ----';

DROP TYPE IF EXISTS dbo.typeTableC_tempdb;
GO

CREATE TYPE dbo.typeTableC_tempdb -- !!  Traditional tempdb.
AS TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR(10)
);
GO

DECLARE @dateString_Begin NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_Begin,
    ' = Begin time, _tempdb.'
);
GO

SET NOCOUNT ON;

DECLARE @tvTableC dbo.typeTableC_tempdb;-- !!

INSERT INTO @tvTableC (Column1) VALUES (1), (2);
INSERT INTO @tvTableC (Column1) VALUES (3), (4);

DELETE @tvTableC;
GO 5001

DECLARE @dateString_End NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_End,
    ' = End time, _tempdb.'
);
GO

DROP TYPE IF EXISTS dbo.typeTableC_tempdb;
GO

PRINT '---- Tests done. ----';
GO

Oto zestaw wyników.

---- Next, memory-optimized, faster. ----
2016-04-20 00:26:58.033  = Begin time, _mem.
Beginning execution loop
Batch execution completed 5001 times.
2016-04-20 00:26:58.733  = End time, _mem.

---- Next, tempdb based, slower. ----
2016-04-20 00:26:58.750  = Begin time, _tempdb.
Beginning execution loop
Batch execution completed 5001 times.
2016-04-20 00:27:05.440  = End time, _tempdb.
---- Tests done. ----

G. Przewidywanie aktywnego użycia pamięci

Możesz dowiedzieć się, jak przewidzieć potrzeby aktywnej pamięci tabel zoptymalizowanych pod kątem pamięci przy użyciu następujących zasobów:

W przypadku większych zmiennych tabeli indeksy nieklastrowane używają większej ilości pamięci niż w przypadku tabel zoptymalizowanych pod kątem pamięci. Tym większa liczba wierszy i klucz indeksu, tym większa jest różnica.

Jeśli dostęp do zmiennej tabeli zoptymalizowanej pod kątem pamięci jest uzyskiwany tylko z jedną dokładną wartością klucza na dostęp, indeks skrótu może być lepszym wyborem niż indeks nieklastrowany. Jednak jeśli nie możesz oszacować odpowiedniej wartości dla BUCKET_COUNT, indeks nierozproszony (NONCLUSTERED) jest dobrym drugim wyborem.

Treści powiązane