Eventi
Ottieni gratuitamente la certificazione in Microsoft Fabric.
19 nov, 23 - 10 dic, 23
Per un periodo di tempo limitato, il team della community di Microsoft Fabric offre buoni per esami DP-600 gratuiti.
Prepara oraQuesto browser non è più supportato.
Esegui l'aggiornamento a Microsoft Edge per sfruttare i vantaggi di funzionalità più recenti, aggiornamenti della sicurezza e supporto tecnico.
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, è possibile convertirli per usufruire delle tabelle e delle variabili di tabella ottimizzata per la memoria per migliorare le prestazioni. Le modifiche al codice sono in genere limitate.
L'articolo illustra:
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:
OLTP in memoria offre gli oggetti seguenti che possono essere usati per l'ottimizzazione per la memoria di tabelle temporanee e variabili di tabella:
CREATE TYPE my_type AS TABLE ...;
, quindiDECLARE @mytablevariable my_type;
.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 il rilascio di tabelle con ottimizzazione per la memoria come parte del carico di lavoro online influirebbe sulle prestazioni del carico di lavoro, nonché sulle prestazioni della fase di rollforward nei database secondari del gruppo di disponibilità AlwaysOn e nel ripristino 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);
Per convertire la tabella temporanea globale in SCHEMA_ONLY, eseguire i passaggi seguenti:
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, una maggior quantità di T-SQL non implica alcuna altra operazione 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)
);
Innanzitutto, creare la funzione con valori di tabella seguente per applicare un filtro in @@spid. La funzione potrà essere usata 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.
Si noti che ogni tabella ottimizzata per la memoria deve contenere almeno un indice.
CREATE TABLE dbo.soSessionC
(
Column1 INT NOT NULL,
Column2 NVARCHAR(4000) NULL,
SpidFilter SMALLINT NOT NULL DEFAULT (@@spid),
INDEX ix_SpidFiler NONCLUSTERED (SpidFilter),
--INDEX ix_SpidFilter HASH
-- (SpidFilter) WITH (BUCKET_COUNT = 64),
CONSTRAINT CHK_soSessionC_SpidFilter
CHECK ( SpidFilter = @@spid ),
)
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:
CREATE TABLE #tempSessionC
nel codice con DELETE FROM dbo.soSessionC
per assicurarsi che una sessione non venga esposta al contenuto della tabella inserito da una sessione precedente con lo stesso session_id. È importante creare la tabella con ottimizzazione per la memoria in fase di distribuzione, non in fase di esecuzione, per evitare il sovraccarico di compilazione associato alla creazione della tabella.DROP TABLE #tempSessionC
dal codice. Facoltativamente, è possibile inserire un'istruzione DELETE FROM dbo.soSessionC
, nel caso le dimensioni della memoria costituiscano un potenziale problemaUna variabile di tabella tradizionale rappresenta una tabella del database tempdb. Per ottenere migliori prestazioni è possibile convertire la variabile di tabella in variabile di tabella con ottimizzazione per la memoria.
Di seguito è riportato il codice 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) );
La sintassi precedente crea la variabile di tabella inline. La sintassi inline non supporta l'ottimizzazione per la memoria. È necessario quindi convertire la sintassi inline nella sintassi esplicita per TYPE.
Ambito: la definizione TYPE creata dal primo batch delimitato da go rimane valida anche dopo che il server è stato arrestato e riavviato. 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 dbo.typeTableD
;
INSERT INTO @tvTableD (Column1) values (1), (2)
;
SELECT * from @tvTableD;
go
Una variabile di tabella ottimizzata per la memoria è memorizzata 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 TYPE esplicita come segue, aggiungendo:
CREATE TYPE dbo.typeTableD
AS TABLE
(
Column1 INT NOT NULL INDEX ix1,
Column2 CHAR(10)
)
WITH
(MEMORY_OPTIMIZED = ON);
La conversione è stata completata.
In Microsoft SQL Server per usare le funzionalità ottimizzate per la memoria, è necessario che il database includa un FILEGROUP dichiarato con MEMORY_OPTIMIZED_DATA.
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.
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:
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:
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. ----
È 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 si è in grado di stimare il valore BUCKET_COUNT appropriato, un indice NONCLUSTERED rappresenta una buona scelta.
Definizione di durabilità per gli oggetti con ottimizzazione per la memoria
Eventi
Ottieni gratuitamente la certificazione in Microsoft Fabric.
19 nov, 23 - 10 dic, 23
Per un periodo di tempo limitato, il team della community di Microsoft Fabric offre buoni per esami DP-600 gratuiti.
Prepara ora