Megosztás a következőn keresztül:

Gyorsabb ideiglenes táblák és táblaváltozók a memória optimalizálásával.

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Ha ideiglenes táblákat, táblázatváltozókat vagy táblaértékű paramétereket használ, fontolja meg az átalakításokat, hogy memóriaoptimalizált táblákat és táblázatváltozókat használjon a teljesítmény javítása érdekében. A kódmódosítások általában minimálisak.

Ez a cikk a következőket ismerteti:

  • Olyan forgatókönyvek, amelyek az átalakítás memóriaalapúvá tétele mellett érvelnek.
  • Technikai lépések a memóriabeli átalakítások implementálásához.
  • Előfeltételek a memórián belüli átalakítás előtt.
  • Kódminta, amely kiemeli a memóriaoptimalizálás teljesítménybeli előnyeit

Egy. A memóriaoptimalizált táblaváltozók alapjai

A memóriaoptimalizált táblázatváltozók a memóriaoptimalizált táblák által használt memóriaoptimalizált algoritmus és adatstruktúrák használatával nagy hatékonyságot biztosítanak. A hatékonyság akkor maximalizálható, ha a táblaváltozó natívan lefordított modulból érhető el.

Memória-optimalizált táblaváltozó:

  • A rendszer csak a memóriában tárolja, és nincs összetevő a lemezen.
  • Nem jár IO-tevékenységekkel.
  • tempdb Nem jár kihasználtsággal vagy versengéssel.
  • A tárolt eljárásba továbbítható táblaértékű paraméterként (TVP).
  • Legalább egy indexnek lennie kell, lehetőleg kivonatolt vagy nem klaszteres.
    • Hashindex esetén a vödörszámnak ideális esetben a várt egyedi indexkulcsok számának 1-2-szerese kell lennie, de a vödörszám túlbecsülése általában rendben van, legfeljebb akár tízszeresére. További információ: Indexek Memory-Optimized táblákon.

Objektumtípusok

In-Memory OLTP a következő objektumokat biztosítja, amelyek felhasználhatók a memóriaoptimalizált ideiglenes táblákhoz és táblázatváltozókhoz:

  • Memóriaoptimalizált táblák
    • Tartósság = SCHEMA_ONLY
  • Memóriaoptimalizált táblaváltozók
    • Két lépésben kell deklarálni (nem beágyazott módon):
      • CREATE TYPE my_type AS TABLE ...;, akkor
      • DECLARE @mytablevariable my_type;.

B. Forgatókönyv: Globális ideiglenes táblának a cseréje

A globális ideiglenes táblák lecserélése memóriaoptimalizált SCHEMA_ONLY táblára meglehetősen egyszerű. A legnagyobb változás az, hogy a táblát üzembe helyezéskor kell létrehozni, nem futtatókörnyezetben. A memóriaoptimalizált táblák létrehozása hosszabb időt vesz igénybe, mint a hagyományos táblák létrehozása a fordítási idő optimalizálása miatt. A memóriaoptimalizált táblák online számítási feladat részeként történő létrehozása és elvetése hatással lenne a számítási feladat teljesítményének, valamint az Always On rendelkezésre állási csoport másodfokainak és az adatbázis-helyreállításnak a teljesítményére.

Tegyük fel, hogy a következő globális ideiglenes táblával rendelkezik.

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

Érdemes lehet lecserélni a globális ideiglenes táblát a következő memóriaoptimalizált táblára, amelynek tartóssága = SCHEMA_ONLY.

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

Lépések

A globális ideiglenesről SCHEMA_ONLY-ra történő átalakítás a következő lépésekből áll:

  1. Hozza létre egyszer a dbo.soGlobalB táblát, ugyanúgy, mint bármely hagyományos lemezen lévő táblát.
  2. Távolítsa el a ##tempGlobalB tábla létrehozását a Transact-SQL (T-SQL) közül. Fontos, hogy a memóriaoptimalizált táblát ne futásidőben, hanem üzembe helyezéskor hozza létre, hogy elkerülje a táblalétrehozással járó fordítási többletterhelést.
  3. A T-SQL-ben cserélje le minden ##tempGlobalB előfordulását dbo.soGlobalB-re.

C. Forgatókönyv: Ideiglenes munkamenet-tábla cseréje

A munkamenet-ideiglenes táblák cseréjének előkészületei több T-SQL-t foglalnak magukban, mint a korábbi globális ideiglenes táblaforgatókönyv esetében. Szerencsére az extra T-SQL nem jelenti azt, hogy több erőfeszítésre van szükség az átalakítás elvégzéséhez.

A globális temp table-forgatókönyvhöz hasonlóan a legnagyobb változás az, hogy a táblázatot üzembe helyezéskor hozza létre, nem pedig futtatókörnyezetben, hogy elkerülje a fordítási többletterhelést.

Tegyük fel, hogy a következő munkamenet-ideiglenes táblával rendelkezik.

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

Először hozza létre a következő tábla-érték függvényt @@spida szűréshez. A függvényt az ideiglenes munkamenet-táblákból konvertált összes SCHEMA_ONLY tábla használhatja.

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

Másodszor hozza létre a SCHEMA_ONLY táblát, valamint egy biztonsági szabályzatot a táblán.

Minden memóriaoptimalizált táblának legalább egy indexel kell rendelkeznie.

  • A dbo.soSessionC tábla esetében a HASH-index jobb lehet, ha kiszámítjuk a megfelelő BUCKET_COUNT. Ebben a mintában azonban leegyszerűsítjük a használatot egy NEMCLUSTERED indexre.
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

Harmadszor, az általános T-SQL-kódban:

  1. Módosítsa a Transact-SQL utasításokban szereplő ideiglenes táblára mutató összes hivatkozást az új memóriaoptimalizált táblára:
    • Régi:#tempSessionC
    • Új:dbo.soSessionC
  2. Cserélje le a CREATE TABLE #tempSessionC utasításokat a kódjában DELETE FROM dbo.soSessionC-re annak érdekében, hogy a munkamenet ne tegye ki magát az azonos session_id-vel rendelkező előző munkamenet által beszúrt táblázattartalomnak. Fontos, hogy a memóriaoptimalizált táblát ne futásidőben, hanem üzembe helyezéskor hozza létre, hogy elkerülje a táblalétrehozással járó fordítási többletterhelést.
  3. Távolítsa el a DROP TABLE #tempSessionC utasításokat a kódból. Szükség esetén utasítást DELETE FROM dbo.soSessionC is beszúrhat arra az esetre, ha a memória mérete problémát okozhat.

D. Forgatókönyv: A táblaváltozó lehet MEMORY_OPTIMIZED=ON

A hagyományos táblaváltozók az adatbázisban lévő táblát tempdb jelölik. A sokkal gyorsabb teljesítmény érdekében memóriaoptimalizált táblázatváltozót használhat.

Egy hagyományos táblaváltozó T-SQL-jét íme. Hatóköre akkor ér véget, amikor a köteg vagy a munkamenet véget ér.

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

Beágyazott szöveg konvertálása explicitre

Az előző szintaxis azt mondja, hogy a táblaváltozót beágyazottan hozza létre. A beágyazott szintaxis nem támogatja a memóriaoptimalizálást. Ezért alakítsuk át a beágyazott szintaxist a TYPE explicit szintaxisává.

Kiterjedés: Az első go-limited köteg által létrehozott TYPE-definíció a kiszolgáló leállítása és újraindítása után is megmarad. Az első go utasítás után azonban a deklarált tábla @tvTableC csak addig marad meg, amíg el nem éri a következő go utasítást, és a sorozat véget nem ér.

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 Kifejezetten lemezen tároltat memóriára optimalizáltra konvertálás

A memóriaoptimalizált táblaváltozó nem található a fájlban tempdb. A memóriaoptimalizálás olyan sebességnövekedést eredményez, amely gyakran 10-szer gyorsabb vagy több.

