Estensione del pool di buffer

Introdotta in SQL Server 2014, l'estensione del pool di buffer offre la perfetta integrazione di un'estensione di memoria ad accesso casuale non volatile (ovvero unità ssd) al pool di buffer del motore di database per migliorare significativamente la velocità effettiva di I/O. L'estensione del pool di buffer non è disponibile in ogni edizione di SQL Server. Per ulteriori informazioni, vedere Funzionalità supportate dalle edizioni di SQL Server 2014.

Vantaggi dell'estensione del pool di buffer

Lo scopo principale di un database di SQL Server è l'archiviazione e il recupero dei dati. L'esecuzione di una quantità elevata di operazioni di I/O su disco è pertanto una caratteristica fondamentale del motore di database. Poiché le operazioni di I/O nel disco possono usare molte risorse e richiedere un tempo relativamente lungo per il completamento, in SQL Server viene data grande importanza all'efficienza dell'I/O. Il pool di buffer funge da fonte primaria di allocazione della memoria di SQL Server. La gestione del buffer è un elemento chiave per il raggiungimento di tale efficienza. Il componente di gestione del buffer è costituito da due meccanismi: gestione buffer per accedere e aggiornare le pagine del database e il pool di buffer, per ridurre le operazioni di I/O dei file di database.

Le pagine di indice e di dati vengono lette dal disco nel pool di buffer e le pagine modificate, dette anche pagine dirty, vengono riscritte sul disco. L'utilizzo della memoria nei checkpoint di database e del server determinano la rimozione dalla cache del buffer delle pagine dirty ad accesso frequente (attive) e la scrittura su dischi di tipo meccanico, quindi la rilettura delle stesse nella cache. Queste operazioni di I/O sono in genere letture casuali di piccole dimensioni e scritture nell'ordine da 4 a 16 KB di dati. Piccoli modelli di I/O casuali generano frequenti ricerche, per contendersi il braccio meccanico del disco, aumentare la latenza dell'attività di I/O e ridurre la velocità effettiva di I/O aggregata del sistema.

L'approccio tipico per risolvere questi colli bottiglia nelle operazioni di I/O consiste nell'aggiungere una maggiore quantità di DRAM o, in alternativa, più spindle SAS a prestazioni elevate. Sebbene queste opzioni siano utili, presentano svantaggi significativi: DRAM è più costoso rispetto alle unità di archiviazione dati e l'aggiunta di spindle aumenta la spesa in conto capitale nell'acquisizione hardware e aumenta i costi operativi aumentando il consumo di energia e aumentando la probabilità di errore del componente.

La funzionalità di estensione del pool di buffer estende la cache di pool di buffer con RAM non volatile (di solito unità SSD). Grazie a questa estensione, il pool di buffer può supportare un working set del database più esteso, forzando il paging delle attività di I/O tra la RAM e le unità SSD. In questo modo viene effettuato in modo efficiente l'offload delle piccole operazioni di I/O casuali dai dischi meccanici alle unità SSD. Grazie alla latenza più bassa e al miglioramento delle prestazioni di I/O casuali delle unità SSD, l'estensione del pool di buffer consente di migliorare notevolmente la velocità effettiva di I/O.

Nell'elenco seguente vengono descritti i vantaggi della funzionalità di estensione del pool di buffer.

• Maggiore velocità effettiva di I/O casuale

• Riduzione della latenza di I/O

• Maggiore velocità effettiva delle transazioni

• Miglioramento delle prestazioni di lettura con un più ampio pool di buffer ibrido

• Architettura di memorizzazione nella cache per l'utilizzo delle unità di memoria attuali e future

Concetti

I seguenti termini sono applicabili alla funzionalità di estensione del pool di buffer.

Le unità SSD (Solid State Drive) archiviano i dati in memoria (RAM) in modo permanente. Per altre informazioni, vedere questa definizione.

Buffer in SQL Server, un buffer è una pagina di 8 KB in memoria, la stessa dimensione di una pagina di dati o indice. La cache del buffer è quindi suddivisa in pagine da 8 KB. Una pagina rimane nella cache del buffer fino a quando per Gestione buffer non è necessaria l'area del buffer per leggere un maggior numero di dati. I dati vengono riscritti su disco solo se vengono modificati. Queste pagine modificate in memoria sono dette pagine dirty. Una pagina è pulita quando equivale all'immagine del database su disco. I dati nella cache del buffer possono essere modificati più volte prima di venire riscritti sul disco.

Pool di buffer denominato anche cache del buffer. Il pool di buffer è una risorsa globale condivisa da tutti i database per le pagine di dati memorizzate nella cache. La dimensione massima e minima della cache del pool di buffer viene determinata durante l'avvio o quando l'istanza di SQL Server viene riconfigurata dinamicamente tramite sp_configure. Tali dimensioni determinano il numero massimo di pagine che possono essere memorizzate nella cache del pool di buffer dell'istanza in esecuzione in un momento qualsiasi.

Checkpoint Un checkpoint crea un punto valido noto da cui il motore di database può iniziare ad applicare le modifiche contenute nel log delle transazioni durante il ripristino dopo un arresto imprevisto o un arresto anomalo. Un checkpoint scrive le pagine dirty e le informazioni sul log delle transazioni dalla memoria al disco e registra inoltre le informazioni sul log delle transazioni. Per altre informazioni, vedere Checkpoint di database (SQL Server).

Dettagli dell'estensione del buffer pool

