Stimare i requisiti di memoria delle tabelle con ottimizzazione per la memoria

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

Le tabelle con ottimizzazione per la memoria richiedono memoria sufficiente per mantenere tutte le righe e tutti gli indici in memoria. Poiché la memoria è una risorsa limitata, è importante conoscere e gestire l'utilizzo di memoria nel sistema. Negli argomenti di questa sezione vengono illustrati gli scenari comuni di utilizzo e gestione della memoria.

È importante avere una stima ragionevole delle esigenze di memoria di ogni tabella ottimizzata per la memoria in modo da poter effettuare il provisioning del server con memoria sufficiente. Questo vale sia per le nuove tabelle che per le tabelle di cui è stata eseguita la migrazione da tabelle basate su disco. In questa sezione viene descritto come stimare la quantità di memoria necessaria per contenere i dati di una tabella ottimizzata per la memoria.

Se si sta valutando una migrazione da tabelle basate su disco a tabelle ottimizzate per la memoria, vedere Determinare se una tabella o una stored procedure deve essere convertita in In-Memory OLTP per indicazioni sulle tabelle di cui eseguire la migrazione. Tutti gli argomenti disponibili in Migrazione a OLTP in memoria offrono informazioni aggiuntive sulla migrazione da tabelle basate su disco a tabelle ottimizzate per la memoria.

Guida di base per la stima dei requisiti di memoria

In SQL Server 2016 (13.x) e versioni successive non esiste alcun limite per le dimensioni delle tabelle ottimizzate per la memoria, anche se le tabelle devono adattarsi alla memoria. In SQL Server 2014 (12.x), le dimensioni dei dati supportate sono pari a 256 GB per le tabelle SCHEMA_AND_DATA.

Le dimensioni di una tabella ottimizzata per la memoria corrispondono alle dimensioni dei dati più l'overhead per le intestazioni di riga. Le dimensioni della tabella ottimizzata per la memoria corrispondono approssimativamente alle dimensioni dell'indice cluster o dell'heap della tabella originale basata su disco.

Gli indici delle tabelle ottimizzate per la memoria tendono a essere più piccoli rispetto agli indici non cluster nelle tabelle basate su disco. Le dimensioni degli indici non clusterizzati sono nell'ordine di [primary key size] * [row count]. Le dimensioni degli indici hash corrispondono a [bucket count] * 8 bytes.

Quando è presente un carico di lavoro attivo, è necessaria altra memoria per tenere conto del controllo delle versioni delle righe e di diverse operazioni. La quantità di memoria necessaria dipende dal carico di lavoro, ma per essere sicuri, è consigliabile iniziare con due volte le dimensioni previste di tabelle e indici ottimizzati per la memoria e osservare l'utilizzo effettivo della memoria. L'overhead per il controllo delle versioni delle righe dipende sempre dalle caratteristiche del carico di lavoro - soprattutto le transazioni con esecuzione prolungata incrementano l'overhead. Per la maggior parte dei carichi di lavoro che usano database di dimensioni maggiori (ad esempio, maggiori di 100 GB), il sovraccarico tende a essere limitato (25% o minore).

Per altre informazioni sul potenziale sovraccarico di memoria nel motore OLTP In-Memory, vedere Frammentazione della memoria.

Calcolo dettagliato dei requisiti di memoria

• Esempio di tabella ottimizzata per la memoria

• Memoria per la tabella

• Memoria per gli indici

• Memoria per il controllo delle versioni delle righe

• Memoria per le variabili di tabella

• Memoria per la crescita

Esempio di tabella ottimizzata per la memoria

Si consideri il seguente schema di tabella ottimizzata per la memoria:

