SQL Server gestione memoria e Pagefile

SQL Server è progettato per  acquisire e liberare la memoria in modo dinamico in base alle necessità. Il suo obiettivo principale consiste nel ridurre al minimo l’I/O su disco poiché le letture e le scritture su disco impegnano la maggiore quantità di risorse se confrontate con le altre operazioni (a riguardo si veda Memory Architecture).

Per conservare le pagine lette dal database, SQL Server crea in memoria un pool di buffer e cerca di raggiungere un equilibrio tra i due obiettivi seguenti:

  • 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

Quando SQL Server utilizza la memoria in modo dinamico, esegue query periodiche sul sistema per determinare la memoria libera disponibile in modo da evitare il paging del sistema operativo rilasciando memoria al sistema operativo se necessario. Nel caso invece sia disponibile una quantità maggiore di memoria libera e il carico di lavoro lo richiede SQL Server alloca più memoria.

Quindi riassumendo per quanto concerne la gestione della memoria in SQL Server possiamo dire che:

  1. SQL Server non utilizza il PageFile
  2. SQL Server alloca la memoria solo quando il carico di lavoro lo richiede e il sistema ha memoria libera disponibile
  3. SQL Server non rilascia la memoria allocata a meno che la memoria libera del sistema non scenda a valori tali da richiedere il paging.

È possibile impostare manualmente le opzioni per la memoria e limitare la quantità di memoria massima a cui può accedere SQL Server (come pure garantire una memoria minima per maggiori informazioni si vedano Effects of min and max server memory e How to adjust memory usage by using configuration options in SQL Server).

Se si decide di  impostare manualmente la memoria massima assegnabile a SQL Server occorre determinare la memoria necessaria al sistema operativo, ad eventuali altre istanze in esecuzione e ad altri utilizzi (per esempio funzionalità di SQL Server oltre al DBMS, come ad esempio SQL Server Agent, Full Text, Reporting Services etc). La differenza tra Memoria fisica e Memoria non destinata a SQL Server rappresenta la quantità di memoria massima assegnabile a SQL Server (Max Server Memory).

Nel post Suggested Max Memory Settings for SQL Server 2005/2008 di Glenn Berry (SQL Server MVP 2007, 2008 e 2009) vengono ad esempio suggeriti dei valori di Memoria Massima per SQL Server in base alla Memoria fisica disponibile.

Si noti che il valore Max Server Memory si riferisce in realtà alla dimensione del pool di buffer e non alla memoria occupata da SQL Server.

L’impostazione della memora massima previene situazioni di memori pressure che possono verificarsi nel lasso di tempo che intercorre tra due query per la memoria libera disponibile. Inoltre come indicato nel seguente Importance of setting Max Server Memory in SQL Server and How to Set it su sistemi a 64 Bit è buona pratica impostare il limite massimo di memoria assegnabile a SQL Server (a riguardo si veda anche How to reduce paging of buffer pool memory in the 64-bit version of SQL Server).

Inoltre per evitare la paginazione dei dati di SQL Server è consigliabile abilitare la funzionalità Lock Pages in Memory, in SQL Server 2012 è sufficiente che l’utente che esegue SQL Server (per default l’utente virtuale NT Service\MSSQLSERVER) abbia il privilegio Locked Pages in Memory impostato tramite la policy locale Computer Windows Settings\Security Settings\User Rights Assignment\Lock pages in memory.

image

Per ulteriori informazioni si veda Server Memory Server Configuration Options e Enable the Lock Pages in Memory Option (Windows).

Ipotizzando quindi uno scenario in cui SQL Server 2012 sia installato su Windows 2008 R2 su sistema con 10 GB di RAM dedicato esclusivamente al servizio di database io di solito eseguo le seguenti configurazioni:

  1. Imposto la Max Server Memory per SQL Server a RAM-1GB quindi nell’esempio a 10GB-1GB=9 GB. La scelta di sottrarre 1 GB deriva dal fatto che il requisito minimo di sistema per WS2008R2 è 512 MB a cui aggiungo altri 512 MB per eventuali altri processi che possano avere la necessità di essere eseguiti anche anche il sistema è dedicato esclusivamente a SQL Server (script di backup, Reporting Services etc..)
  2. Abilito la funzionalità Lock Pages in Memory
  3. Imposto il PageFile a 1024 MB

La scelta di impostare il PageFile a 1024 MB nasce dalle seguenti considerazioni:

  1. SQL Server se configurato correttamente (Max Server Memory impostato e Lock Pages in Memory abilitata) non utilizza il PageFile.
  2. Per default WS2008R2 è configurato per generare in caso di fault un Kernel Dump il quale richiede 800 MB per sistemi con RAM >= 8GB come indicato in Understanding Crash Dump Files. Si tenga conto che il supporto Microsoft nel caso venga aperta una chiamata potrebbe richiedere il Kernel Dump.
  3. Per default WS2008R2 imposta il page file ad una dimensione pari alla RAM e considerando che la memoria del sistema oggetto di possibile paginazione è RAM-Max Server Memory che nell’esempio precedente risulta uguale a 1GB.

Per ulteriori informazioni si vedano: