Condividi tramite

Guida all'architettura di gestione della memoria

  • Articolo

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Piattaforma di strumenti analitici (PDW)

Virtual Memory Manager di Windows

Viene eseguito il mapping delle aree di cui è stato eseguito il commit dello spazio degli indirizzi alla memoria fisica disponibile tramite Virtual Memory Manager (VMM) di Windows.

Per altre informazioni sulla quantità di memoria fisica supportata dai diversi sistemi operativi, vedere la documentazione di Windows relativa ai limiti di memoria per le versioni di Windows.

I sistemi con memoria virtuale consentono il commit in eccesso della memoria fisica, per cui il rapporto tra memoria virtuale e memoria fisica può essere maggiore di 1:1. Di conseguenza, i computer con diverse configurazioni di memoria fisica consentono l'esecuzione di programmi di dimensioni elevate. Tuttavia l'utilizzo di una quantità di memoria virtuale di molto superiore alla combinazione dei set di lavoro medi per tutti i processi determina un peggioramento delle prestazioni.

Architettura della memoria di SQL Server

In SQL Server la memoria viene acquisita e liberata in modo dinamico in base alle esigenze. In genere non è necessario che un amministratore specifichi la quantità di memoria da allocare a SQL Server. L'opzione corrispondente, tuttavia, è ancora disponibile e in alcuni ambienti è necessario impostarla.

Uno dei principali obiettivi di progettazione di tutti i software di database è la riduzione del disco I/O dal momento che le letture e le scritture del disco sono le operazioni che consumano più risorse. SQL Server crea un pool di buffer in memoria per contenere le pagine lette dal database. Gran parte del codice in SQL Server è dedicata alla riduzione del numero di letture e scritture fisiche tra il disco e il pool di buffer. SQL Server tenta di raggiungere un equilibrio tra i due obiettivi:

  • Evitare che le dimensioni del pool di buffer aumentino fino a limitare la memoria dell'intero sistema.
  • Ridurre al minimo l'I/O fisico sui file di database aumentando la dimensione del pool di buffer fino a raggiungere il valore massimo possibile.

In un sistema con carichi pesanti, alcune query di grandi dimensioni la cui esecuzione richiede una grande quantità di memoria non possono ottenere la quantità minima di memoria richiesta e ricevono un errore di timeout mentre sono in attesa delle risorse della memoria. Per risolvere il problema, aumentare il valore dell' opzione query wait. Per una query parallela, provare a ridurre l' opzione Massimo grado di parallelismo.

In un sistema con carichi elevati e con un numero eccessivo di richieste di memoria, le query con merge join, sort e bitmap nel piano di query possono eliminare il bitmap quando non ottengono la memoria minima necessaria per il bitmap. Ciò può influire sulle prestazioni delle query e, se il processo di ordinamento non può essere contenuto nella memoria, è possibile aumentare l'uso delle tabelle di lavoro nel database tempdb, causando la crescita di tempdb. Per risolvere questo problema, aggiungere memoria fisica o ottimizzare le query per l'uso di un piano di query diverso e più rapido.

Memoria convenzionale (virtuale)

Tutte le edizioni di SQL Server supportano la memoria convenzionale sulla piattaforma a 64 bit. Il processo di SQL Server può accedere allo spazio degli indirizzi virtuali fino al massimo del sistema operativo nell'architettura x64 (SQL Server Standard Edition supporta fino a 128 GB). Con l'architettura IA64, il limite era di 7 TB (IA64 non supportato in SQL Server 2012 (11.x) e versioni successive). Per altre informazioni, vedere Limiti di memoria per Windows .

Memoria AWE (Address Windows Extensions)

Usando AWE (Address Windowing Extensions) e i privilegi di blocco delle pagine in memoria (LPIM) richiesti da AWE, è possibile mantenere la maggior parte della memoria del processo di SQL Server bloccata nella RAM fisica in condizioni di memoria virtuale insufficiente. Ciò si verifica sia nelle allocazioni AWE a 32 bit che a 64 bit. Il blocco della memoria si verifica perché la memoria AWE non passa attraverso Virtual Memory Manager in Windows, che controlla il paging della memoria. L'API di allocazione della memoria AWE richiede il privilegio di blocco di pagine in memoria (SeLockMemoryPrivilege). Vedere le note su AllocateUserPhysicalPages. Pertanto, il vantaggio principale dell'uso dell'API AWE è quello di mantenere la maggior parte della memoria residente nella RAM se si verifica una pressione di memoria sul sistema. Per informazioni su come consentire a SQL Server di usare AWE, vedere Abilitare l'opzione Blocca pagine in memoria (Windows).

Se viene concesso il privilegio LPIM (Blocco di pagine in memoria), è consigliabile impostare max server memory (MB) su un valore specifico, invece di lasciare l'impostazione predefinita di 2.147.483.647 megabyte (MB). Per altre informazioni, vedere Configurazione del server Server memory: impostare le opzioni manualmente e Bloccare le pagine in memoria (LPIM)