CREATE TABLE t_hk
(  
  col1 int NOT NULL  PRIMARY KEY NONCLUSTERED,  

  col2 int NOT NULL  INDEX t1c2_index   
      HASH WITH (bucket_count = 5000000),  

  col3 int NOT NULL  INDEX t1c3_index   
      HASH WITH (bucket_count = 5000000),  

  col4 int NOT NULL  INDEX t1c4_index   
      HASH WITH (bucket_count = 5000000),  

  col5 int NOT NULL  INDEX t1c5_index NONCLUSTERED,  

  col6 char (50) NOT NULL,  
  col7 char (50) NOT NULL,   
  col8 char (30) NOT NULL,   
  col9 char (50) NOT NULL  

)   WITH (memory_optimized = on)  ;
GO  

Usando questo schema, si determinerà la memoria minima necessaria per questa tabella ottimizzata per la memoria.

Memoria per la tabella

Una riga di tabella ottimizzata per la memoria ha tre parti:

• Timestamp
  Intestazione di riga/marchi temporali = 24 byte.

• Puntatori dell'indice
  Per ogni indice hash nella tabella, a ogni riga è associato un puntatore all'indirizzo di 8 byte alla riga successiva nell'indice. Poiché sono presenti quattro indici, ogni riga alloca 32 byte per i puntatori all'indice (un puntatore a 8 byte per ogni indice).

• Dati
  Le dimensioni della parte di dati della riga vengono determinate sommando le dimensioni di tipo per ogni colonna di dati. Nella nostra tabella abbiamo cinque interi a 4 byte, tre colonne di caratteri di 50 byte e una colonna di caratteri di 30 byte. Pertanto la parte di dati di ogni riga è 4 + 4 + 4 + 4 + 4 + 50 + 50 + 30 + 50, vale a dire 200 byte.

Di seguito è riportato un calcolo di dimensioni per 5.000.000 (5 milioni) di righe in una tabella ottimizzata per la memoria. La memoria totale utilizzata dalle righe di dati viene stimata come segue:

Memoria per le righe della tabella

Dai calcoli sopra riportati, le dimensioni di ogni riga della tabella ottimizzata per la memoria sono pari a 24 + 32 + 200, vale a dire 256 byte. Poiché sono presenti 5 milioni di righe, la tabella utilizza 5.000.000 * 256 byte o 1.280.000.000 byte circa 1,28 GB.

Memoria per gli indici

Memoria per ogni indice hash

Ogni indice hash è una matrice hash di puntatori di indirizzo a 8 byte. Le dimensioni della matrice sono determinate in modo ottimale dal numero di valori di indice univoci per tale indice. Nell'esempio corrente il numero di valori Col2 univoci è un buon punto di partenza per le dimensioni della matrice per il t1c2_index. Una matrice di hash eccessiva comporta uno spreco di memoria. Una matrice hash troppo piccola rallenta le prestazioni a causa di troppi conflitti derivanti dai valori di indice che vengono mappati alla stessa voce di indice.

Tramite gli indici hash è possibile ottenere ricerche di uguaglianza estremamente veloci, ad esempio:

SELECT * FROM t_hk  
   WHERE Col2 = 3;

Gli indici non cluster sono più veloci per le ricerche in intervalli, ad esempio:

SELECT * FROM t_hk  
   WHERE Col2 >= 3;

Se si esegue la migrazione di una tabella basata su disco, è possibile utilizzare quando riportato di seguito per determinare il numero di valori univoci per l'indice t1c2_index.

SELECT COUNT(DISTINCT [Col2])  
  FROM t_hk;

Se si sta creando una nuova tabella, è necessario stimare le dimensioni della matrice o raccogliere dati dai test prima della distribuzione.

Per informazioni sul funzionamento degli indici hash in tabelle ottimizzate per la memoria di OLTP in memoria, vedi Indici hash.

Impostazione delle dimensioni della matrice dell'indice hash

Le dimensioni della matrice di hash vengono impostate tramite (bucket_count= value) dove value è un intero maggiore di zero. Se value non è una potenza di 2, il bucket count effettivo viene arrotondato alla potenza di 2 successiva. Nella tabella di esempio, (bucket_count = 5000000), poiché 5.000.000 non è una potenza di 2, il numero effettivo di bucket viene arrotondato per eccesso a 8.388.608 (2^23). È necessario utilizzare questo numero, non 5.000.000, quando si calcola la memoria necessaria per la matrice di hash.

