Rychlejší dočasné tabulky a proměnné tabulek díky optimalizaci paměti

platí pro: SQL Server Azure SQL Database Azure SQL Managed Instance

Pokud používáte dočasné tabulky, proměnné tabulek nebo parametry s hodnotou tabulky, zvažte jejich převedení na tabulky a proměnné tabulek optimalizované pro paměť, abyste zlepšili výkon. Změny kódu jsou obvykle minimální.

Tento článek popisuje:

Scénáře, které argumentují ve prospěch převodu na in-Memory.
Technické kroky pro implementaci převodů na in-Memory
Požadavky před převodem na in-Memory.
Ukázka kódu, která zvýrazňuje výhody optimalizace paměti

A. Základy proměnných tabulek optimalizovaných pro paměť

Proměnná tabulky optimalizovaná pro paměť poskytuje velkou efektivitu pomocí stejného algoritmu optimalizovaného pro paměť a datových struktur, které používají tabulky optimalizované pro paměť. Efektivita se maximalizuje, když se k proměnné tabulky přistupuje z nativně kompilovaného modulu.

Proměnná tabulky optimalizovaná pro paměť:

Je uložen pouze v paměti a nemá na disku žádnou komponentu.
Nezahrnuje žádnou I/O aktivitu.
Nezahrnuje žádné tempdb využití ani spory.
Lze předat do uložené procedury jako parametr tabulkové hodnoty (TVP).
Musí mít alespoň jeden index, buď hodnotu hash, nebo neclusterovanou.
- U hashového indexu by měl být počet přihrádek ideálně 1–2krát větší než počet očekávaných jedinečných indexových klíčů, ale přehnaný odhad počtu přihrádek je obvykle v pořádku (až 10X). Další informace najdete v tématu Indexy v tabulkách Memory-Optimized.

Typy objektů

In-Memory OLTP poskytuje následující objekty, které lze použít k optimalizaci dočasných tabulek a proměnných tabulek:

Tabulky optimalizované pro paměť
- Stálost = SCHEMA_ONLY
Proměnné tabulek optimalizované pro paměť
- Musí být deklarována ve dvou krocích (nikoli přímo):
  - CREATE TYPE my_type AS TABLE ...; potom
  - DECLARE @mytablevariable my_type;.

B. Scénář: Nahrazení globální dočasné tabulky

Nahrazení globální dočasné tabulky tabulkou SCHEMA_ONLY optimalizovanou pro paměť je poměrně jednoduché. Největší změnou je vytvoření tabulky v době nasazení, ne za běhu. Vytváření tabulek optimalizovaných pro paměť trvá déle než vytváření tradičních tabulek kvůli optimalizaci doby kompilace. Vytváření a rušení tabulek optimalizovaných pro paměť v rámci online úlohy by ovlivnilo výkon úlohy a také výkon operací redo na sekundárních replikách skupin dostupnosti Always On a zotavení databáze.

Předpokládejme, že máte následující globální dočasnou tabulku.

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

Zvažte nahrazení globální dočasné tabulky následující tabulkou optimalizovanou pro paměť, která má DURABILITY = SCHEMA_ONLY.

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

Postup

Převod z globálního dočasného na SCHEMA_ONLY je následující postup:

dbo.soGlobalB Vytvořte tabulku jednorázově stejně jako jakoukoli tradiční tabulku na disku.
Z vašeho Transact-SQL (T-SQL) odeberte vytvoření tabulky ##tempGlobalB. Je důležité vytvořit paměťově optimalizovanou tabulku v době nasazení, ne během běhu, abyste se vyhnuli režii kompilace, která je spojena s vytvářením tabulek.
V T-SQL nahraďte všechny zmínky na ##tempGlobalB výrazem dbo.soGlobalB.

C. Scénář: Nahrazení dočasné tabulky pro relaci

Přípravy na nahrazení dočasné tabulky specifické pro relaci zahrnují více T-SQL než v případě scénáře s dřívější globální dočasnou tabulkou. Navíc T-SQL neznamená, že k převodu je potřeba další úsilí.

Stejně jako u scénáře globální dočasné tabulky je největší změnou vytvoření tabulky v době nasazení, ne v době běhu programu, aby se zabránilo režii kompilace.

Předpokládejme, že máte následující dočasnou tabulku.

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

Nejprve vytvořte následující funkci table-value pro filtrování @@spid. Funkce je použitelná všemi tabulkami SCHEMA_ONLY, které převedete z dočasných tabulek relace.

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

Za druhé vytvořte tabulku SCHEMA_ONLY a zásady zabezpečení v tabulce.

Každá tabulka optimalizovaná pro paměť musí mít alespoň jeden index.

Pro tabulku dbo.soSessionC může být index HASH lepší, pokud vypočítáme odpovídající BUCKET_COUNT. Pro tuto ukázku ale zjednodušíme index 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

Za třetí ve vašem obecném kódu T-SQL:

Změňte všechny odkazy na dočasnou tabulku v příkazech Transact-SQL na novou tabulku optimalizovanou pro paměť:
- Starý:#tempSessionC
- Nový:dbo.soSessionC
Nahraďte příkazy označené jako CREATE TABLE #tempSessionC ve vašem kódu za DELETE FROM dbo.soSessionC, aby se zajistilo, že relace nebude vystavena tabulkovému obsahu vloženému předchozí relací se stejným session_id. Je důležité vytvořit paměťově optimalizovanou tabulku v době nasazení, ne během běhu, abyste se vyhnuli režii kompilace, která je spojena s vytvářením tabulek.
Odeberte příkazy DROP TABLE #tempSessionC ze svého kódu. Volitelně můžete vložit DELETE FROM dbo.soSessionC příkaz pro případ, že velikost paměti představuje potenciální problém.

D. Scénář: Proměnná tabulky může být MEMORY_OPTIMIZED=ON

Tradiční proměnná tabulky představuje tabulku v tempdb databázi. Pro mnohem rychlejší výkon můžete optimalizovat proměnnou tabulky pomocí paměti.

Tady je T-SQL pro tradiční proměnnou tabulky. Jeho rozsah končí, když dávka nebo relace skončí.

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

Převod z řádkového na explicitní

Předchozí syntaxe údajně vytváří proměnnou tabulky inline. Vložená syntaxe nepodporuje optimalizaci paměti. Pojďme tedy převést vloženou syntaxi na explicitní syntaxi pro TYP.

Rozsah: Definice TYPE vytvořená první dávkou s „go“ oddělovači přetrvává i po vypnutí a restartování serveru. Ale po prvním příkazu GO se deklarovaná tabulka @tvTableC zachová pouze do doby, dokud není dosaženo dalšího příkazu GO a dávka skončí.

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 Převod explicitního na disku na paměťově optimalizovaný

Proměnná tabulky optimalizovaná pro paměť se nenachází v tempdb. Optimalizace paměti vede ke zvýšení rychlosti, které jsou často 10krát rychlejší nebo více.

Převod na paměťově optimalizovaný je dosaženo pouze v jednom kroku. Vylepšete explicitní vytvoření TYPU následujícím způsobem, který přidá:

Index. Každá tabulka optimalizovaná pro paměť musí mít opět alespoň jeden index.
MEMORY_OPTIMIZED = ZAPNUTO.

CREATE TYPE dbo.typeTableD AS TABLE (
    Column1 INT NOT NULL INDEX ix1,
    Column2 CHAR (10))
    WITH (MEMORY_OPTIMIZED = ON);

Hotový.

E. Požadavky FILEGROUP pro SQL Server

Pokud chcete používat funkce optimalizované pro paměť, musí mít databáze na Microsoft SQL Serveru deklarovanou souborovou skupinu FILEGROUP, která je deklarována pomocí MEMORY_OPTIMIZED_DATA.

Azure SQL Database nevyžaduje vytvoření této skupiny FILEGROUP.

Předpoklad: Následující Transact-SQL kód pro FILEGROUP je předpokladem pro dlouhé ukázky kódu T-SQL v pozdějších částech tohoto článku.

Musíte použít SSMS.exe nebo jiný nástroj, který může odeslat T-SQL.
Vložte ukázkový kód FILEGROUP T-SQL do SSMS.
Upravte T-SQL a změňte jeho konkrétní názvy a cesty k adresářům podle svých představ.

Všechny adresáře uvedené v hodnotě FILENAME musí existovat, s výjimkou toho, že konečný adresář nesmí existovat.

Spusťte upravený T-SQL.

Soubor FILEGROUP T-SQL nemusíte spouštět vícekrát, a to ani v případě, že opakovaně upravujete a znovu spustíte porovnání rychlosti T-SQL v další pododdílu.

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

Následující skript vytvoří skupinu souborů za vás a nakonfiguruje doporučená nastavení databáze: enable-in-memory-oltp.sql

Další informace o ALTER DATABASE ... ADD pro soubor a FILEGROUP najdete v tématech:

F. Rychlý test pro prokázání zlepšení rychlosti

Tato část obsahuje Transact-SQL kód, který můžete spustit pro testování a porovnání zvýšení rychlosti pro INSERT-DELETE při použití proměnné tabulky optimalizované pro paměť. Kód se skládá ze dvou polovin, které jsou téměř stejné, s výjimkou první poloviny typu tabulky je optimalizována pro paměť.

Srovnávací test trvá asi 7 sekund. Chcete-li spustit ukázku:

Předpoklad: Soubor FILEGROUP T-SQL už musíte mít spuštěný z předchozí části.
Spusťte následující skript T-SQL INSERT-DELETE.

GO 5001 Všimněte si příkazu, který znovu odešle T-SQL 5 001krát. Můžete upravit číslo a znovu jej opakovaně spustit.

Při spouštění skriptu ve službě Azure SQL Database nezapomeňte spustit z virtuálního počítače ve stejné oblasti.

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

Tady je soubor výsledků.

---- 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. Predikce spotřeby aktivní paměti

Pomocí následujících prostředků se dozvíte, jak předpovědět potřeby aktivní paměti vašich tabulek optimalizovaných pro paměť:

U větších proměnných tabulky používají neclusterované indexy více paměti, než u tabulek optimalizovaných pro paměť. Čím větší je počet řádků a klíč indexu, tím více se zvyšuje rozdíl.

Pokud je tabulková proměnná optimalizovaná pro paměť přístupna pouze s jednou přesnou hodnotou klíče na každý přístup, může být hašovací index lepší volbou než neclusterovaný index. Pokud ale nemůžete odhadnout odpovídající BUCKET_COUNT, je index NONCLUSTERED dobrou druhou volbou.

Váš názor

Byla tato stránka užitečná?

Last updated on 2025-05-31