Dela via

Snabbare temporär tabell och tabellvariabel med hjälp av minnesoptimering

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Om du använder temporära tabeller, tabellvariabler eller tabellvärdesparametrar bör du överväga konverteringar av dem för att använda minnesoptimerade tabeller och tabellvariabler för att förbättra prestandan. Kodändringarna är vanligtvis minimala.

I den här artikeln beskrivs:

  • Scenarier som argumenterar för konvertering till In-Memory.
  • Tekniska steg för att implementera konverteringarna till minnebaserad.
  • Förutsättningar före konvertering till Minnesbaserad.
  • Ett kodexempel som visar prestandafördelarna med minnesoptimering

A. Grunderna i minnesoptimerade tabellvariabler

En minnesoptimerad tabellvariabel ger stor effektivitet genom att använda samma minnesoptimerade algoritm och datastrukturer som används av minnesoptimerade tabeller. Effektiviteten maximeras när tabellvariabeln nås inifrån en internt kompilerad modul.

En minnesoptimerad tabellvariabel:

  • Lagras endast i minnet och har ingen komponent på disken.
  • Innebär ingen I/O-aktivitet.
  • Innebär ingen tempdb användning eller konflikt.
  • Kan skickas till en lagrad procedur som en tabellvärdesparameter (TVP).
  • Måste ha minst ett index, antingen hash eller icke-grupperat.
    • För ett hash-index bör antalet bucketar helst vara 1–2 gånger antalet förväntade unika indexnycklar, men att överskatta antalet bucketar är vanligtvis bra (upp till 10X). För mer information, se Index över Memory-Optimized-tabeller.

Objekttyper

In-Memory OLTP innehåller följande objekt som kan användas för minnesoptimering av temporära tabeller och tabellvariabler:

  • Minnesoptimerade tabeller
    • Hållbarhet = SCHEMA_ONLY
  • Minnesoptimerade tabellvariabler
    • Måste anges i två steg (snarare än inline):
      • CREATE TYPE my_type AS TABLE ...;, då
      • DECLARE @mytablevariable my_type;.

B. Scenario: Ersätt global tillfällig tabell

Det är ganska enkelt att ersätta en global tillfällig tabell med en minnesoptimerad SCHEMA_ONLY tabell. Den största ändringen är att skapa tabellen vid distributionen, inte vid körtid. Det tar längre tid att skapa minnesoptimerade tabeller än att skapa traditionella tabeller på grund av kompileringstidsoptimeringarna. Att skapa och släppa minnesoptimerade tabeller som en del av onlinearbetsbelastningen skulle påverka arbetsbelastningens prestanda, samt prestandan för att göra om på AlwaysOn-tillgänglighetsgruppens sekundärfiler och databasåterställning.

Anta att du har följande globala temporära tabell.

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

Överväg att ersätta den globala temporära tabellen med följande minnesoptimerade tabell som har DURABILITY = SCHEMA_ONLY.

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

Steg

Konverteringen från global tillfällig till SCHEMA_ONLY är följande steg:

  1. dbo.soGlobalB Skapa tabellen en gång, precis som med en traditionell disktabell.
  2. Ta bort tabellskapandet ##tempGlobalB från din Transact-SQL (T-SQL). Det är viktigt att skapa den minnesoptimerade tabellen vid distributionstillfället, inte vid körning, för att undvika kompileringskostnaderna som följer med att skapa tabeller.
  3. I din T-SQL ersätter du alla omnämnanden av ##tempGlobalB med dbo.soGlobalB.

C. Scenario: Ersätt temporär tabell för session

Förberedelserna för att ersätta en tillfällig sessionstabell omfattar mer T-SQL än för det tidigare globala temporära tabellscenariot. Lyckligtvis innebär inte den extra T-SQL att det behövs mer arbete för att utföra konverteringen.

Precis som med scenariot med den globala temporära tabellen är den största ändringen att skapa tabellen vid distributionstid, inte körning, för att undvika kompileringskostnaderna.

Anta att du har följande temporära sessionstabell.

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

Skapa först följande tabellvärdefunktion för att filtrera på @@spid. Funktionen kan användas av alla SCHEMA_ONLY tabeller som du konverterar från temporära sessionstabeller.

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

För det andra skapar du tabellen SCHEMA_ONLY plus en säkerhetsprincip i tabellen.

Varje minnesoptimerad tabell måste ha minst ett index.

  • För table dbo.soSessionC kan ett HASH-index vara bättre om vi beräknar lämpliga BUCKET_COUNT. Men för det här exemplet förenklar vi till ett NONCLUSTERED-index.
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

För det tredje, i din allmänna T-SQL-kod:

  1. Ändra alla referenser till temp-tabellen i dina Transact-SQL-instruktioner till den nya minnesoptimerade tabellen:
    • gammal:#tempSessionC
    • Ny:dbo.soSessionC
  2. Ersätt uttrycken CREATE TABLE #tempSessionC i koden med DELETE FROM dbo.soSessionCför att säkerställa att en session inte exponeras för tabellinnehåll som infogats av en tidigare session med samma session_id. Det är viktigt att skapa den minnesoptimerade tabellen vid distributionstillfället, inte vid körning, för att undvika kompileringskostnaderna som följer med att skapa tabeller.
  3. Ta bort DROP TABLE #tempSessionC-satserna från koden. Valfritt kan du också infoga en DELETE FROM dbo.soSessionC instruktion om minnesstorlek är ett potentiellt problem.