A memóriaoptimalizáltvá alakítás csak egy lépésben érhető el. Az explicit TYPE létrehozásának továbbfejlesztése az alábbiak szerint, amely a következőket teszi hozzá:

  • Egy index. Ismét minden memóriaoptimalizált táblának rendelkeznie kell legalább egy indexel.
  • MEMORY_OPTIMIZED = BEKAPCSOLVA.
CREATE TYPE dbo.typeTableD AS TABLE (
    Column1 INT NOT NULL INDEX ix1,
    Column2 CHAR (10))
    WITH (MEMORY_OPTIMIZED = ON);

Kész.

E. Előfeltétel FILEGROUP az SQL Serverhez

A Microsoft SQL Serveren a memóriaoptimalizált funkciók használatához az adatbázisnak rendelkeznie kell egy FÁJLCSOPORTtal, amely deklarálva van a következővel MEMORY_OPTIMIZED_DATA: .

  • Az Azure SQL Database-nek nem kell létrehoznia ezt a FILEGROUP-t.

Előfeltétel: A fájlcsoport következő Transact-SQL kódja a cikk későbbi szakaszaiban szereplő hosszú T-SQL-kódminták előfeltétele.

  1. Használnia kell a SSMS.exe eszközt, vagy más olyan eszközt, amely be tud küldeni T-SQL-t.
  2. Illessze be a FILEGROUP T-SQL mintakódját az SSMS-be.
  3. Szerkessze a T-SQL-t úgy, hogy a nevek és a könyvtár elérési útjai az Ön kívánságai szerint módosuljanak.
  • A FILENAME értékben lévő összes könyvtárnak előre meg kell léteznie, kivéve az utolsó könyvtár, amely nem létezhet előzetesen.
  1. Futtassa a szerkesztett T-SQL-t.
  • A FILEGROUP T-SQL-t nem kell többször futtatnia, még akkor sem, ha a következő alszakaszban ismételten módosítja és újra futtatja a T-SQL sebesség-összehasonlító T-SQL-t.
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

A következő szkript létrehozza a fájlcsoportot, és konfigurálja az ajánlott adatbázis-beállításokat: enable-in-memory-oltp.sql

A FILE-ról és a FILEGROUP-ról ALTER DATABASE ... ADD további információt a következő témakörben talál:

F. Gyorsteszt a sebesség javulásának bizonyításához

Ez a szakasz Transact-SQL kódot tartalmaz, amelyet futtathat a memóriaoptimalizált táblaváltozók INSERT-DELETE sebességnövekedésének teszteléséhez és összehasonlításához. A kód két félből áll, amelyek majdnem azonosak, kivéve, hogy az első félben a táblatípus memóriaoptimalizált.

Az összehasonlító teszt körülbelül 7 másodpercig tart. A minta futtatásához:

  1. Előfeltétel: Az előző szakaszban már futtatnia kell a FILEGROUP T-SQL-t.
  2. Futtassa a következő T-SQL-INSERT-DELETE szkriptet.
  • Figyelje meg az GO 5001 utasítást, amely 5001 alkalommal küldi újra a T-SQL-t. Módosíthatja a számot, és újrafuttathatja.

Amikor egy Azure SQL Database-ben futtatja a szkriptet, győződjön meg arról, hogy ugyanabban a régióban lévő virtuális gépről fut.

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

Itt van az eredmények összessége.

---- 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. Aktív memóriahasználat előrejelzése

A memóriaoptimalizált táblák aktív memóriaigényének előrejelzését az alábbi erőforrásokkal végezheti el:

Nagyobb táblázatváltozók esetén a nem konfigurált indexek több memóriát használnak, mint a memóriaoptimalizált táblák esetében. Minél nagyobb a sorok száma és az indexkulcs, annál nagyobb a különbség.

Ha a memóriaoptimalizált táblaváltozó hozzáférésenként csak egy pontos kulcsértékkel érhető el, a kivonatindex jobb választás lehet, mint egy nemclustered index. Ha azonban nem tudja megbecsülni a megfelelő BUCKET_COUNT-ot, a NEMCLUSTERED index egy jó második választás.

Kapcsolódó tartalom

  • Last updated on