Condividi tramite

Opzioni di configurazione server memory

  • Articolo

Usare le due opzioni di memoria del server, min server memory e max server memory, per riconfigurare la quantità di memoria (in megabyte) gestita dal SQL Server Memory Manager per un processo di SQL Server usato da un'istanza di SQL Server.

L'impostazione predefinita per min server memory è 0, mentre quella per max server memory è 2147483647 MB. Per impostazione predefinita, SQL Server può modificare i propri requisiti di memoria in modo dinamico in base alle risorse di sistema disponibili.

Nota

L'impostazione della memoria massima del server sul valore minimo può ridurre notevolmente le prestazioni SQL Server e persino impedire l'avvio. Se non è possibile avviare SQL Server dopo aver modificato questa opzione, avviarla usando l'opzione di avvio -f e reimpostare max server memory sul valore precedente. Per altre informazioni, vedere Opzioni di avvio del servizio del motore di database.

Quando SQL Server usa la memoria in modo dinamico, esegue periodicamente una query sul sistema per determinare la quantità di memoria libera. Il mantenimento di tale memoria libera impedisce il paging del sistema operativo. Se è disponibile meno memoria, SQL Server rilascia memoria al sistema operativo. Se è disponibile più memoria, SQL Server può allocare più memoria. SQL Server aggiunge memoria solo quando il carico di lavoro richiede più memoria. Un server inattivo non aumenta le dimensioni dello spazio indirizzi virtuali.

Vedere l'esempio B per una query che restituisce la memoria usata attualmente. max server memory controlla l'allocazione di memoria SQL Server, tra cui il pool di buffer, la memoria di compilazione, tutte le cache, le concessioni di memoria qe, la memoria di gestione blocchi e la memoria clr (essenzialmente qualsiasi clerk di memoria presente in sys.dm_os_memory_clerks). La memoria per gli stack di thread, gli heap di memoria, i provider di server collegati diversi da SQL Server e qualsiasi memoria allocata da una DLL non SQL Server non sono controllate dalla memoria massima del server.

SQL Server usa l'API di notifica della memoria QueryMemoryResourceNotification per determinare quando gestione memoria SQL Server può allocare memoria e rilasciare memoria.

È consigliabile consentire SQL Server di usare la memoria in modo dinamico. È tuttavia possibile impostare manualmente le opzioni di memoria e limitare la quantità di memoria a cui SQL Server possibile accedere. Prima di impostare la quantità di memoria per SQL Server, determinare l'impostazione di memoria appropriata sottraendo, dalla memoria fisica totale, dalla memoria fisica necessaria per il sistema operativo e da qualsiasi altra istanza di SQL Server (e altri usi di sistema, se il computer non è completamente dedicato a SQL Server). Questa differenza è la quantità massima di memoria che è possibile assegnare a SQL Server.

Impostazione manuale delle opzioni per la memoria

Le opzioni per la memoria min server memory e max server memory possono essere impostate come valori limite di un intervallo di valori di memoria. Questo metodo è utile per gli amministratori di sistema o di database per configurare un'istanza di SQL Server in combinazione con i requisiti di memoria di altre applicazioni o altre istanze di SQL Server eseguite nello stesso host.

Nota

min server memory e max server memory sono opzioni avanzate. Se si utilizza la stored procedure di sistema sp_configure per modificare queste impostazioni, è possibile modificarle solo se il valore di show advanced options è impostato su 1. Queste impostazioni diventano effettive immediatamente e non richiedono il riavvio del server.

Usare min_server_memory per garantire una quantità minima di memoria disponibile per SQL Server Memory Manager per un'istanza di SQL Server. SQL Server non allocherà immediatamente la quantità di memoria specificata nella memoria minima del server all'avvio. Tuttavia, dopo che l'utilizzo della memoria ha raggiunto questo valore a causa del caricamento del client, SQL Server non può liberare memoria a meno che il valore della memoria minima del server non venga ridotto. Ad esempio, quando più istanze di SQL Server possono esistere contemporaneamente nello stesso host, impostare il parametro min_server_memory anziché max_server_memory allo scopo di riservare memoria per un'istanza di . Inoltre, l'impostazione di un valore min_server_memory è essenziale in un ambiente virtualizzato per garantire che la pressione della memoria dall'host sottostante non tenti di deallocare la memoria dal pool di buffer in un SQL Server guest oltre a quanto necessario per le prestazioni accettabili.

Nota

SQL Server non è garantito allocare la quantità di memoria specificata nella memoria minima del server. Se il carico sul server non richiede mai l'allocazione della quantità di memoria specificata nella memoria minima del server, SQL Server verrà eseguito con meno memoria.

Usare max server memory per garantire che il sistema operativo non subisca un pregiudizievole utilizzo elevato di memoria. Per impostare la configurazione massima della memoria del server, monitorare l'utilizzo complessivo del processo di SQL Server per determinare i requisiti di memoria. Per ottenere calcoli più precisi per una singola istanza:

  • Dalla memoria totale del sistema operativo riservare 1-4 GB al sistema operativo stesso.
  • Sottrarre quindi l'equivalente di potenziali allocazioni di memoria SQL Server al di fuori del controllo max server memory, che è costituito da dimensioni dello stack 1 * thread di lavoro calcolati 2 + -g parametro di avvio 3 (o 256 MB per impostazione predefinita se -g non è impostato). Il valore restante dovrebbe corrispondere all'impostazione di max server memory per la configurazione di una singola istanza.

