Snellere tijdelijke tabel- en tabelvariabele met behulp van geheugenoptimalisatie

2025-05-31

Van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance

Als u tijdelijke tabellen, tabelvariabelen of tabelwaardeparameters gebruikt, kunt u overwegen om conversies ervan te gebruiken om tabellen en tabelvariabelen te gebruiken die zijn geoptimaliseerd voor geheugen en tabelvariabelen om de prestaties te verbeteren. De codewijzigingen zijn meestal minimaal.

In dit artikel wordt het volgende beschreven:

Scenario's die pleiten voor conversie naar In-Memory.
Technische stappen voor het implementeren van de conversies naar In-Memory.
Vereisten vóór conversie naar In-Memory.
Een codevoorbeeld waarin de prestatievoordelen van geheugenoptimalisatie worden gemarkeerd

Eén. Basisbeginselen van tabelvariabelen die zijn geoptimaliseerd voor geheugen

Een tabelvariabele die is geoptimaliseerd voor geheugen biedt een grote efficiëntie door gebruik te maken van hetzelfde algoritme en gegevensstructuren die worden gebruikt door tabellen die zijn geoptimaliseerd voor geheugen. De efficiëntie wordt gemaximaliseerd wanneer de tabelvariabele wordt geopend vanuit een systeemeigen gecompileerde module.

Een tabelvariabele die is geoptimaliseerd voor geheugen:

Wordt alleen opgeslagen in het geheugen en heeft geen onderdeel op schijf.
Er is geen IO-activiteit betrokken.
Omvat geen tempdb gebruik of wedijver.
Kan worden doorgegeven aan een opgeslagen proc als een parameter met tabelwaarde (TVP).
Moet ten minste één index hebben, hash of niet-geclusterd.
- Voor een hash-index moet het aantal buckets idealiter 1-2 keer het aantal verwachte unieke indexsleutels zijn, maar het is meestal geen probleem om het aantal buckets te overschatten (tot maximaal 10 keer zo groot). Zie Indexen van Memory-Optimized tabellen voor meer informatie.

Objecttypen

In-Memory OLTP biedt de volgende objecten die kunnen worden gebruikt voor het optimaliseren van tijdelijke tabellen en tabelvariabelen:

Tabellen die zijn geoptimaliseerd voor geheugen
- Duurzaamheid = SCHEMA_ONLY
Tabelvariabelen die zijn geoptimaliseerd voor geheugen
- Moet in twee stappen worden gedeclareerd (in plaats van inline):
  - CREATE TYPE my_type AS TABLE ...; dan
  - DECLARE @mytablevariable my_type;.

B. Scenario: Globale tijdelijke tabel vervangen

Het vervangen van een globale tijdelijke tabel door een voor geheugen geoptimaliseerde SCHEMA_ONLY tabel is vrij eenvoudig. De grootste wijziging is het maken van de tabel tijdens de implementatie, niet tijdens runtime. Het maken van tabellen die zijn geoptimaliseerd voor geheugen duurt langer dan het maken van traditionele tabellen, vanwege de optimalisaties voor compileertijd. Het maken en verwijderen van geheugen-geoptimaliseerde tabellen als onderdeel van de online-workload, beïnvloedt de prestaties van de werklast, evenals de prestaties van het 'redo'-proces op secundaire 'Always On' beschikbaarheidsgroepen en databaseherstel.

Stel dat u de volgende globale tijdelijke tabel hebt.

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

Overweeg de globale tijdelijke tabel te vervangen door de volgende geheugen-geoptimaliseerde tabel met DUURZAAMHEID = ALLEEN_SCHEMA.

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

Stappen

De conversie van globale tijdelijke tabel naar SCHEMA_ONLY bestaat uit de volgende stappen:

Maak de dbo.soGlobalB tabel één keer, net zoals elke traditionele tabel op schijf.
Verwijder uit uw Transact-SQL (T-SQL) de ##tempGlobalB tabelcreatie. Het is belangrijk om de tabel die is geoptimaliseerd voor geheugen te maken tijdens de implementatie, niet tijdens runtime, om de compilatieoverhead te voorkomen die wordt geleverd bij het maken van tabellen.
Vervang in uw T-SQL alle vermeldingen van ##tempGlobalB door dbo.soGlobalB.

C. Scenario: tijdelijke sessietabel vervangen

De voorbereidingen voor het vervangen van een tijdelijke sessietabel omvatten meer T-SQL dan voor het eerdere globale tijdelijke tabelscenario. Gelukkig betekent de extra T-SQL niet dat er meer moeite nodig is om de conversie uit te voeren.

Net als bij het globale tijdelijke tabelscenario is de grootste wijziging het maken van de tabel tijdens de implementatie, niet runtime, om de compilatieoverhead te voorkomen.

Stel dat u de volgende tijdelijke sessietabel hebt.

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

Maak eerst de volgende tabelwaardefunctie om op te filteren @@spid. De functie kan worden gebruikt door alle SCHEMA_ONLY tabellen die u converteert vanuit tijdelijke sessietabellen.

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

Maak ten tweede de SCHEMA_ONLY tabel, plus een beveiligingsbeleid voor de tabel.

Elke tabel die is geoptimaliseerd voor geheugen, moet ten minste één index bevatten.

Voor tabel dbo.soSessionC is een HASH-index mogelijk beter als we de juiste BUCKET_COUNT berekenen. Maar voor dit voorbeeld vereenvoudigen we naar een NIET-GECLUSTERDE 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

Ten derde in uw algemene T-SQL-code:

Wijzig alle verwijzingen naar de tijdelijke tabel in uw Transact-SQL-instructies in de nieuwe tabel die is geoptimaliseerd voor geheugen:
- oud:#tempSessionC
- Nieuw:dbo.soSessionC
Vervang de CREATE TABLE #tempSessionC instructies in uw code door DELETE FROM dbo.soSessionC, om ervoor te zorgen dat een sessie niet zichtbaar is voor de inhoud van een tabel die door een vorige sessie is ingevoegd met dezelfde session_id. Het is belangrijk om de tabel die is geoptimaliseerd voor geheugen te maken tijdens de implementatie, niet tijdens runtime, om de compilatieoverhead te voorkomen die wordt geleverd bij het maken van tabellen.
Verwijder de DROP TABLE #tempSessionC instructies uit uw code. U kunt desgewenst een DELETE FROM dbo.soSessionC instructie invoegen voor het geval de geheugengrootte een potentieel probleem is.

D. Scenario: Tabelvariabele kan geheugen-geoptimaliseerd zijn = AAN

Een traditionele tabelvariabele vertegenwoordigt een tabel in de tempdb database. Voor veel snellere prestaties kunt u de tabelvariabele optimaliseren met geheugen.

Hier volgt de T-SQL voor een traditionele tabelvariabele. Het bereik eindigt wanneer de batch of de sessie wordt beëindigd.

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

Inline converteren naar expliciet

De voorgaande syntaxis zou de tabelvariabele inline creëren. De inlinesyntaxis biedt geen ondersteuning voor geheugenoptimalisatie. Laten we de inline-syntaxis omzetten in de expliciete syntaxis voor TYPE.

Draagwijdte: De TYPE-definitie die door de eerste door go gescheiden batch is gemaakt, blijft behouden, zelfs nadat de server is afgesloten en opnieuw is opgestart. Maar na het eerste scheidingsteken voor go blijft de gedeclareerde tabel @tvTableC behouden totdat de volgende go is bereikt en de batch eindigt.

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 Converteren van expliciet op schijf naar geheugen-geoptimaliseerd

Een tabelvariabele die is geoptimaliseerd voor geheugen, bevindt zich niet in tempdb. Geheugenoptimalisatie resulteert in snelheidsverhogingen die vaak 10 keer sneller of meer zijn.

De conversie naar geheugen-geoptimaliseerde wordt in slechts één stap voltooid. Verbeter het expliciet maken van TYPE op de volgende manier, waardoor het volgende wordt toegevoegd:

Een index. Ook hier moet elke tabel die is geoptimaliseerd voor geheugen ten minste één index hebben.
MEMORY_OPTIMIZED = AAN.

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

Klaar.

E. Vereiste BESTANDSGROEP voor SQL Server

Als u op Microsoft SQL Server functies wilt gebruiken die zijn geoptimaliseerd voor geheugen, moet uw database een FILEGROUP hebben die is gedeclareerd met MEMORY_OPTIMIZED_DATA.

Voor Azure SQL Database is het maken van deze FILEGROUP niet vereist.

Voorwaarde: De volgende Transact-SQL code voor een FILEGROUP is een vereiste voor de lange T-SQL-codevoorbeelden in latere secties van dit artikel.

U moet SSMS.exe of een ander hulpprogramma gebruiken waarmee T-SQL kan worden verzonden.
Plak de T-SQL-voorbeeldcode FILEGROUP in SSMS.
Bewerk de T-SQL om de specifieke namen en mappaden naar wens te wijzigen.

Alle mappen in de waarde BESTANDSNAAM moeten vooraf bestaan, behalve dat de uiteindelijke map niet bestaat.

Voer de bewerkte T-SQL uit.

U hoeft de FILEGROUP T-SQL niet meer dan één keer uit te voeren, zelfs niet als u de T-SQL-snelheidsvergelijking herhaaldelijk aanpast en opnieuw uitvoert in de volgende subsectie.

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

Met het volgende script wordt de bestandsgroep voor u gemaakt en worden aanbevolen database-instellingen geconfigureerd: enable-in-memory-oltp.sql

Voor meer informatie over ALTER DATABASE ... ADD, de FILE en de FILEGROUP, zie:

F. Snelle test om snelheidsverbetering te bewijzen

Deze sectie bevat Transact-SQL code die u kunt uitvoeren om de snelheidswinst voor INSERT-DELETE te testen en te vergelijken met behulp van een tabelvariabele die is geoptimaliseerd voor geheugen. De code bestaat uit twee helften die bijna hetzelfde zijn, behalve dat in de eerste helft het tabeltype geoptimaliseerd is voor geheugen.

De vergelijkingstest duurt ongeveer 7 seconden. Het voorbeeld uitvoeren:

Voorwaarde: U moet de FILEGROUP T-SQL al uit de vorige sectie hebben uitgevoerd.
Voer het volgende T-SQL-INSERT-DELETE-script uit.

Let op de GO 5001 instructie, waarmee de T-SQL 5001 keer opnieuw wordt ingediend. U kunt het nummer aanpassen en opnieuw uitvoeren.

Wanneer u het script uitvoert in een Azure SQL Database, moet u dit uitvoeren vanaf een VIRTUELE machine in dezelfde regio.

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

Hier is het resultatenoverzicht.

---- 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. Actief geheugenverbruik voorspellen

U kunt met de volgende resources leren hoe u de actieve geheugenbehoeften van uw tabellen die zijn geoptimaliseerd voor geheugen kunt voorspellen:

Voor grotere tabelvariabelen gebruiken niet-geclusterde indexen meer geheugen dan voor tabellen die zijn geoptimaliseerd voor geheugen. Hoe groter het aantal rijen en de indexsleutel, hoe meer het verschil toeneemt.

Als de tabelvariabele die is geoptimaliseerd voor geheugen slechts wordt geopend met één exacte sleutelwaarde per toegang, is een hash-index mogelijk een betere keuze dan een niet-geclusterde index. Als u echter geen schatting kunt maken van de juiste BUCKET_COUNT, is een NIET-GECLUSTERDE index een goede tweede keuze.

Feedback

Is deze pagina nuttig?