Introduzione alle tabelle con ottimizzazione per la memoria

Articolo
2025-04-30

Si applica a:SQL Server Database SQL di AzureIstanza gestita di SQL di Azure

Le tabelle ottimizzate per la memoria vengono create usando CREATE TABLE (Transact-SQL).

Le tabelle ottimizzate per la memoria sono completamente durevoli per impostazione predefinita e, come per le transazioni delle tabelle tradizionali basate su disco, le transazioni nelle tabelle ottimizzate per la memoria sono completamente ACID (atomicità, coerenza, isolamento e durabilità). Le tabelle con ottimizzazione per la memoria e le stored procedure compilate in modo nativo supportano solo un subset di funzionalità Transact-SQL.

A partire da SQL Server 2016 e nel database SQL di Azure, non esistono limitazioni per le regole di confronto o le tabelle codici specifiche per In-Memory OLTP.

Lo spazio di archiviazione primario per le tabelle ottimizzate per la memoria è la memoria principale. Le righe della tabella vengono lette e scritte in memoria. Una seconda copia dei dati della tabella viene mantenuta su disco, ma solo per motivi di durabilità. Per altre informazioni sulle tabelle durevoli , vedere Creazione e gestione dell'archiviazione per gli oggetti Memory-Optimized . I dati nelle tabelle ottimizzate per la memoria vengono letti solo dal disco durante il ripristino del database, ad esempio dopo il riavvio del server.

Per miglioramenti delle prestazioni ancora superiori, OLTP in memoria supporta tabelle durevoli con durabilità delle transazioni posticipata. Le transazioni durevoli ritardate vengono salvate su disco subito dopo che il commit e il controllo delle transazioni vengono restituiti al client. In cambio di prestazioni migliorate, le transazioni di cui è stato eseguito il commit che non sono persistenti su disco vengono perse in caso di arresto anomalo del server o failover.

Oltre alle tabelle predefinite ottimizzate per la memoria durevoli, SQL Server supporta anche tabelle ottimizzate per la memoria non durevoli, che non vengono registrate e i relativi dati non vengono salvati in modo permanente su disco. Ciò significa che le transazioni in queste tabelle non richiedono operazioni di I/O su disco, ma i dati vengono persi in caso di arresto anomalo o failover del server.

OLTP in memoria viene integrato con SQL Server per fornire un'esperienza senza problemi in tutte le aree, ad esempio sviluppo, distribuzione, gestibilità e supporto. Un database può contenere sia oggetti in memoria che oggetti basati su disco.

Le righe delle tabelle ottimizzate per la memoria dispongono di versioni. Ciò significa che ogni riga nella tabella dispone potenzialmente di più versioni. Tutte le versioni delle righe vengono mantenute nella stessa struttura dei dati della tabella. Il controllo delle versioni delle righe viene utilizzato per consentire letture e scritture simultanee nella stessa riga. Per altre informazioni sulle letture e le scritture simultanee nella stessa riga, vedere Transazioni con tabelle Memory-Optimized.

Nella figura seguente viene illustrato il controllo di più versioni. Nella figura è illustrata una tabella con tre righe e ogni riga ha diverse versioni.

Versioni multiple.

Nella tabella sono presenti tre righe: r1, r2 e r3. r1 include tre versioni, r2 due versioni e r3 quattro versioni. Versioni diverse della stessa riga non occupano necessariamente posizioni di memoria consecutive. Le diverse versioni delle righe possono essere presenti in diverse posizioni della struttura dei dati della tabella.

La struttura dei dati della tabella ottimizzata per la memoria può essere considerata una raccolta di versioni di riga. Le righe nelle tabelle basate su disco sono organizzate in pagine ed extent e alle singole righe viene fatto riferimento utilizzando il numero e l'offset della pagina, alle versioni di riga nelle tabelle ottimizzate per la memoria viene fatto riferimento tramite puntatori alla memoria a 8 byte.

Ai dati nelle tabelle ottimizzate per la memoria è possibile accedere in due modi:

Tramite stored procedure compilate in modo nativo.
Al di fuori di una procedura memorizzata compilata in modo nativo, tramite Transact-SQL interpretato. Queste istruzioni Transact-SQL possono essere incluse in stored procedure interpretate o essere istruzioni Transact-SQL ad hoc.

Accesso ai dati nelle tabelle con ottimizzazione per la memoria

