Tabella temporanea più rapida e variabile di tabella tramite l'ottimizzazione per la memoria

Si applica a:SQL Server , Database SQL di Azure e Istanza gestita di SQL di Azure

Se si usano tabelle temporanee, variabili di tabella o parametri con valori di tabella, prendere in considerazione le conversioni per usare tabelle e variabili di tabella ottimizzate per la memoria per migliorare le prestazioni. Le modifiche al codice sono in genere limitate.

L'articolo illustra:

Scenari a sostegno della conversione in elementi in memoria.
Passaggi tecnici per l'implementazione della conversione in elementi in memoria.
Prerequisiti per la conversione in elementi in memoria.
Un esempio di codice che evidenzia i vantaggi in termini di prestazioni dell'ottimizzazione per la memoria

R. Introduzione alle variabili di tabella ottimizzata per la memoria

Una variabile di tabella ottimizzata per la memoria offre una maggiore efficienza grazie all'uso dello stesso algoritmo e delle stesse strutture di dati ottimizzati per la memoria usati dalle tabelle ottimizzate per la memoria. L'efficienza è particolarmente evidente quando viene eseguito l'accesso alla variabile di tabella dall'interno di un modulo compilato in modo nativo.

Una variabile di tabella ottimizzata per la memoria:

È archiviata solo in memoria e non ha alcun componente su disco.
Non comporta alcuna attività di I/O.
Non comporta alcun tempdb utilizzo o contesa.
Può essere passata in una stored procedure come parametro con valori di tabella (TVP).
Deve avere almeno un indice, hash o non cluster.
- Per un indice hash, il numero di bucket dovrebbe essere idealmente 1 o 2 volte il numero di chiavi di indice univoco previsto. Tuttavia, sovrastimare il numero di bucket è solitamente corretto (fino a 10 X). Per ulteriori informazioni, vedere Indici per le tabelle ottimizzate per la memoria.

Tipi di oggetti

OLTP in memoria offre gli oggetti seguenti che possono essere usati per l'ottimizzazione per la memoria di tabelle temporanee e variabili di tabella:

Tabelle ottimizzate per la memoria
- Durabilità = SCHEMA_ONLY
Variabili di tabella ottimizzate per la memoria
- Devono essere dichiarate in due passaggi (anziché inline):
  - CREATE TYPE my_type AS TABLE ...; , quindi
  - DECLARE @mytablevariable my_type;.

B. Scenario: sostituire la tabella temporanea globale

La sostituzione di una tabella temporanea globale con una tabella SCHEMA_ONLY con ottimizzazione per la memoria è piuttosto semplice. La principale modifica consiste nel creare la tabella in fase di distribuzione, non in fase di esecuzione. La creazione di tabelle con ottimizzazione per la memoria richiede più tempo rispetto alla creazione di tabelle tradizionali, a causa delle ottimizzazioni in fase di compilazione. La creazione e l'eliminazione di tabelle ottimizzate per la memoria come parte del carico di lavoro online influiscono sulle prestazioni del carico di lavoro, sulle prestazioni della ricostruzione sui secondari di Gruppo di Disponibilità Always On e il recupero del database.

Si supponga di avere la tabella temporanea globale seguente.

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

È possibile sostituire la tabella temporanea globale con la tabella ottimizzata per la memoria seguente che include DURABILITY = SCHEMA_ONLY.

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

Fasi

Per convertire la tabella temporanea globale in SCHEMA_ONLY, eseguire i passaggi seguenti:

Creare la dbo.soGlobalB tabella, una sola volta, esattamente come si farebbe con qualsiasi tabella su disco tradizionale.
Rimuovere dal Transact-SQL (T-SQL) la creazione della tabella ##tempGlobalB. È importante creare la tabella ottimizzata per la memoria in fase di distribuzione, non in fase di esecuzione, per evitare il sovraccarico di compilazione fornito con la creazione di tabelle.
In T-SQL sostituire tutte le menzioni di ##tempGlobalB con dbo.soGlobalB.

C. Scenario: Sostituire la tabella temporanea della sessione

Le operazioni preliminari per la sostituzione di una tabella temporanea di sessione implicano un uso maggiore di T-SQL rispetto allo scenario della tabella temporanea globale precedente. Fortunatamente, il T-SQL aggiuntivo non significa che sia necessario alcun ulteriore sforzo per eseguire la conversione.

Come nello scenario di tabelle temporanee globali, la più importante modifica consiste nella creazione della tabella in fase di distribuzione, non di runtime, per evitare il sovraccarico dovuto alla compilazione.

Si supponga di avere la tabella temporanea di sessione seguente.

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

Per prima cosa, creare la seguente funzione di tabella-valore per filtrare in base a @@spid. La funzione è utilizzabile da tutte le tabelle SCHEMA_ONLY convertite da tabelle temporanee di sessione.

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

Creare quindi la tabella SCHEMA_ONLY e i criteri di sicurezza nella tabella.

Ogni tabella ottimizzata per la memoria deve avere almeno un indice.

Per la tabella dbo.soSessionC potrebbe essere consigliabile un indice HASH, se viene calcolato il BUCKET_COUNT corretto. In questo esempio, tuttavia, viene usato per semplicità un indice 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

Infine, nel codice T-SQL generale:

Modifica tutti i riferimenti alla tabella temporanea nelle istruzioni Transact-SQL impostando la nuova tabella ottimizzata per la memoria:
- Precedente:#tempSessionC
- Nuovo:dbo.soSessionC
Sostituisci le CREATE TABLE #tempSessionC istruzioni nel codice con DELETE FROM dbo.soSessionC per garantire che una sessione non sia esposta ai contenuti della tabella inseriti da una sessione precedente con lo stesso session_id. È importante creare la tabella ottimizzata per la memoria in fase di distribuzione, non in fase di esecuzione, per evitare il sovraccarico di compilazione fornito con la creazione di tabelle.
Rimuovi le istruzioni DROP TABLE #tempSessionC dal tuo codice. Facoltativamente, è possibile inserire un'istruzione DELETE FROM dbo.soSessionC nel caso in cui la dimensione della memoria sia un potenziale problema.

D. Scenario: la variabile di tabella può essere MEMORY_OPTIMIZED=ON

Una variabile di tabella tradizionale rappresenta una tabella nel tempdb database. Per prestazioni molto più veloci, è possibile ottimizzare la memoria della variabile di tabella.

Ecco il T-SQL per una variabile di tabella tradizionale. L'ambito termina alla fine del batch o della sessione.

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

Convertire inline in esplicito

La sintassi precedente crea la variabile di tabella inline. La sintassi inline non supporta l'ottimizzazione della memoria. È necessario quindi convertire la sintassi inline nella sintassi esplicita per TYPE.

Portata: La definizione TYPE creata dal primo batch delimitato da go viene mantenuta anche dopo l'arresto e il riavvio del server. Tuttavia, dopo il primo delimitatore go, la tabella dichiarata @tvTableC rimane valida solo fino a quando non vengono raggiunti il go successivo e la fine del batch.

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 Convertire esplicito su disco in ottimizzato per la memoria

Una variabile di tabella ottimizzata per la memoria non risiede in tempdb. L'ottimizzazione per la memoria offre una velocità spesso maggiore di 10 volte o più.

La conversione in ottimizzato per la memoria viene eseguita in un solo passaggio. Migliorare la creazione esplicita di TYPE come indicato di seguito, che aggiunge:

Un indice. Si noti che ogni tabella ottimizzata per la memoria deve contenere almeno un indice.
MEMORY_OPTIMIZED = ON.

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

La conversione è stata completata.

E. FILEGROUP prerequisito per SQL Server

In Microsoft SQL Server, per usare le funzionalità ottimizzate per la memoria, il database deve avere un FILEGROUP dichiarato con MEMORY_OPTIMIZED_DATA.

Il database SQL di Azure non richiede la creazione di questo FILEGROUP.

Prerequisito: il seguente codice Transact-SQL per un FILEGROUP è un prerequisito per i lunghi esempi di codice T-SQL riportati nelle sezioni successive di questo articolo.

È necessario usare SSMS.exe o un altro strumento che può inviare T-SQL.
Incollare il codice T-SQL di FILEGROUP di esempio in SQL Server Management Studio.
Modificare il codice T-SQL per cambiare i nomi e i percorsi di directory in base alle proprie esigenze.

Tutte le directory nel valore FILENAME devono essere già esistenti, ad eccezione della directory finale.

Eseguire il codice T-SQL modificato.

Non è necessario eseguire il FILEGROUP T-SQL più di una volta, anche se si modifica ed esegue ripetutamente il confronto della velocità T-SQL nella sottosezione successiva.

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

Lo script seguente crea automaticamente il filegroup e configura le impostazioni di database consigliate: enable-in-memory-oltp.sql

Per altre informazioni su ALTER DATABASE ... ADD per FILE e FILEGROUP, vedere:

F. Test rapido per dimostrare il miglioramento della velocità

Questa sezione include il codice Transact-SQL che è possibile eseguire per testare e confrontare l'aumento della velocità di INSERT-DELETE dovuto all'uso di una variabile di tabella ottimizzata per la memoria. Il codice è suddiviso in due parti pressoché uguali, ad eccezione del fatto che nella prima parte il tipo di tabella corrisponde a una tabella ottimizzata per la memoria.

Il test di confronto richiede circa 7 secondi. Per eseguire l'esempio:

Prerequisito: è necessario avere già eseguito il codice T-SQL di FILEGROUP della sezione precedente.
Eseguire lo script INSERT-DELETE T-SQL seguente.

Si noti l'istruzione GO 5001 , che invia nuovamente T-SQL 5.001 volte. È possibile modificare il numero ed eseguire di nuovo lo script.

Quando si esegue lo script in un database SQL di Azure, assicurarsi di eseguire lo script da una macchina virtuale nella stessa area.

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

Il set di risultati è il seguente.

---- 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. Stimare il consumo di memoria attiva

È possibile imparare a prevedere la quantità di memoria attiva richiesta dalle tabelle ottimizzate per la memoria con le risorse seguenti:

Per le variabili di tabella di dimensioni maggiori, gli indici non cluster usano una maggior quantità di memoria rispetto a quella usata per le tabelle ottimizzate per la memoria. Maggiore è il totale delle righe e la chiave di indice, maggiore sarà la differenza.

Se l'accesso alla variabile di tabella ottimizzata per la memoria avviene soltanto con un determinato valore di chiave a ogni accesso, è consigliabile usare un indice hash anziché un indice non cluster. Tuttavia, se non è possibile stimare il BUCKET_COUNT appropriato, un indice NONCLUSTERED è una buona seconda scelta.

Commenti e suggerimenti

Questa pagina è stata utile?

Last updated on 2025-05-31