D. Scenario: Tabellvariabeln kan vara MEMORY_OPTIMIZED=ON

En traditionell tabellvariabel representerar en tabell i tempdb databasen. För mycket snabbare prestanda kan du minnesoptimera tabellvariabeln.

Här är T-SQL för en traditionell tabellvariabel. Dess omfång upphör när antingen batchen eller sessionen slutar.

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

Konvertera i linje till explicit

Den föregående syntaxen sägs skapa tabellvariabeln inline. Den infogade syntaxen stöder inte minnesoptimering. Så låt oss konvertera den inbyggda syntaxen till den explicita syntaxen för TYP.

Omfattning: Typdefinitionen som skapades av den första goavgränsade batchen kvarstår även efter att servern har stängts av och startats om. Men efter den första go-avgränsare behålls den deklarerade tabellen @tvTableC endast tills nästa steg har nåtts och batchen slutar.

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 Konvertera uttryckligen lagrade data till minnesoptimerad format.

En minnesoptimerad tabellvariabel finns inte i tempdb. Minnesoptimering resulterar i hastighetsökningar som ofta är 10 gånger snabbare eller mer.

Konverteringen till minnesoptimerad sker bara i ett steg. Förbättra skapandet av den explicita TYPE enligt följande, vilket tillsätter:

  • Ett index. Återigen måste varje minnesoptimerad tabell ha minst ett index.
  • MEMORY_OPTIMIZED = PÅ.
CREATE TYPE dbo.typeTableD AS TABLE (
    Column1 INT NOT NULL INDEX ix1,
    Column2 CHAR (10))
    WITH (MEMORY_OPTIMIZED = ON);

Färdig.

E. Nödvändig FILGRUPP för SQL Server

Om du vill använda minnesoptimerade funktioner på Microsoft SQL Server måste databasen ha en FILEGROUP som deklareras med MEMORY_OPTIMIZED_DATA.

  • Azure SQL Database kräver inte att du skapar den här FILGRUPPEN.

Förutsättning: Följande Transact-SQL kod för en FILEGROUP är en förutsättning för de långa T-SQL-kodexemplen i senare avsnitt i den här artikeln.

  1. Du måste använda SSMS.exe eller ett annat verktyg som kan skicka T-SQL.
  2. Klistra in exempelkoden FILEGROUP T-SQL i SSMS.
  3. Redigera T-SQL för att ändra dess specifika namn och katalogsökvägar efter dina önskemål.
  • Alla kataloger i FILENAME-värdet måste finnas i förväg, förutom att den slutliga katalogen inte får finnas i förväg.
  1. Kör din redigerade T-SQL.
  • Du behöver inte köra FILEGROUP T-SQL mer än en gång, även om du upprepade gånger justerar och kör hastighetsjämförelse-T-SQL i nästa underavsnitt.
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

Följande skript skapar filgruppen åt dig och konfigurerar rekommenderade databasinställningar: enable-in-memory-oltp.sql

Mer information om ALTER DATABASE ... ADD för FILE och FILEGROUP finns i:

F. Snabbtest för att bevisa hastighetsförbättring

Det här avsnittet innehåller Transact-SQL kod som du kan köra för att testa och jämföra hastighetsvinsten för INSERT-DELETE från att använda en minnesoptimerad tabellvariabel. Koden består av två halvor som är nästan desamma, förutom att tabelltypen är minnesoptimerad under den första halvan.

Jämförelsetestet varar i cirka 7 sekunder. Så här kör du exemplet:

  1. Förutsättning: Du måste redan ha kört FILEGROUP T-SQL från föregående avsnitt.
  2. Kör följande T-SQL-INSERT-DELETE-skript.
  • Lägg märke till instruktionen GO 5001 som skickas in igen 5 001 gånger för T-SQL. Du kan justera talet och köra igen.

När du kör skriptet i en Azure SQL Database ska du köra från en virtuell dator i samma region.

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

Här är resultatet.

---- 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. Förutsäga aktiv minnesförbrukning

Du kan lära dig att förutsäga de aktiva minnesbehoven för dina minnesoptimerade tabeller med följande resurser:

För större tabellvariabler använder icke-grupperade index mer minne än för minnesoptimerade tabeller. Ju större radantal och indexnyckel, desto mer ökar skillnaden.

Om den minnesoptimerade tabellvariabeln endast nås med ett exakt nyckelvärde per åtkomst kan ett hash-index vara bättre än ett icke-klustrat index. Men om du inte kan uppskatta lämpliga BUCKET_COUNT är ett NONCLUSTERED-index ett bra andra val.

Relaterat innehåll