L'archiviazione sull'unità SSD viene utilizzata come estensione del sottosistema di memoria anziché del sottosistema di archiviazione su disco, ovvero il file di estensione del pool di buffer consente alla funzionalità di gestione del pool di buffer di utilizzare sia la memoria DRAM che la memoria flash NAND, per gestire un pool di buffer molto più grande di pagine ad accesso meno frequente nella NvRAM (Non-volatile Random Access Memory) supportata da unità SSD. In questo modo viene creata una gerarchia multilivello di memorizzazione nella cache con livello 1 (L1) come DRAM e livello 2 (L2) come file di estensione del pool di buffer sull'unità SSD. Solo le pagine clean vengono scritte nella cache L2 che consente di gestire la sicurezza dei dati. In Gestione buffer viene gestito lo spostamento delle pagine clean tra le cache L1 e L2.

Nella figura seguente viene fornita una panoramica di alto livello dell'architettura del pool di buffer in relazione agli altri componenti di SQL Server.

Quando è abilitata, l'estensione del pool di buffer specifica le dimensioni e il percorso del file di memorizzazione nella cache del pool di buffer sull'unità SSD. Il file è un extent contiguo di archiviazione sull'unità SSD e viene configurato in modo statico durante l'avvio dell'istanza di SQL Server. Le modifiche ai parametri di configurazione del file possono essere eseguite solo quando la funzionalità di estensione del pool di buffer è disabilitata. In tal caso, tutte le impostazioni di configurazione correlate vengono rimosse dal Registro di sistema. Il file di estensione del pool di buffer viene eliminato durante l'arresto dell'istanza di SQL Server.

Migliori pratiche

È consigliabile attenersi a queste procedure consigliate.

• Dopo aver abilitato l'estensione del pool di buffer per la prima volta, è consigliabile riavviare l'istanza di SQL Server per ottenere i massimi vantaggi in termini di prestazioni.

• Le dimensioni dell'estensione del pool di buffer possono essere fino a 32 volte il valore di max_server_memory per le edizioni Enterprise e fino a 4 volte per l'edizione Standard. È consigliabile un rapporto tra le dimensioni della memoria fisica (max_server_memory) e le dimensioni dell'estensione del pool di buffer pari o inferiore a 1:16. Un rapporto inferiore nell'intervallo compreso tra 1:4 e 1:8 può essere ottimale. Per informazioni sull'impostazione dell'opzione max_server_memory, vedere Opzioni di configurazione della memoria del server.

• Effettuare test approfonditi dell'estensione del pool di buffer prima dell'implementazione in un ambiente di produzione. Una volta in produzione, evitare di apportare modifiche alla configurazione al file o disattivare la funzionalità. Queste attività possono avere un impatto negativo sulle prestazioni del server perché le dimensioni del pool di buffer sono notevolmente ridotte quando la funzionalità è disabilitata. Se disabilitata, la memoria usata per supportare la funzionalità non viene recuperata fino al riavvio dell'istanza di SQL Server. Tuttavia, se la funzionalità è riabilitata, la memoria verrà riutilizzata senza riavviare l'istanza.

Restituisce informazioni sull'estensione del pool di buffer

È possibile utilizzare le viste a gestione dinamica (DMV) seguenti per visualizzare la configurazione dell'estensione del pool di buffer e le informazioni sulle pagine di dati restituite nell'estensione.

• sys.dm_os_buffer_pool_extension_configuration (Transact-SQL)

• sys.dm_os_buffer_descriptors (Transact-SQL)

I contatori delle prestazioni sono disponibili nell'oggetto di Gestione buffer di SQL Server per tenere traccia delle pagine di dati nel file di estensione del pool di buffer. Per ulteriori informazioni, vedere l'argomento relativo ai contatori delle prestazioni dell'estensione del pool di buffer.

Sono disponibili i seguenti eventi Xevent.

XEvent	Descrizione	Parametri
sqlserver.buffer_pool_extension_pages_written	Viene attivato quando una pagina o un gruppo di pagine vengono eliminate dal pool di buffer e vengono scritte nel file di estensione del pool di buffer.	numero_di_pagina first_page_id scostamento_prima_pagina initiator_numa_node_id
sqlserver.buffer_pool_extension_pages_read	Viene attivato quando una pagina viene letta dal file di estensione del pool di buffer nel pool di buffer.	numero_pagina first_page_id offset_prima_pagina initiator_numa_node_id
sqlserver.buffer_pool_extension_pages_evicted	Viene attivato quando una pagina viene rimossa dal file di estensione del pool di buffer.	numero_pagina id_prima_pagina offset_prima_pagina initiator_numa_node_id
sqlserver.buffer_pool_eviction_thresholds_recalculated	Viene attivato quando viene calcolata la soglia di eliminazione.	soglia di calore soglia_di_freddo pagine_con_evizione_ignorata motivo_deroga_sfratto descrizione_motivo_deroga_sfratto

Attività correlate

Descrizione attività	argomento
Abilitare e configurare l'estensione del pool di buffer.	ALTER SERVER CONFIGURATION (Transact-SQL)
Modificare la configurazione dell'estensione del pool di buffer.	ALTER SERVER CONFIGURATION (Transact-SQL)
Visualizzare la configurazione dell'estensione del pool di buffer.	sys.dm_os_buffer_pool_extension_configuration (Transact-SQL)
Eseguire il monitoraggio dell'estensione del pool di buffer.	sys.dm_os_buffer_descriptors (Transact-SQL) Contatori delle prestazioni