Se LPIM non è abilitato, SQL Server passerà all'uso della memoria convenzionale e in caso di esaurimento della memoria del sistema operativo, è possibile che nel log degli errori venga segnalato l'errore [MSSQLSERVER_17890] (errors-events/mssqlserver-17890-database-engine-error.md). Nell'esempio seguente l'errore viene risolto:

A significant part of SQL Server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%.

Modifiche alla gestione della memoria a partire da SQL Server 2012

Nelle versioni precedenti di SQL Server, l'allocazione di memoria veniva gestita con cinque meccanismi diversi:

  • Allocatore di pagine singole (SPA), che include solo le allocazioni di memoria minori o uguali a 8 KB nel processo di SQL Server. Le opzioni di configurazione max server memory (MB) e min server memory (MB) che determinano i limiti di memoria fisica usata dall'allocatore di pagine singole. Il pool di buffer è contemporaneamente il meccanismo per l'allocazione di pagine singole e il maggiore consumer di allocazioni di pagine singole.
  • Allocatore di più pagine (MPA), per le allocazioni di memoria che richiedono più di 8 KB.
  • Allocatore CLR, che include gli heap CLR SQL e le relative allocazioni globali create durante l'inizializzazione di CLR.
  • Allocazioni di memoria per gli stack di thread nel processo di SQL Server.
  • Allocazioni di Windows dirette, per le richieste di allocazione di memoria effettuate direttamente da Windows. Sono incluse le allocazioni per l'utilizzo dell'heap di Windows e le allocazioni virtuali dirette effettuate dai moduli caricati nel processo di SQL Server. Alcuni esempi di queste richieste di allocazione di memoria sono le allocazioni da DLL di stored procedure estese, gli oggetti creati tramite procedure di automazione (chiamate sp_OA) e le allocazioni dai provider di server collegati.

A partire da SQL Server 2012 (11.x), le allocazioni di pagine singole, le allocazioni di più pagine e le allocazioni CLR sono tutte consolidate in un allocatore di pagine di "qualsiasi dimensione" e sono incluse nei limiti di memoria controllati dalle opzioni di configurazione max server memory (MB) e min server memory (MB) Questa modifica introduce capacità di ridimensionamento più accurate per tutti i requisiti di memoria che passano attraverso lo strumento di gestione della memoria di SQL Server.

Importante

Controllare con attenzione le configurazioni correnti di max server memory (MB) e min server memory (MB) dopo l'aggiornamento a SQL Server 2012 (11.x) e versioni successive. Infatti, a partire da SQL Server 2012 (11.x), queste configurazioni ora includono e tengono conto di più allocazioni di memoria rispetto alle versioni precedenti. Queste modifiche si applicano sia alle versioni a 32 bit che a 64 bit di SQL Server 2012 (11.x) e a SQL Server 2014 (12.x) e alle versioni a 64 bit di SQL Server 2016 (13.x) e versioni successive.

La tabella seguente indica se un tipo specifico di allocazione di memoria è controllato dalle opzioni di configurazione max server memory (MB) e min server memory (MB):

Tipo di allocazione di memoria SQL Server 2005 (9.x), SQL Server 2008 (10.0.x) e SQL Server 2008 R2 (10.50.x) A partire da SQL Server 2012 (11.x)
Allocazioni di singole pagine Sì Sì, consolidata in allocazioni di pagine "di qualsiasi dimensione"
Allocazioni di più pagine No Sì, consolidata in allocazioni di pagine "di qualsiasi dimensione"
Allocazioni CLR No Sì
Memoria stack di thread No No
Allocazioni dirette da Windows No No

SQL Server potrebbe eseguire il commit della memoria nell'impostazione max server memory

A partire da SQL Server 2012 (11.x), SQL Server potrebbe allocare altra memoria rispetto al valore specificato nell'impostazione max server memory. Questo comportamento può verificarsi quando il valore Memoria totale server (KB) ha già raggiunto il valore dell'impostazione Memoria prevista server (KB) (come specificato da max server memory). Se la memoria contigua disponibile è insufficiente per soddisfare le richieste di più pagine di memoria (più di 8 KB) a causa della frammentazione della memoria, SQL Server può eseguire l'overcommit anziché rifiutare la richiesta di memoria.

Non appena viene eseguita questa allocazione, l'attività in background Monitoraggio risorse inizia a segnalare a tutti i consumer di memoria di rilasciare la memoria allocata e tenta di portare il valore Memoria totale server (KB) al di sotto del valore specificato per Memoria prevista server (KB). Pertanto, l'utilizzo della memoria di SQL Server potrebbe entro breve superare l'impostazione max server memory (MB). In questo caso, la lettura del contatore delle prestazioni Memoria totale server (KB) risulterà superiore alle impostazioni max server memory (MB) e Memoria prevista server (KB).

Questo comportamento viene in genere osservato durante le operazioni seguenti:

  • Query su indici columnstore di grandi dimensioni
  • Query di grandi dimensioni per la modalità batch nel rowstore
  • Compilazioni o ricompilazioni di indici ColumnStore che usano grandi quantità di memoria per eseguire operazioni di hash e ordinamento.
  • Operazioni di backup che richiedono buffer di memoria di grandi dimensioni.
  • Operazioni di traccia che devono archiviare parametri di input di grandi dimensioni.
  • Richieste di concessione di memoria di grandi dimensioni