È possibile accedere alle tabelle ottimizzate per la memoria in modo più efficiente tramite Stored Procedures Compilate Nativamente (Natively Compiled Stored Procedures). È inoltre possibile accedere alle tabelle con ottimizzazione per la memoria con Transact-SQL tradizionale interpretato. Con Transact-SQL interpretato si fa riferimento all'accesso a tabelle ottimizzate per la memoria senza una stored procedure compilata in modo nativo. Alcuni esempi di accesso Transact-SQL interpretato includono l'accesso a una tabella ottimizzata per la memoria da un trigger DML o un batch, una vista e una funzione con valori di tabella Transact-SQL ad hoc.

Nella tabella seguente viene riepilogato l'accesso Transact-SQL interpretato e nativo per vari oggetti.

Funzionalità	Accesso tramite una stored procedure compilata in modo nativo	Accesso Transact-SQL interpretato	Accesso con CLR
Tabella con ottimizzazione per la memoria	Sì	Sì	No¹
Tipo di tabella con ottimizzazione per la memoria	Sì	Sì	NO
Stored procedure compilata in modo nativo	L'annidamento di stored procedure compilate in modo nativo ora è supportato. È possibile usare la sintassi EXECUTE all'interno delle stored procedure, purché la procedura a cui viene fatto riferimento sia anch'essa compilata in modo nativo.	Sì	No*