1 Fare riferimento alla Guida sull'architettura di gestione della memoria per informazioni sulle dimensioni degli stack di thread per ogni architettura.

2 Fare riferimento alla pagina della documentazione Configurare l'opzione di configurazione del server max worker threads per informazioni sui thread di lavoro predefiniti calcolati per un determinato numero di CPU per cui è stata impostata l'affinità nell'host corrente.

3 Fare riferimento alla pagina della documentazione Opzioni di avvio del servizio del motore di database per informazioni sul parametro di avvio -g. Aplicabile solo a SQL Server a 32 bit (da SQL Server 2005 a SQL Server 2014).

Tipo di sistema operativo Quantità minime di memoria consentite per max server memory
32 bit 64 MB
64 bit 128 MB

Come configurare le opzioni per la memoria tramite SQL Server Management Studio

Usare le due opzioni di memoria del server, min server memory e max server memory, per riconfigurare la quantità di memoria (in megabyte) gestita da SQL Server Memory Manager per un'istanza di SQL Server. Per impostazione predefinita, SQL Server può modificare i propri requisiti di memoria in modo dinamico in base alle risorse di sistema disponibili.

Procedura per la configurazione di una quantità di memoria fissa

Per impostare una quantità di memoria fissa:

  1. In Esplora oggetti fare clic con il pulsante destro del mouse su un server e scegliere Proprietà.

  2. Fare clic sul nodo Memoria .

  3. In Opzioni per la memoria del serverimmettere la quantità da usare per Memoria minima per il server e Memoria massima per il server.

    Usare le impostazioni predefinite per consentire SQL Server di modificare dinamicamente i requisiti di memoria in base alle risorse di sistema disponibili. L'impostazione predefinita per min server memory è 0 e l'impostazione predefinita per max server memory è 2147483647 megabyte (MB).

Ottimizzare la velocità effettiva dei dati per applicazioni di rete

Per ottimizzare l'uso della memoria di sistema per SQL Server, è necessario limitare la quantità di memoria usata dal sistema per la memorizzazione nella cache dei file. Per limitare la cache del file system, assicurarsi che l'opzione Massimizza la velocità di trasmissione dati per condivisione file non sia selezionata. Per specificare la quantità minima di cache del file system, è possibile selezionare Minimizza la quantità di memoria usata o Bilanciamento.

Per controllare l'impostazione corrente nel sistema operativo

  1. Fare clic su Start, scegliere Pannello di controllo, fare doppio clic su Connessioni di rete, quindi su Connessione alla rete locale (LAN).

  2. Nella scheda Generale fare clic su Proprietà, selezionare l'opzione Condivisione file e stampanti per reti Microsofte quindi scegliere Proprietà.

  3. Se l'opzione Massimizza la velocità di trasmissione dati per le applicazioni di rete è selezionata, scegliere un'altra opzione, fare clic su OK, quindi chiudere tutte le finestre di dialogo rimanenti.

Blocco di pagine in memoria

Questi criteri di Windows determinano gli account autorizzati a usare un processo per mantenere i dati nella memoria fisica, impedendo al sistema di eseguire il paging dei dati nella memoria virtuale su disco. Il blocco delle pagine in memoria può garantire il corretto funzionamento del server quando si verifica il paging della memoria su disco. L'opzione blocco pagine in memoria SQL Server è impostata su ON in istanze a 32 bit e a 64 bit di SQL Server 2014 Standard Edition e versioni successive quando all'account con privilegi per eseguire sqlservr.exe è stato concesso il diritto utente "Pagine bloccate in memoria" (LPIM). In versioni precedenti di SQL Server, l'impostazione dell'opzione Blocco pagine per un'istanza a 32 bit di SQL Server richiede che l'account con i privilegi per eseguire sqlservr.exe disponga del diritto utente LPIM e che l'opzione "awe_enabled"sia impostata su ON.

Per disabilitare l'opzione Blocca pagine in memoria per SQL Server, rimuovere il diritto utente "Pagine bloccate in memoria" per l'account di avvio SQL Server.

Per disabilitare l'opzione Blocco di pagine in memoria

Per disabilitare l'opzione blocco pagine in memoria:

  1. Fare clic sul menu Start e scegliere Esegui. Nella casella Apri digitare gpedit.msc.

    Viene visualizzata la finestra di dialogo Criteri gruppo .

  2. Nella console Criteri di gruppo espandere Configurazione computere quindi espandere Impostazioni di Windows.

  3. Espandere Impostazioni sicurezzae quindi espandere Criteri locali.

  4. Selezionare la cartella Assegnazione diritti utente .

    I criteri verranno visualizzati nel riquadro dei dettagli.

  5. Nel riquadro fare doppio clic su Blocco di pagine in memoria.

  6. Nella finestra di dialogo Impostazioni criteri di sicurezza locali selezionare l'account con i privilegi per eseguire sqlservr.exe e fare clic su Rimuovi.

