Opzioni di configurazione della memoria del server

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 Gestione memoria di SQL Server per un processo di SQL Server usato da un'istanza di SQL Server.

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

Annotazioni

L'impostazione della memoria massima del server sul valore minimo può ridurre notevolmente le prestazioni di SQL Server e persino impedirne 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 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 questa memoria libera impedisce il paging del sistema operativo. Se meno memoria è libera, 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 virtuale.

Consultare l'esempio B per una query che restituisce la memoria attualmente in uso. max server memory controlla l'allocazione di memoria di 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 trovato 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 di SQL Server può allocare memoria e rilasciare memoria.

È consigliabile consentire a 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 può accedere. Prima di impostare la quantità di memoria per SQL Server, determinare l'impostazione di memoria appropriata sottraendo, dalla memoria fisica totale, la memoria necessaria per il sistema operativo e 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 di memoria

Le opzioni server min server memory e max server memory possono essere impostate per estendersi su 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.

Annotazioni

Le opzioni min server memory e max server memory sono opzioni avanzate. Se si usa la stored procedure di sistema sp_configure per modificare queste impostazioni, è possibile modificarle solo quando mostra opzioni avanzate è impostato su 1. Queste impostazioni diventano effettive immediatamente senza un riavvio del server.

Usare min_server_memory per garantire una quantità minima di memoria disponibile per Gestione memoria di SQL Server per un'istanza di SQL Server. SQL Server non allocherà immediatamente la quantità di memoria specificata in min server memory 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 simultaneamente 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 di 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 una macchina virtuale (VM) guest di SQL Server oltre a ciò che è necessario per ottenere prestazioni accettabili.

Annotazioni

SQL Server non garantisce di allocare la quantità di memoria specificata in min server memory. Se il carico sul server non richiede mai l'allocazione della quantità di memoria specificata in min server memory, SQL Server verrà eseguito con meno memoria.

Usare max_server_memory per garantire che il sistema operativo non subisca un utilizzo dannoso della 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 essere più accurati con questi calcoli per una singola istanza:

• Dalla memoria totale del sistema operativo, riservare 1 GB-4 GB al sistema operativo stesso.
• Sottrarre quindi l'equivalente delle potenziali allocazioni di memoria di SQL Server al di fuori del controllo max server memory , costituito da stack size ¹ * calculated max worker ^{threads 2} + -g startup parameter ³ (o 256MB per impostazione predefinita se -g non è impostato). Ciò che rimane deve essere l'impostazione max_server_memory per una singola istanza di installazione.

¹ Fare riferimento alla Guida all'architettura di gestione della memoria per informazioni sulle dimensioni dello stack di thread per architettura.

² Fare riferimento alla pagina della documentazione su come configurare l'opzione di configurazione del server max worker threads, per informazioni sui thread di lavoro predefiniti calcolati per un determinato numero di CPU affinizzate nell'host corrente.

³ Fare riferimento alla pagina della documentazione in Opzioni di avvio del servizio motore di database per informazioni sul parametro di avvio -g . Replicabile 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 la memoria massima del server
32 bit	64 MB
64 bit	128 MB

Come configurare le opzioni di memoria con 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 Gestione memoria DI SQL Server per un'istanza di SQL Server. Per impostazione predefinita, SQL Server può modificare i requisiti di memoria in modo dinamico in base alle risorse di sistema disponibili.

Procedura per configurare una quantità fissa di memoria

Per impostare una quantità fissa di memoria:

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 memoria server immettere la quantità desiderata per Memoria minima del server e Memoria massima del server.

   Usare le impostazioni predefinite per consentire a 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 le 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à effettiva dei dati per la condivisione file non sia selezionata. È possibile specificare la cache del file system più piccola selezionando Riduci la memoria usata o Bilancia.

Per controllare l'impostazione corrente nel sistema operativo

1. Fare clic su Start, fare clic su Pannello di controllo, fare doppio clic su Connessioni di rete, quindi fare doppio clic su Connessione area locale.

2. Nella scheda Generale fare clic su Proprietà, selezionare Condivisione file e stampanti reti Microsoft e quindi fare clic su Proprietà.

3. Se si seleziona Ottimizza velocità effettiva dei dati per le applicazioni di rete , scegliere qualsiasi altra opzione, fare clic su OK e quindi chiudere il resto delle finestre di dialogo.

Blocca pagine in memoria

Questi criteri di Windows determinano quali account possono utilizzare un processo per mantenere i dati nella memoria fisica, impedendo al sistema di trasferire i dati nella memoria virtuale su disco. Il blocco delle pagine in memoria può mantenere il server reattivo quando la memoria viene paginata su disco. L'opzione SQL Server Lock Pages in Memory è 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 di esecuzione sqlservr.exe è stato concesso il diritto utente "Pagine bloccate in memoria" (LPIM). Nelle versioni precedenti di SQL Server, l'impostazione dell'opzione Pagine di blocco per un'istanza a 32 bit di SQL Server richiede che l'account con privilegi per l'esecuzione sqlservr.exe disponga del diritto utente LPIM e che l'opzione di configurazione "awe_enabled" sia impostata su ON.

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

Per disabilitare le pagine di blocco 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 computer, quindi espandere Impostazioni di Windows.

3. Espandere Impostazioni di sicurezza e 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 privilegi per eseguire sqlservr.exe e fare clic su Rimuovi.

Gestore della memoria virtuale

I sistemi operativi a 32 bit forniscono l'accesso a 4 GB di spazio indirizzi virtuale. 2 GB di memoria virtuale sono privati per ogni processo e disponibili per l'utilizzo da parte delle applicazioni. 2 GB è riservato per l'uso del sistema operativo. Tutte le edizioni del sistema operativo includono un commutatore in grado di fornire alle applicazioni l'accesso fino a 3 GB di spazio indirizzi virtuale, limitando il sistema operativo a 1 GB. Per altre informazioni su come usare la configurazione della memoria del commutatore, vedere la documentazione di Windows sull'ottimizzazione da 4 gigabyte (4GT). Quando 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.

Le aree di cui è stato eseguito il commit dello spazio indirizzi vengono mappate alla memoria fisica disponibile da Gestione memoria virtuale Windows (VMM).

Per altre informazioni sulla quantità di memoria fisica supportata da sistemi operativi diversi, vedere la documentazione di Windows "Limiti di memoria per le versioni di Windows".

I sistemi di memoria virtuale consentono l'over-impegno della memoria fisica, in modo che il rapporto tra memoria virtuale e fisica possa superare 1:1. Di conseguenza, i programmi di grandi dimensioni possono essere eseguiti in computer con un'ampia gamma di configurazioni di memoria fisica. Tuttavia, l'uso di una quantità significativamente maggiore di memoria virtuale rispetto ai set di lavoro medi combinati di tutti i processi può causare prestazioni scarse.

Le opzioni min server memory e max server memory sono opzioni avanzate. Se si usa la stored procedure di sistema sp_configure per modificare queste impostazioni, è possibile modificarle solo quando l'opzione mostra opzioni avanzate è impostata su 1. Queste impostazioni diventano effettive immediatamente senza un 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:

• Usare max server memory per controllare l'utilizzo della memoria. Stabilire le impostazioni massime per ogni istanza, facendo attenzione che la quantità totale non sia maggiore della memoria fisica totale nel computer. È possibile assegnare a ogni istanza memoria proporzionale alle dimensioni previste del carico di lavoro o del database. Questo approccio offre il vantaggio che quando vengono avviati nuovi processi o istanze, la memoria libera sarà disponibile immediatamente. Lo svantaggio è che se non si eseguono tutte le istanze, nessuna delle istanze in esecuzione sarà in grado di usare la memoria disponibile rimanente.

• Usare min server memory per controllare l'utilizzo della memoria. Stabilire le impostazioni minime per ogni istanza, in modo che la somma di questi minimi sia di 1-2 GB inferiore alla memoria fisica totale nel computer. Anche in questo caso, è possibile stabilire questi minimi in modo proporzionale al carico previsto di tale istanza. Questo approccio offre il vantaggio che, se non tutte le istanze sono in esecuzione contemporaneamente, quelle in esecuzione possono usare la memoria disponibile rimanente. Questo approccio è utile anche quando è presente un altro processo a elevato utilizzo di memoria nel computer, perché garantisce che SQL Server otterrebbe almeno una quantità ragionevole di memoria. Lo svantaggio è che all'avvio di una nuova istanza (o di qualsiasi altro processo) potrebbero essere necessari del tempo per rilasciare memoria nelle istanze in esecuzione, soprattutto se devono scrivere pagine modificate nei database.

• Non eseguire alcuna operazione (non consigliata). Le prime istanze presentate con un carico di lavoro tendono ad allocare tutta la memoria. Le istanze inattive o avviate in un secondo momento possono terminare con una quantità minima di memoria disponibile. SQL Server non tenta di bilanciare l'utilizzo della memoria tra istanze. Tutte le istanze, tuttavia, risponderanno ai segnali di notifica della memoria di Windows per regolare le dimensioni del footprint di memoria. Windows non bilancia la memoria tra le applicazioni con l'API di notifica della memoria. Fornisce semplicemente commenti e suggerimenti globali sulla disponibilità della memoria nel sistema.

È possibile modificare queste impostazioni senza riavviare le istanze, in modo da poter provare facilmente a trovare le impostazioni migliori per il modello di utilizzo.

Fornire la quantità massima di memoria per SQL Server

	32 bit	64 bit
Memoria convenzionale	Fino al limite dello spazio di indirizzi virtuali per tutte le edizioni di SQL Server: 2GB 3 GB con /3 gb di parametro di avvio* 4 GB su WOW64**	Fino al limite dello spazio degli indirizzi virtuali del processo in tutte le edizioni di SQL Server. Architettura x64 con 8 TB

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

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

Esempi

Esempio A

L'esempio seguente imposta 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)