¹Non è possibile accedere a una tabella ottimizzata per la memoria o a una stored procedure compilata in modo nativo dalla connessione di contesto (la connessione da SQL Server durante l'esecuzione di un modulo CLR). È tuttavia possibile creare e aprire un'altra connessione da cui accedere a tabelle ottimizzate per la memoria e stored procedure compilate in modo nativo.

I dati sensibili nelle tabelle ottimizzate per la memoria possono essere protetti tramite Always Encrypted. Si applicano le limitazioni seguenti:

Quando si usa Always Encrypted con enclave sicuri, l'uso di chiavi abilitate per l'enclave per le colonne nelle tabelle ottimizzate per la memoria non è supportato. Ciò significa che la crittografia sul posto non può essere usata e la crittografia iniziale viene eseguita sul client.
Always Encrypted non è supportato per qualsiasi colonna in una tabella ottimizzata per la memoria quando viene fatto riferimento alla tabella in un modulo compilato in modo nativo.

Prestazioni e scalabilità

I fattori seguenti influiscono sui miglioramenti delle prestazioni che è possibile ottenere con In-Memory OLTP:

Comunicazione: Un'applicazione che usa molte chiamate di stored procedure brevi potrebbe visualizzare un miglioramento delle prestazioni inferiore rispetto a un'applicazione con meno chiamate e più funzionalità implementate in ogni stored procedure.

Transact-SQL Esecuzione: In-Memory OLTP consente di ottenere le migliori prestazioni quando si usano stored procedure compilate in modo nativo anziché stored procedure interpretate o esecuzioni di query. L'accesso alle tabelle ottimizzate per la memoria da queste stored procedure può essere vantaggioso.

Analisi intervallo e ricerca punto: Gli indici non cluster ottimizzati per la memoria supportano le analisi degli intervalli e le analisi ordinate. Per le ricerche a punti, gli indici hash ottimizzati per la memoria offrono prestazioni migliori rispetto agli indici non cluster ottimizzati per la memoria. Gli indici non cluster con ottimizzazione per la memoria offrono prestazioni migliori rispetto agli indici basati su disco.

A partire da SQL Server 2016, il piano di query per una tabella ottimizzata per la memoria può analizzare la tabella in parallelo. Ciò migliora le prestazioni delle query di analisi.
- In SQL Server 2016 è ora possibile anche analizzare in parallelo gli indici hash
- e gli indici non cluster.

Operazioni sugli indici: Le operazioni sugli indici non vengono registrate e sono presenti solo in memoria.

Concorrenza: Le applicazioni le cui prestazioni sono influenzate dalla concorrenza a livello di motore, ad esempio conflitti di latch o blocco, migliorano significativamente quando l'applicazione passa a In-Memory OLTP.

Nella tabella seguente sono elencati i problemi relativi a prestazioni e scalabilità presenti in genere nei database relazionali e viene descritto in che modo OLTP in memoria può consentire il miglioramento delle prestazioni.

Problema	Impatto di OLTP in memoria
Prestazioni Utilizzo elevato delle risorse (CPU, I/O, rete o memoria).	CPU (unità centrale di elaborazione) Le stored procedure compilate in modo nativo possono ridurre significativamente l'utilizzo della CPU perché richiedono meno istruzioni per eseguire un'istruzione Transact-SQL rispetto alle stored procedure interpretate. In-Memory OLTP può contribuire a ridurre l'investimento hardware nei carichi di lavoro con scalabilità orizzontale perché un server può potenzialmente offrire la velocità effettiva di più server. Input/Output (Ingresso/Uscita) Se si verifica un collo di bottiglia a livello di I/O in seguito all'elaborazione di pagine di dati o di indice, in OLTP in memoria il collo di bottiglia potrebbe risultare contenuto. Inoltre, il processo di checkpointing degli oggetti OLTP In-Memory è continuo e non causa improvvisi aumenti nelle operazioni di I/O. Tuttavia, se il working set delle tabelle critiche per le prestazioni non rientra nella memoria, In-Memory OLTP non si applica perché richiede che i dati siano residenti nella memoria. Se si verifica un collo di bottiglia a livello di I/O durante la registrazione, OLTP in memoria può ridurre il collo di bottiglia perché richiede meno attività di registrazione. Se una o più tabelle ottimizzate per la memoria sono configurate come tabelle non durevoli, è possibile eliminare la registrazione dei dati. Memoria In-Memory OLTP non offre alcun vantaggio sulle prestazioni. È possibile che OLTP in memoria comporti un utilizzo elevato di memoria, in quando gli oggetti devono essere residenti in memoria. Rete In-Memory OLTP non offre alcun vantaggio sulle prestazioni. I dati devo essere comunicati dal livello dati al livello applicazione.
Scalabilità La maggior parte dei problemi di scalabilità nelle applicazioni di SQL Server è causata da problemi di concorrenza, ad esempio la contesa in blocchi, latch e spinlock.	Contesa di latch Uno scenario tipico riguarda la contesa nell'ultima pagina di un indice quando vengono inserite contemporaneamente righe nell'ordine delle chiavi. Poiché In-Memory OLTP non accetta latch durante l'accesso ai dati, i problemi di scalabilità correlati ai conflitti di latch vengono completamente rimossi. Contesa di spinlock Poiché In-Memory OLTP non utilizza latch durante l'accesso ai dati, i problemi di scalabilità legati ai conflitti di spinlock sono completamente eliminati. Contesa correlata al blocco Se nell'applicazione di database vengono rilevati problemi di blocco tra operazioni di lettura e scrittura, con OLTP in memoria vengono rimossi i problemi che impediscono di proseguire perché è previsto l'utilizzo di una nuova forma di controllo della concorrenza ottimistica per implementare tutti i livelli di isolamento delle transazioni. In-Memory OLTP non usa TempDB per archiviare le versioni di riga. Se il problema di scalabilità è determinato dal conflitto tra due operazioni di scrittura, ad esempio due transazioni simultanee che tentano di aggiornare la stessa riga, OLTP in memoria consente il completamento di una transazione e genera un errore per l'altra. La transazione non riuscita deve essere rieseguita in modo esplicito o implicito, ritentando la transazione. In entrambi i casi è necessario apportare modifiche all'applicazione. Se nell'applicazione si verificano frequenti conflitti tra due operazioni di scrittura, il valore del blocco ottimistico viene ridotto. L'applicazione non è adatta per In-Memory OLTP. La maggior parte delle applicazioni OLTP non presenta conflitti di scrittura, a meno che il conflitto non sia il risultato dell'escalation dei blocchi.

Problema

Impatto di OLTP in memoria

Prestazioni

Utilizzo elevato delle risorse (CPU, I/O, rete o memoria).

CPU (unità centrale di elaborazione)
Le stored procedure compilate in modo nativo possono ridurre significativamente l'utilizzo della CPU perché richiedono meno istruzioni per eseguire un'istruzione Transact-SQL rispetto alle stored procedure interpretate.

In-Memory OLTP può contribuire a ridurre l'investimento hardware nei carichi di lavoro con scalabilità orizzontale perché un server può potenzialmente offrire la velocità effettiva di più server.

Input/Output (Ingresso/Uscita)
Se si verifica un collo di bottiglia a livello di I/O in seguito all'elaborazione di pagine di dati o di indice, in OLTP in memoria il collo di bottiglia potrebbe risultare contenuto. Inoltre, il processo di checkpointing degli oggetti OLTP In-Memory è continuo e non causa improvvisi aumenti nelle operazioni di I/O. Tuttavia, se il working set delle tabelle critiche per le prestazioni non rientra nella memoria, In-Memory OLTP non si applica perché richiede che i dati siano residenti nella memoria. Se si verifica un collo di bottiglia a livello di I/O durante la registrazione, OLTP in memoria può ridurre il collo di bottiglia perché richiede meno attività di registrazione. Se una o più tabelle ottimizzate per la memoria sono configurate come tabelle non durevoli, è possibile eliminare la registrazione dei dati.

Memoria
In-Memory OLTP non offre alcun vantaggio sulle prestazioni. È possibile che OLTP in memoria comporti un utilizzo elevato di memoria, in quando gli oggetti devono essere residenti in memoria.

Rete
In-Memory OLTP non offre alcun vantaggio sulle prestazioni. I dati devo essere comunicati dal livello dati al livello applicazione.

Scalabilità

La maggior parte dei problemi di scalabilità nelle applicazioni di SQL Server è causata da problemi di concorrenza, ad esempio la contesa in blocchi, latch e spinlock.

Contesa di latch
Uno scenario tipico riguarda la contesa nell'ultima pagina di un indice quando vengono inserite contemporaneamente righe nell'ordine delle chiavi. Poiché In-Memory OLTP non accetta latch durante l'accesso ai dati, i problemi di scalabilità correlati ai conflitti di latch vengono completamente rimossi.

Contesa di spinlock
Poiché In-Memory OLTP non utilizza latch durante l'accesso ai dati, i problemi di scalabilità legati ai conflitti di spinlock sono completamente eliminati.

Contesa correlata al blocco
Se nell'applicazione di database vengono rilevati problemi di blocco tra operazioni di lettura e scrittura, con OLTP in memoria vengono rimossi i problemi che impediscono di proseguire perché è previsto l'utilizzo di una nuova forma di controllo della concorrenza ottimistica per implementare tutti i livelli di isolamento delle transazioni. In-Memory OLTP non usa TempDB per archiviare le versioni di riga.

Se il problema di scalabilità è determinato dal conflitto tra due operazioni di scrittura, ad esempio due transazioni simultanee che tentano di aggiornare la stessa riga, OLTP in memoria consente il completamento di una transazione e genera un errore per l'altra. La transazione non riuscita deve essere rieseguita in modo esplicito o implicito, ritentando la transazione. In entrambi i casi è necessario apportare modifiche all'applicazione.

Se nell'applicazione si verificano frequenti conflitti tra due operazioni di scrittura, il valore del blocco ottimistico viene ridotto. L'applicazione non è adatta per In-Memory OLTP. La maggior parte delle applicazioni OLTP non presenta conflitti di scrittura, a meno che il conflitto non sia il risultato dell'escalation dei blocchi.

Sicurezza a livello di riga nelle tabelle con ottimizzazione per la memoria

Row-Level La sicurezza è supportata nelle tabelle ottimizzate per la memoria. L'applicazione dei criteri di sicurezza a livello di riga alle tabelle ottimizzate per la memoria corrisponde essenzialmente alle tabelle basate su disco, ad eccezione del fatto che le funzioni con valori di tabella inline usate come predicati di sicurezza devono essere compilate in modo nativo, ovvero create con l'opzione WITH NATIVE_COMPILATION. Per informazioni dettagliate, vedere la sezione Compatibilità tra funzionalità nell'argomento sicurezza diRow-Level .

Sono disponibili varie funzioni di sicurezza predefinite essenziali per la sicurezza a livello di riga per le tabelle ottimizzate per la memoria. Per informazioni dettagliate, vedere Funzioni predefinite nei moduli compilati in modo nativo.

EXECUTE AS CALLER : tutti i moduli nativi ora supportano e usano EXECUTE AS CALLER per impostazione predefinita, anche se l'hint non è specificato. Ciò è dovuto al fatto che tutte le funzioni del predicato di sicurezza a livello di riga usano EXECUTE AS CALLER in modo che la funzione e tutte le funzioni predefinite usate al suo interno vengano valutate nel contesto dell'utente chiamante.
EXECUTE AS CALLER influisce in proporzione modesta (circa il 10%) sulle prestazioni a causa dei controlli delle autorizzazioni sul chiamante. Se il modulo specifica EXECUTE AS OWNER o EXECUTE AS SELF in modo esplicito, questi controlli delle autorizzazioni e i relativi costi di prestazioni associati vengono evitati. Tuttavia, l'uso di una di queste opzioni insieme alle funzioni predefinite menzionate comporta un miglioramento delle prestazioni a causa del cambio di contesto necessario.

Scenari

Per una breve descrizione degli scenari tipici in cui In-Memory OLTP può migliorare le prestazioni, vedere In-Memory OLTP.

Vedi anche

In-Memory OLTP ( OttimizzazioneIn-Memory)

Condividi tramite