Virtual Memory Manager

I sistemi operativi a 32 bit consentono di accedere a 4 GB di spazio degli indirizzi virtuali. 2 GB di memoria virtuale sono riservati a singoli processi e disponibili per l'utilizzo da parte delle applicazioni. 2 GB sono riservati all'utilizzo da parte del sistema operativo. In tutte le edizioni del sistema operativo è disponibile un'opzione che consente alle applicazioni di accedere a un massimo di 3 GB di spazio degli indirizzi virtuali, limitando il sistema operativo a 1 GB. Per altre informazioni su come usare l'opzione di configurazione della memoria, vedere la documentazione di Windows sull'ottimizzazione a 4 gigabyte. Quando il SQL Server a 32 bit è in esecuzione nel sistema operativo a 64 bit, lo spazio di indirizzi virtuale disponibile per l'utente è l'intero 4 GB.

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.

min server memory e max server memory sono opzioni avanzate. Se si utilizza la stored procedure di sistema sp_configure per modificare queste impostazioni, è possibile modificarle solo se il valore di show advanced options è impostato su 1. Queste impostazioni diventano effettive immediatamente e non richiedono il riavvio del server.

Esecuzione di più istanze di SQL Server

Quando si eseguono più istanze del motore di database, è possibile usare tre approcci per gestire la memoria:

  • Controllare l'utilizzo di memoria usando max server memory . Stabilire le impostazioni massime per ogni istanza, accertandosi che il totale non sia superiore alla memoria fisica disponibile sul computer. È possibile rendere la memoria di ogni istanza proporzionale al relativo carico di lavoro previsto o alle dimensioni del database. Questo approccio presenta il vantaggio di rendere la memoria libera immediatamente disponibile ad ogni nuovo processo o istanza. Lo svantaggio è che se non vengono eseguite tutte le istanze, parte della memoria resterà inusata.

  • Controllare l'utilizzo di memoria usando min server memory . Stabilire le impostazioni minime per ogni istanza, in modo che la somma di tali minimi sia 1-2 GB inferiore alla memoria fisica totale del computer. Anche questi minimi possono essere resi proporzionali al carico previsto dell'istanza. Con questo approccio, quando non vengono eseguite tutte le istanze contemporaneamente, quelle in esecuzione potranno usare la memoria libera rimanente. Questo approccio è utile anche quando è presente un altro processo a elevato utilizzo di memoria nel computer, poiché si assicura che SQL Server almeno otterrebbe una quantità ragionevole di memoria. Lo svantaggio è che quando vengono avviati una nuova istanza o altri processi, le istanze eseguite rilasceranno la memoria con un certo ritardo, in particolare quando a tale scopo dovranno riscrivere le pagine modificate nei rispettivi database.

  • Non intervenire in alcun modo (non consigliato). Le prime istanze sottoposte a carico di lavoro tenderanno ad allocare tutta la memoria. Alle istanze inattive o a quelle avviate in un secondo momento verrà destinata solo una minima quantità di memoria. In SQL Server non viene ripartita in alcun modo la memoria tra le diverse istanze. Tutte le istanze, tuttavia, risponderanno ai segnali di Windows Memory Notification correggendo di conseguenza le dimensioni dei rispettivi footprint di memoria. In Windows la memoria non viene bilanciata tra le applicazioni tramite l'API di Windows Memory Notification. Offre invece un semplice feedback globale sulla disponibilità di memoria nel sistema.

Poiché è possibile modificare queste impostazioni senza riavviare le istanze, sarà possibile provare agevolmente valori diversi fino a individuare quelli più adatti alle esigenze.

Assegnazione della quantità massima di memoria a SQL Server

32 bit 64 bit
Memoria convenzionale Fino a elaborare il limite di spazio indirizzi virtuale in tutte le edizioni SQL Server:

2 GB

3 GB con il parametro di avvio /3 gb*

4 GB su WOW64**		 Fino a elaborare il limite di spazio indirizzi virtuale in tutte le edizioni SQL Server:

8 TB in sistemi con architettura x64

*/3gb è un parametro di avvio del sistema operativo. Per altre informazioni, visitare MSDN Library.

**WOW64 (Windows in Windows 64) è una modalità in cui viene eseguita SQL Server a 32 bit in un sistema operativo a 64 bit. Per altre informazioni, visitare MSDN Library.

Esempio

Esempio A

Nell'esempio seguente viene impostata l'opzione max server memory su 4 GB.

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'max server memory', 4096;  
GO  
RECONFIGURE;  
GO

Esempio B: Determinazione dell'allocazione di memoria corrente

La query seguente restituisce le informazioni sulla memoria attualmente allocata.

SELECT  
(physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB,  
(locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB,  
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,  
process_physical_memory_low,  
process_virtual_memory_low  
FROM sys.dm_os_process_memory;

Vedere anche

Monitoraggio e ottimizzazione delle prestazioni
RECONFIGURE (Transact-SQL)
Opzioni di configurazione del server (SQL Server)
sp_configure (Transact-SQL)