Se si osserva spesso questo comportamento, è consigliabile usare il flag di traccia 8121 in SQL Server 2019 (15.x) per consentire a Monitoraggio risorse di eseguire la pulizia più rapidamente. A partire da SQL Server 2022 (16.x) questa funzionalità è abilitata per impostazione predefinita e il flag di traccia non ha alcun effetto.

Modifiche apportate a memory_to_reserve a partire da SQL Server 2012

Nelle versioni precedenti di SQL Server, lo strumento di gestione della memoria di SQL Server riserva parte dello spazio di indirizzi virtuali (VAS) del processo perché sia utilizzato dall'allocatore di più pagine (MPA), dall'allocatore CLR, dalle allocazioni di memoria per gli stack di thread nel processo di SQL Server e dalle allocazioni di Windows dirette (DWA). Questa parte dello spazio indirizzi virtuali è nota anche come area MemToLeave o "pool non di buffer".

Lo spazio indirizzi virtuali riservato per queste allocazioni varia a seconda dell'opzione di configurazione memory_to_reserve. Il valore predefinito usato da SQL Server è 256 MB.

Dato che l'allocatore di pagine "di qualsiasi dimensione" gestisce anche le allocazioni di dimensioni superiori a 8 KB, il valore memory_to_reserve non include le allocazioni di più pagine. Ad eccezione di questa modifica, non vi sono altre novità per questa opzione di configurazione.

La tabella seguente indica se un tipo specifico di allocazione di memoria rientra nell'area memory_to_reserve dello spazio indirizzi virtuali per il processo di SQL Server:

Tipo di allocazione di memoria SQL Server 2005 (9.x), SQL Server 2008 (10.0.x) e SQL Server 2008 R2 (10.50.x) A partire da SQL Server 2012 (11.x)
Allocazioni di singole pagine No No, consolidata in allocazioni di pagine "di qualsiasi dimensione"
Allocazioni di più pagine Sì No, consolidata in allocazioni di pagine "di qualsiasi dimensione"
Allocazioni CLR Sì Sì
Memoria stack di thread Sì Sì
Allocazioni dirette da Windows Sì Sì

Gestione della memoria dinamica

Il comportamento predefinito per la gestione della memoria del motore di database di Microsoft SQL Server consiste nell'acquisire la quantità di memoria necessaria senza causare insufficienza di memoria nel sistema. Nel motore di database di SQL Server questo comportamento è reso possibile tramite l'uso delle API di notifica della memoria di Microsoft Windows.

Quando SQL Server utilizza la memoria in modo dinamico, esegue query periodiche sul sistema per determinare la quantità di memoria libera disponibile. Il mantenimento di tale memoria libera impedisce il paging del sistema operativo. Se è disponibile una quantità minore di memoria libera, SQL Server rilascia memoria al sistema operativo. Se è disponibile una quantità maggiore di memoria libera, SQL Server può allocare più memoria. SQL Server aggiunge memoria solo se richiesto dal relativo carico di lavoro. In un server non operativo non vengono aumentate le dimensioni del proprio spazio degli indirizzi virtuali. Se si nota che Gestione attività e Monitor prestazioni mostrano una diminuzione costante della memoria disponibile quando SQL Server usa la gestione dinamica della memoria, questo è il comportamento predefinito e non deve essere percepito come perdita di memoria.

Le opzioni di configurazione della memoria del server controllano l'allocazione di memoria di Server, la memoria per la compilazione, tutte le cache (incluso il pool di buffer), le concessioni di memoria per l'esecuzione di query, la memoria per la gestione blocchi e la memoria CLR1 (in particolare i clerk di memoria presenti in sys.dm_os_memory_clerks).

1 La memoria CLR viene gestita nelle allocazioni max_server_memory a partire da SQL Server 2012 (11.x).

La query seguente restituisce le informazioni sulla memoria attualmente allocata:

SELECT physical_memory_in_use_kb / 1024 AS sql_physical_memory_in_use_MB,
       large_page_allocations_kb / 1024 AS sql_large_page_allocations_MB,
       locked_page_allocations_kb / 1024 AS sql_locked_page_allocations_MB,
       virtual_address_space_reserved_kb / 1024 AS sql_VAS_reserved_MB,
       virtual_address_space_committed_kb / 1024 AS sql_VAS_committed_MB,
       virtual_address_space_available_kb / 1024 AS sql_VAS_available_MB,
       page_fault_count AS sql_page_fault_count,
       memory_utilization_percentage AS sql_memory_utilization_percentage,
       process_physical_memory_low AS sql_process_physical_memory_low,
       process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

Dimensioni dello stack

La memoria per gli stack di thread 1, CLR 2, i file DLL di procedure estese, i provider OLE DB a cui fanno riferimento query distribuite, gli oggetti di automazione con riferimenti nelle istruzioni Transact-SQL e qualsiasi allocazione di memoria eseguita da una DLL non SQL Server non sono controllate da max server memory (MB).

1 Fare riferimento all'articolo Configurare il server max worker threads (opzione di configurazione del server) per informazioni sui thread di lavoro predefiniti calcolati per un determinato numero di CPU per cui è stata impostata l'affinità nell'host corrente. Le dimensioni di stack di SQL Server sono le seguenti:

Architettura di SQL Server Architettura del sistema operativo Dimensioni dello stack
x86 (32 bit) x86 (32 bit) 512 KB
x86 (32 bit) x64 (64 bit) 768 KB
x64 (64 bit) x64 (64 bit) 2.048 KB
IA64 (Itanium) IA64 (Itanium) 4.096 KB

2 La memoria CLR viene gestita nelle allocazioni max_server_memory a partire da SQL Server 2012 (11.x).

SQL Server usa l'API di notifica di memoria QueryMemoryResourceNotification per determinare i casi in cui è possibile allocare e rilasciare memoria con Gestione memoria di SQL Server.

All'avvio di SQL Server, viene calcolata la dimensione dello spazio degli indirizzi virtuali per il pool di buffer in base a parametri come la quantità di memoria fisica nel sistema, il numero di thread del server e vari parametri di avvio. In SQL Server la quantità calcolata di spazio degli indirizzi virtuali del processo per il pool di buffer viene riservata, ma viene acquisita solo la quantità di memoria fisica necessaria (ovvero ne viene eseguito il commit) per il carico corrente.

L'istanza continua quindi ad acquisire la memoria necessaria per supportare il carico di lavoro. Man mano che altri utenti si connettono ed eseguono query, in SQL Server viene acquisita altra memoria fisica su richiesta. Un'istanza SQL Server continua ad acquisire memoria fisica fino a quando viene raggiunto il limite di allocazione max server memory (MB) o fino a quando il sistema operativo indica che non è più disponibile memoria in eccesso. La memoria viene liberata quando viene superato il valore dell'impostazione min server memory e il sistema operativo indica un'insufficienza di memoria disponibile.

L'avvio di altre applicazioni in un computer in cui viene eseguita un'istanza di SQL Server comporta l'uso di altra memoria e la quantità di memoria fisica libera si riduce oltre il livello di SQL Server. Nell'istanza di SQL Server l'uso della memoria viene regolato automaticamente. Se un'altra applicazione viene arrestata e viene resa disponibile altra memoria, l'istanza di SQL Server aumenta le dimensioni della propria allocazione di memoria. SQL Server può liberare e acquisire diversi megabyte di memoria al secondo, in modo che possa adattarsi rapidamente alle modifiche dell'allocazione della memoria.

Effetti delle opzioni min server memory e max server memory

Le opzioni di configurazione min server memory e max server memory stabiliscono il limite massimo e minimo della quantità di memoria usata dal pool di buffer e in altre cache del motore di database. Il pool di buffer non acquisisce immediatamente la quantità di memoria specificata in min server memory. ma solo la memoria necessaria per l'inizializzazione. Con l'aumentare del carico di lavoro da gestire, il motore di database di SQL Server continua ad acquisire la memoria necessaria per supportare il carico di lavoro. Il pool di buffer non libera la memoria acquisita fino a quando non viene raggiunta la quantità specificata in min server memory. Quando viene raggiunto il limite impostato in Memoria minima del server, il pool di buffer usa quindi l'algoritmo standard per acquisire e liberare memoria nella misura necessaria. L'unica differenza è rappresentata dal fatto che nel pool di buffer la quantità di memoria allocata non scende mai al di sotto del livello specificato in Memoria minima del server, né viene mai acquisita una quantità di memoria superiore al livello specificato in max server memory (MB).

Nota

SQL Server come processo acquisisce più memoria rispetto a quella specificata dall'opzione max server memory (MB). I componenti interni ed esterni possono allocare memoria al di fuori del pool di buffer, richiedendo altra memoria, ma la memoria allocata al pool di buffer in genere rappresenta la parte di memoria maggiore usata da SQL Server.

La quantità di memoria acquisita dal motore di database di SQL Server dipende interamente dal carico di lavoro assegnato all'istanza. È possibile che per un'istanza di SQL Server che elabora un numero ridotto di richieste, la quantità di memoria allocata rimanga sempre inferiore al valore specificato da min server memory.

Se si specifica lo stesso valore per min server memory e max server memory (MB), quando la memoria allocata al motore di database di SQL Server raggiunge tale valore, il motore di database smette di liberare e acquisire dinamicamente memoria per il pool di buffer.

Se un'istanza di SQL Server è in esecuzione in un computer in cui viene spesso avviata o arrestata l'esecuzione di altre applicazioni, è possibile che l'allocazione e la deallocazione della memoria da parte dell'istanza di SQL Server rallenti l'avvio delle altre applicazioni. Se SQL Server è solo una delle numerose applicazioni server in esecuzione in un singolo computer, l'amministratore di sistema dovrebbe controllare la quantità di memoria allocata a SQL Server. In questi casi, è possibile usare le opzioni min server memory e max server memory (MB) per controllare la quantità di memoria che può essere impiegata da SQL Server. I valori delle opzioni min server memory e max server memory vengono specificati in megabyte. Per altre informazioni su come impostare queste configurazioni di memoria, vedere Opzioni di configurazione della memoria del server.

Memoria usata dalle specifiche degli oggetti di SQL Server

Nella seguente tabella sono indicate le quantità di memoria approssimative usate da diversi oggetti in SQL Server. I valori riportati sono stimati e possono variare a seconda dell'ambiente e delle modalità di creazione degli oggetti:

  • Blocco (gestito da Gestione blocchi): 64 byte + 32 byte per proprietario
  • Connessione utente: circa (3 * dimensione pacchetto di rete + 94 KB)

Le dimensioni del pacchetto di rete indica le dimensioni del flusso TDS usato per le comunicazioni tra le applicazioni e il motore di database. La dimensione predefinita del pacchetto è 4 KB e viene controllata dall'opzione di configurazione delle dimensioni del pacchetto di rete.

Quando è abilitato MARS (Multiple Active Result Set), la connessione utente corrisponde a circa (3 + 3 * num connessioni logiche)) * dimensioni pacchetto di rete + 94 KB.

Effetti dell'opzione min memory per query

L'opzione di configurazione min memory per query consente di specificare la quantità minima di memoria, in kilobyte, che verrà allocata per l'esecuzione di una query. Questa operazione è nota anche come concessione di memoria minima. Tutte le query devono attendere che la memoria minima richiesta venga assegnata, prima che possa iniziare l'esecuzione, o fino a quando non viene superato il valore specificato nell'opzione di configurazione query wait server. Il tipo di attesa accumulato in questo scenario è RESOURCE_SEMAPHORE.

Importante

Non impostare l'opzione di configurazione del server min memory per query su un valore troppo elevato, in particolare nei sistemi molto occupati, perché ciò potrebbe causare:

  • Una maggiore contesa per le risorse di memoria.
  • Una riduzione della concorrenza aumentando la quantità di memoria per ogni singola query, anche se la memoria necessaria in fase di esecuzione è inferiore rispetto a questa configurazione.

Per consigli sull'uso di questa configurazione, vedere Configurare l'opzione di configurazione del server min memory per query.

Considerazioni sulla concessione di memoria

Per l'esecuzione in modalità riga la concessione di memoria iniziale non può essere superata in qualsiasi condizione. Se è necessaria più memoria della concessione iniziale per l'esecuzione di operazioni di hash o ordinamento, viene eseguito lo spill su disco. Un'operazione di hash con spill è supportata da un file di lavoro in TempDB, mentre un'operazione di ordinamento con spill è supportata da una tabella di lavoro.

Un evento spill che si verifica durante un'operazione di ordinamento è noto come Classe di evento di avviso di ordinamento. Gli avvisi di ordinamento indicano operazioni di ordinamento per cui la memoria disponibile risulta insufficiente. Ciò vale soltanto per le operazioni di ordinamento eseguite in una query, ad esempio una clausola ORDER BY in un'istruzione SELECT, e non per le operazioni di ordinamento che implicano la creazione di indici.

Un evento spill che si verifica durante un'operazione di hash è noto come Classe di evento di avviso di hash. Questo tipo di avviso viene generato quando si verifica una ricorsione di hash o l'interruzione dell'hashing (bailout hash) durante un'operazione di hash.

  • La ricorsione di hash si verifica quando la memoria disponibile non è sufficiente per l'input di compilazione, che viene quindi suddiviso in più partizioni elaborate separatamente. Se la memoria disponibile non è sufficiente per una delle partizioni, la partizione viene suddivisa ulteriormente in sottopartizioni che vengono elaborate separatamente. Il processo di suddivisione continua fino a quando non vengono inserite nella memoria disponibile tutte le partizioni o non viene raggiunto il livello massimo di ricorsione.
  • L'evento hash bailout si verifica quando un'operazione di hashing raggiunge il livello massimo di ricorsione e ricorre a un piano alternativo per l'elaborazione dei dati partizionati rimanenti. Questi eventi possono causare una riduzione delle prestazioni nel server.

Per l'esecuzione in modalità batch, la concessione di memoria iniziale può aumentare in modo dinamico fino a una determinata soglia interna per impostazione predefinita. Questo meccanismo di concessione di memoria dinamica è progettato per consentire l'esecuzione residente in memoria di operazioni di hash oppure ordinamento in modalità batch. Se la memoria risulta ancora insufficiente per queste operazioni, ne viene eseguito lo spill su disco.

Per altre informazioni sulle modalità di esecuzione, vedere la Guida sull'architettura di elaborazione delle query.

Gestione del 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. La gestione del buffer è un elemento chiave per il raggiungimento di tale efficienza. Il componente di gestione del buffer è costituito da due meccanismi, ovvero Gestione buffer che consente di accedere alle pagine del database e aggiornarle, e la cache del buffer, detta anche pool di buffer, che consente di ridurre le operazioni di I/O del file di database.

Per una spiegazione dettagliata dell'I/O del disco in SQL Server, vedere Concetti fondamentali sull'I/O di SQL Server.

Funzionamento di Gestione buffer

Un buffer è una pagina da 8 KB in memoria, ovvero delle stesse dimensioni di una pagina di dati o di indice. La cache del buffer è quindi suddivisa in pagine da 8 KB. Gestione buffer consente di gestire le funzioni per la lettura delle pagine di dati o di indice dai file su disco del database nella cache buffer e la riscrittura sul disco delle pagine modificate. 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 sul disco solo se vengono modificati. I dati nella cache del buffer possono essere modificati più volte prima di venire riscritti sul disco. Per altre informazioni, vedere Lettura di pagine e Scrittura di pagine.

All'avvio di SQL Server, viene calcolata la dimensione dello spazio degli indirizzi virtuali per la cache dei buffer in base a parametri come la quantità di memoria fisica nel sistema, il numero massimo di thread del server configurati e vari parametri di avvio. In SQL Server la quantità calcolata di spazio degli indirizzi virtuali del processo per la cache di buffer viene riservata (chiamata destinazione di memoria), ma viene acquisita solo la quantità di memoria fisica necessaria (ovvero ne viene eseguito il commit) per il carico corrente. È possibile eseguire query sulle colonne committed_target_kb e committed_kb nella vista del catalogo sys.dm_os_sys_info per restituire il numero di pagine riservate come destinazione di memoria e il numero di pagine su cui attualmente viene eseguito il commit nella cache del buffer, rispettivamente.

L'intervallo tra l'avvio di SQL Server e il momento in cui alla cache di buffer viene allocata la memoria massima è detto processo di avvio. Durante questo intervallo le richieste di lettura riempiono i buffer in base alle esigenze. Ad esempio, una richiesta di lettura di una singola pagina da 8 KB riempie una singola pagina del buffer. Questo significa che il processo di avvio dipende dal numero e dal tipo di richieste del client. Il processo di avvio viene reso più rapido mediante la trasformazione delle richieste di lettura di pagina singola in richieste di otto pagine allineate, ovvero un extent. Ciò consente un completamento del processo di avvio molto più rapido, in particolare nei computer con molta memoria. Per altre informazioni su pagine ed extent, vedere Guida all'architettura di pagine ed extent.

Gestione buffer usa la maggior parte della memoria nel processo di SQL Server e coopera pertanto con Gestione memoria per consentire agli altri componenti di usare i relativi buffer. Gestione buffer interagisce essenzialmente con i componenti seguenti:

  • Resource Manager, per controllare l'utilizzo globale della memoria e, nelle piattaforme a 32 bit, l'utilizzo dello spazio indirizzi.
  • Gestione database e il sistema operativo di SQL Server (SQLOS) per operazioni di I/O di file di basso livello.
  • Strumento di gestione dei log per la registrazione write-ahead.

Funzionalità supportate

Gestione buffer supporta le caratteristiche seguenti:

  • Gestione buffer supporta NUMA (Non-Uniform Memory Access). Le pagine della cache buffer vengono distribuite tra i nodi hardware NUMA, consentendo a un thread di accedere a una pagina del buffer allocata nel nodo NUMA locale anziché dalla memoria esterna.

  • Gestione buffer supporta l'aggiunta di memoria a caldo, consentendo agli utenti di aggiungere memoria fisica senza riavviare il server.

  • Gestione buffer supporta pagine di grandi dimensioni su piattaforme a 64 bit. Le dimensioni delle pagine sono specifiche della versione di Windows utilizzata.

    Nota

    Prima di SQL Server 2012 (11.x), l'abilitazione di pagine di grandi dimensioni in SQL Server richiedeva il flag di traccia 834.

  • In Gestione buffer sono disponibili strumenti di diagnostica aggiuntivi esposti tramite DMV. È possibile usare queste viste per monitorare varie risorse del sistema operativo specifiche di SQL Server. Ad esempio, è possibile usare la vista sys.dm_os_buffer_descriptors per monitorare le pagine nella cache del buffer.

Rilevamento dell'utilizzo elevato della memoria

L'utilizzo elevato della memoria è una condizione risultante dall'insufficienza della memoria e può comportare:

  • I/O aggiuntivo (ad esempio thread in background del Lazywriter molto attivo)
  • Frequenza di ricompilazione maggiore
  • Query con esecuzione più prolungata (in presenza di attese di concessione di memoria)
  • Cicli di CPU aggiuntivi

Questa situazione può avere cause esterne o interne. Le cause esterne includono:

  • La quantità di memoria fisica (RAM) disponibile è bassa. Il sistema deve ricorrere al trimming dei working set dei processi in esecuzione, con un possibile rallentamento generale. SQL Server può ridurre la destinazione di commit del pool di buffer e avviare trimming più frequenti delle cache interne.
  • La memoria di sistema disponibile complessiva (incluso il file di paging del sistema) è bassa. Ciò può causare errori di allocazione della memoria del sistema, perché non è in grado di eliminare tramite paging la memoria attualmente allocata.

Le cause interne includono:

  • Risposta una condizione di utilizzo elevato della memoria esterna, quando il motore di database di SQL Server imposta limiti massimi di utilizzo della memoria inferiori.
  • Riduzione manuale delle impostazioni della memoria riducendo l'opzione di configurazione max server memory.
  • Modifiche nella distribuzione di memoria dei componenti interni tra le varie cache.

Il motore di database di SQL Server implementa un framework dedicato al rilevamento e alla gestione delle condizioni di utilizzo elevato della memoria, come parte della gestione della memoria dinamica. Questo framework include l'attività in background nota come Monitoraggio risorse. L'attività Monitoraggio risorse controlla lo stato degli indicatori di memoria esterna e interna. Quando uno di questi indicatori cambia stato, viene calcolata e trasmessa la notifica corrispondente. Queste notifiche sono messaggi interni da ognuno dei componenti del motore, archiviati nel buffer circolare.

Due buffer circolari contengono le informazioni rilevanti per la gestione della memoria dinamica:

  • Il buffer circolare di Monitoraggio risorse, che tiene traccia delle attività di Monitoraggio risorse, ad esempio se è stata segnalata o meno una condizione di utilizzo elevato della memoria. Questo buffer circolare contiene informazioni sullo stato a seconda della condizione corrente di RESOURCE_MEMPHYSICAL_HIGH, RESOURCE_MEMPHYSICAL_LOW, RESOURCE_MEMPHYSICAL_STEADY o RESOURCE_MEMVIRTUAL_LOW.
  • Il buffer circolare del broker di memoria, che contiene i record delle notifiche relative alla memoria per ogni pool di risorse di Resource Governor. Quando viene rilevata una condizione di utilizzo elevato della memoria interna, vengono attivate le notifiche per memoria insufficiente per i componenti che allocano memoria, in modo da attivare azioni appropriate per bilanciare la memoria tra le cache.

I broker di memoria mantengono monitorato l'utilizzo della memoria da parte di ogni componente e quindi, in base alle informazioni raccolte, viene calcolato il valore ottimale di memoria per ognuno di questi componenti. È presente un set di broker per ogni pool di risorse di Resource Governor. Queste informazioni vengono quindi trasmesse a ognuno dei componenti, che procederanno ad aumentare o ridurre l'utilizzo come richiesto.

Per altre informazioni sui broker di memoria, vedere sys.dm_os_memory_brokers.

Rilevamento dell'errore

Per le pagine di database sono disponibili due meccanismi facoltativi, ovvero la protezione delle pagine incomplete e la protezione dei checksum, che consentono di assicurare l'integrità della pagina dal momento in cui viene scritta sul disco fino a quando viene letta di nuovo. Questi meccanismi offrono una modalità indipendente di verifica della correttezza non solo dell'archiviazione dei dati, ma anche di componenti hardware quali controller, driver, cavi e perfino del sistema operativo. La protezione viene aggiunta alla pagina immediatamente prima della scrittura sul disco e viene verificata dopo la lettura della pagina dal disco.

SQL Server esegue quattro tentativi per qualsiasi operazione di lettura non riuscita a causa di un errore di checksum, di pagina incompleta o di I/O. Se la lettura viene completata correttamente durante uno di questi tentativi, viene scritto un messaggio nel log degli errori e l'esecuzione del comando che ha attivato la lettura continua. Se tutti i tentativi hanno esito negativo, il comando viene interrotto con l'errore MSSQLSERVER_824.

Il tipo di protezione di pagina utilizzato è un attributo del database che contiene la pagina. La protezione dei checksum è la protezione predefinita per i database creati in SQL Server 2005 (9.x) e versioni successive. Il meccanismo di protezione di pagina viene specificato al momento della creazione del database e può essere modificato utilizzando ALTER DATABASE SET. È possibile determinare l'impostazione corrente per la protezione di pagina eseguendo una query nella colonna page_verify_option della vista del catalogo sys.databases o nella proprietà IsTornPageDetectionEnabled della funzione DATABASEPROPERTYEX.

Nota

Se l'impostazione relativa alla protezione di pagina viene modificata, la nuova impostazione non viene immediatamente estesa all'intero database. Il livello di protezione del database corrente viene infatti esteso alle pagine a ogni successiva scrittura. Ciò significa che il database potrebbe essere costituito da pagine con tipi di protezione diversi.

Protezione delle pagine incomplete

La protezione delle pagine incomplete, disponibile in SQL Server 2000 (8.x), rappresenta essenzialmente un modo di rilevare i danneggiamenti della pagina dovuti a interruzioni dell'alimentazione. Ad esempio, un'interruzione dell'alimentazione imprevista potrebbe far sì che solo una parte di una pagina venga scritta su disco. Se si usa la protezione delle pagine incomplete, quando la pagina viene scritta su disco, per ogni settore da 512 byte nella pagina di database da 8 kilobyte (KB) uno schema di firma a 2 bit specifico viene archiviato nell'intestazione di pagina del database.

In fase di lettura della pagina dal disco, i bit per il rilevamento di pagine incomplete archiviati nell'intestazione della pagina vengono confrontati con le informazioni effettive sui settori della pagina. Lo scherma di firma alterna il binario 01 e 10 con ogni scrittura, pertanto è sempre possibile sapere quando solo una parte dei settori è stata scritta sul disco: se un bit si trova nello stato errato quando la pagina viene successivamente letta, la pagina è stata scritta in modo non corretto e viene rilevata una pagina incompleta. Per il rilevamento delle pagine incomplete vengono utilizzate risorse minime, tuttavia non vengono rilevati tutti gli errori causati da problemi hardware del disco. Per informazioni sull'impostazione del rilevamento delle pagine incomplete, vedere ALTER DATABASE SET Options (Transact-SQL).