Pertanto, nell'esempio, la memoria necessaria per ogni matrice di hash è:

8.388.608 * 8 = 2^23 * 8 = 2^23 * 2^3 = 2^26 = 67.108.864 o circa 64 MB.

Poiché vi sono tre indici hash, la memoria necessaria per gli indici hash è 3 * 64 MB = 192 MB.

Memoria per gli indici non cluster

Gli indici non cluster vengono implementati come alberi Bw con i nodi interni contenenti il valore di indice e i puntatori ai nodi successivi. Nei nodi foglia sono contenuti il valore dell'indice e un puntatore alla riga della tabella in memoria.

A differenza degli indici hash, gli indici non cluster non hanno dimensioni fisse per il bucket. Le dimensioni dell'indice aumentano e si riducono dinamicamente in base ai dati.

La memoria necessaria per gli indici non cluster può essere calcolata come indicato di seguito:

• Memoria allocata a nodi non foglie
  Per una configurazione tipica, la memoria allocata ai nodi non terminali è una piccola percentuale della memoria complessiva utilizzata dall'indice. È così piccolo che può essere ignorato senza problemi.

• Memoria per i nodi foglia
  I nodi foglia hanno una riga per ciascuna chiave univoca della tabella, che corrisponde alle righe di dati con quella chiave univoca. Se sono presenti più righe con la stessa chiave, ovvero un indice non univoco non clusterizzato, nel nodo foglia dell'indice è presente una sola riga che punta a una delle righe, mentre le altre sono collegate tra loro. Pertanto, la memoria totale necessaria può essere approssimata nel modo seguente:

  • memoryForNonClusteredIndex = (pointerSize + sum(keyColumnDataTypeSizes)) * rowsWithUniqueKeys

Gli indici non cluster rappresentano la soluzione migliore in caso di ricerche in intervalli, come esemplificato dalla query seguente:

SELECT * FROM t_hk  
   WHERE c2 > 5;  

Memoria per il controllo delle versioni delle righe

Per evitare i blocchi, OLTP in memoria utilizza la concorrenza ottimistica durante l'aggiornamento o l'eliminazione di righe. Pertanto, quando una riga viene aggiornata, viene creata un’altra versione della riga. Inoltre, le eliminazioni sono logiche: la riga esistente viene contrassegnata come eliminata, ma non viene rimossa immediatamente. Il sistema mantiene disponibili versioni di riga precedenti (incluse le righe eliminate) fino a quando tutte le transazioni che potrebbero usare la versione completano l'esecuzione.

Poiché potrebbero esserci molte più righe in memoria in qualsiasi momento, in attesa del ciclo di raccolta dei rifiuti per rilasciare la memoria, è necessario disporre di memoria sufficiente per contenere queste altre righe.

Il numero di righe aggiuntive può essere stimato calcolando il numero massimo di aggiornamenti ed eliminazioni di righe al secondo, quindi moltiplicando il risultato per il numero di secondi impiegati dalla transazione più lunga (almeno 1).

Il valore viene quindi moltiplicato per le dimensioni della riga per ottenere il numero di byte necessari per il controllo delle versioni delle righe.

rowVersions = durationOfLongestTransactionInSeconds * peakNumberOfRowUpdatesOrDeletesPerSecond

Le esigenze di memoria per le righe non aggiornate vengono quindi stimate moltiplicando il numero di righe non aggiornate in base alle dimensioni di una riga di tabella ottimizzata per la memoria. Per altre informazioni, vedere Memoria per la tabella.

memoryForRowVersions = rowVersions * rowSize

Memoria per le variabili di tabella

La memoria utilizzata per una variabile di tabella viene rilasciata solo quando la variabile di tabella abbandona l'ambito. Le righe eliminate, incluse quelle eliminate come parte di un aggiornamento, da una variabile di tabella non vengono sottoposte a Garbage Collection. Finché la variabile di tabella non abbandona l'ambito, la memoria non viene rilasciata.