Protezione dei checksum

La protezione dei checksum, introdotta con SQL Server 2005 (9.x), offre una più solida funzionalità di controllo dell'integrità dei dati. Un checksum viene calcolato per i dati in ogni pagina scritta e archiviato nell'intestazione di pagina. Ogni volta che una pagina con un checksum archiviato viene letta dal disco, il motore di database ricalcola il checksum per i dati della pagina e genera un errore 824 se il nuovo checksum è diverso da quello archiviato. La protezione dei checksum è in grado di intercettare più errori rispetto alla protezione delle pagine incomplete in quanto dipende da ogni byte della pagina. Tuttavia, si tratta di un'operazione a moderato consumo di risorse.

Quando il checksum è abilitato, gli errori causati da interruzioni dell'alimentazione e da hardware o firmware difettosi possono essere rilevati ogni volta che Gestione buffer legge una pagina dal disco. Per informazioni sull'impostazione del checksum, vedere ALTER DATABASE SET Options (Transact-SQL).

Importante

Quando un utente o un database di sistema viene aggiornato a SQL Server 2005 (9.x) o versione successiva, viene mantenuto il valore PAGE_VERIFY (NONE o TORN_PAGE_DETECTION). È consigliabile usare CHECKSUM. TORN_PAGE_DETECTION potrebbe consentire l'utilizzo di un numero più limitato di risorse, ma offre una protezione minore rispetto all'opzione CHECKSUM.

Informazioni sull'accesso non uniforme alla memoria

SQL Server supporta l'accesso non uniforme alla memoria (NUMA, Non-Uniform Memory Access) e può essere usato in modo efficace con hardware NUMA senza alcuna configurazione specifica. Con l'aumentare della velocità di clock e del numero di processori, diventa sempre più difficile ridurre la latenza di memoria necessaria per utilizzare questa ulteriore capacità di elaborazione. Per ovviare a questo problema, i fornitori di hardware offrono cache L3 di grandi dimensioni. Si tratta, tuttavia, di una soluzione soggetta a limiti. L'architettura NUMA offre una soluzione scalabile per questo problema.

SQL Server è progettato per l'uso ottimale in computer basati su NUMA senza che sia necessario apportare alcuna modifica alle applicazioni. Per altre informazioni, vedere Soft-NUMA (SQL Server).

Partizione dinamica di oggetti memoria

Gli allocatori di heap, detti oggetti memoria in SQL Server, consentono al motore di database di allocare memoria dall'heap. È possibile monitorarli tramite la DMV sys.dm_os_memory_objects.

CMemThread è un tipo di oggetto memoria thread-safe che consente allocazioni di memoria simultanee da più thread. Per il monitoraggio corretto, gli oggetti CMemThread si basano sui costrutti di sincronizzazione (mutex) per garantire che solo un singolo thread alla volta aggiorni parti cruciali delle informazioni.

Nota

Il tipo di oggetto CMemThread viene usato in tutta la codebase del motore di database molte allocazioni diverse e può essere partizionato a livello globale, per nodo o per CPU.

Tuttavia, l'uso di mutex può causare contese se molti thread eseguono l'allocazione dallo stesso oggetto memoria in modo estremamente simultaneo. Pertanto, SQL Server supporta il concetto di oggetti memoria partizionati (PMO, Partitioned Memory Objects) e ogni partizione è rappresentata da un singolo oggetto CMemThread. Il partizionamento di un oggetto memoria è definito in modo statico e non può essere modificato dopo la creazione. Poiché i modelli di allocazione della memoria variano notevolmente in base ad aspetti quali l'utilizzo di hardware e memoria, non è possibile stabilire in anticipo il modello di partizionamento perfetto.

Nella maggior parte dei casi, l'uso di una singola partizione è sufficiente, ma in alcuni scenari questo può causare contese che possono essere evitate solo con un oggetto memoria con partizionamento elevato. Non è consigliabile partizionare ogni oggetto memoria poiché più partizioni possono causare altre inefficienze e aumentare la frammentazione della memoria.

Nota

Prima di SQL Server 2016 (13.x), era possibile usare il flag di traccia 8048 per forzare la conversione di un PMO basato su nodo in un PMO basato su CPU. A partire da SQL Server 2014 (12.x) SP2 e SQL Server 2016 (13.x) questo comportamento è dinamico e controllato dal motore.

A partire da SQL Server 2014 (12.x) SP2 ed SQL Server 2016 (13.x), il motore di database può rilevare in modo dinamico la contesa per un oggetto CMemThread specifico e innalzare di livello l'oggetto a un'implementazione basata su nodo o su CPU. L'innalzamento di livello del PMO rimane attivo fino al riavvio del processo di SQL Server. La contesa di CMemThread può essere rilevata dalla presenza di attese CMEMTHREAD elevate nella DMV sys.dm_os_wait_stats e osservando le colonne contention_factor, partition_type, exclusive_allocations_count e waiting_tasks_count della DMV sys.dm_os_memory_objects.

Contenuto correlato