Le variabili di tabella definite in un batch SQL di grandi dimensioni anziché in una stored procedure e usate in molte transazioni possono utilizzare una grande quantità di memoria. Poiché non vengono sottoposte a Garbage Collection, le righe eliminate in una variabile di tabella possono consumare molta memoria e degradare le prestazioni, poiché le operazioni di lettura devono scansionare oltre le righe eliminate.

Memoria per la crescita

I calcoli precedenti stimano le necessità di memoria per la tabella nella sua attuale configurazione. Oltre a questa memoria, è necessario stimare la crescita della tabella e fornire una memoria sufficiente per gestire questa crescita. Ad esempio, se si prevede una crescita del 10%, è necessario moltiplicare i risultati precedenti per 1,1 per ottenere la quantità totale di memoria necessaria per la tua tabella.

Frammentazione della memoria

Per evitare il sovraccarico delle chiamate di allocazione della memoria e migliorare le prestazioni, il motore OLTP In-Memory richiede sempre memoria dal sistema operativo SQL Server (SQLOS) usando blocchi da 64 KB, detti superblock.

Ogni superblock contiene allocazioni di memoria solo all'interno di un intervallo di dimensioni specifico, detto sizeclass. Ad esempio, il superblock A potrebbe avere allocazioni di memoria nella classe size da 1 a 16 byte, mentre il superblock B potrebbe avere allocazioni di memoria nella classe size da 17 a 32 byte e così via.

Per impostazione predefinita, anche i superblock vengono partizionati dalla CPU logica. Ciò significa che per ogni CPU logica è presente un set separato di superblock, ulteriormente suddiviso per sizeclass. In questo modo si riduce la contesa di allocazione di memoria tra le richieste in esecuzione su CPU diverse.

Quando il motore OLTP In-Memory effettua una nuova allocazione di memoria, tenta innanzitutto di trovare memoria libera in un superblock esistente per la classe size richiesta e per l'elaborazione della richiesta. Se questo tentativo ha esito positivo, il valore nella used_bytes colonna in sys.dm_xtp_system_memory_consumers per un consumer di memoria specifico aumenta delle dimensioni della memoria richieste, ma il valore nella allocated_bytes colonna rimane invariato.

Se non è presente memoria libera nei superblock esistenti, viene allocato un nuovo superblock e il valore aumenta used_bytes in base alle dimensioni della memoria richieste, mentre il valore nella allocated_bytes colonna aumenta di 64 KB.

Nel corso del tempo, poiché la memoria nei superblock viene allocata e deallocata, la quantità totale di memoria utilizzata dal motore OLTP In-Memory potrebbe diventare significativamente maggiore della quantità di memoria usata. In altre parole, la memoria può diventare frammentata.

Garbage Collection potrebbe ridurre la memoria usata, ma riduce solo la memoria allocata se uno o più superblock diventano vuoti e vengono deallocati. Questo vale sia per il garbage collection automatico che per quello forzato, utilizzando la stored procedure di sistema sys.sp_xtp_force_gc.

Se la frammentazione della memoria del motore OLTP In-Memory e l'utilizzo della memoria allocata diventano superiori al previsto, è possibile abilitare il flag di traccia 9898. Questo modifica lo schema di partizionamento superblock da per CPU a nodo NUMA, riducendo il numero totale di superblock e il potenziale di frammentazione elevata della memoria.

Questa ottimizzazione è più rilevante per i computer di grandi dimensioni con molte CPU logiche. Il compromesso di questa ottimizzazione è un potenziale aumento della contesa di allocazione della memoria risultante da un minor numero di superblock, che potrebbe ridurre la velocità effettiva complessiva del carico di lavoro. A seconda dei modelli di carico di lavoro, la riduzione della velocità effettiva rispetto all'uso del partizionamento della memoria per NUMA può essere evidente o meno.

Contenuti correlati

• Migrazione a OLTP in memoria