Opzioni di configurazione della memoria del server

Si applica a:SQL Server

L'utilizzo della memoria per il motore di database di SQL Server è limitato da una coppia di impostazioni di configurazione, ovvero min server memory (MB) e max server memory (MB). Nel corso del tempo e in circostanze normali, SQL Server proverà a richiedere memoria fino al limite impostato da max server memory (MB).

Nelle versioni precedenti di SQL Server, l'utilizzo della memoria era virtualmente illimitato, indicando SQL Server che tutta la memoria di sistema era utilizzabile. In tutte le versioni di SQL Server è consigliabile configurare un limite superiore per l'utilizzo della memoria di SQL Server configurando l'opzione max server memory (MB).

• A partire da SQL Server 2019 (15.x), il programma di installazione di SQL nei server Windows offre una raccomandazione per l'opzione max server memory (MB) per un'istanza autonoma di SQL Server in base a una percentuale della memoria di sistema disponibile al momento dell'installazione.
• In qualsiasi momento è possibile riconfigurare i limiti di memoria (in megabyte) per un processo di SQL Server usato da un'istanza di SQL Server tramite le opzioni di configurazione min server memory (MB) e max server memory (MB).

Consigli

Le impostazioni predefinite e i valori minimi consentiti per queste opzioni sono:

Entro questi limiti, SQL Server può modificare i propri requisiti di memoria in modo dinamico in base alle risorse di sistema disponibili. Per altre informazioni, vedere Gestione della memoria dinamica.

• Se si imposta max server memory (MB) su un valore troppo alto, una singola istanza di SQL Server potrebbe risultare in competizione per la memoria con altre istanze di SQL Server ospitate nello stesso host.
• L'impostazione di max server memory (MB) su un valore troppo basso è tuttavia un'opportunità persa di ottimizzare le prestazioni e potrebbe causare una condizione di pressione sulla memoria e problemi di prestazioni nell'istanza di SQL Server.
• L'impostazione di max server memory (MB) sul valore minimo può persino impedire l'avvio di SQL Server. Se non è possibile avviare SQL Server dopo la modifica di questa opzione, eseguire l'avvio con l'opzione di avvio -f e reimpostare max server memory (MB) sul valore precedente. Per altre informazioni, vedere Opzioni di avvio del servizio del motore di database.
• Non è consigliabile impostare max server memory (MB) e min server memory (MB) sullo stesso valore o quasi.

SQL Server può usare la memoria in modo dinamico. È tuttavia possibile impostare manualmente le opzioni per la memoria e limitare la quantità di memoria a cui può accedere SQL Server. 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, le allocazioni di memoria non controllate dall'impostazione max server memory (MB) e qualsiasi altra istanza di SQL Server (e altri usi del sistema, se il server ospita altre applicazioni che utilizzano memoria, incluse altre istanze di SQL Server). La differenza così ottenuta rappresenta la quantità di memoria massima assegnabile all'istanza corrente di SQL Server.

La memoria può essere configurata fino al limite dello spazio degli indirizzi virtuali del processo in tutte le edizioni di SQL Server. Per altre informazioni, vedere Memory Limits for Windows and Windows Server Releases (Limiti di memoria per le diverse versioni di Windows e Windows Server).

Min server memory

Usare min server memory (MB) per garantire una quantità minima di memoria disponibile per Gestione memoria di SQL Server.

• SQL Server non alloca immediatamente la quantità di memoria specificata in min server memory (MB) all'avvio. Se, tuttavia, l'utilizzo della memoria raggiunge tale valore a causa del carico di lavoro del client, SQL Server può liberare memoria solo se si riduce il valore di min server memory (MB). Quando ad esempio diverse istanze di SQL Server vengono installate simultaneamente nello stesso server, valutare l'opportunità di impostare il parametro min server memory (MB) in modo da riservare memoria per un'istanza.

• In un ambiente virtualizzato è essenziale impostare un valore di min server memory (MB) per assicurarsi che un utilizzo elevato di memoria dall'host sottostante non provi a deallocare memoria dal pool di buffer in una macchina virtuale guest, oltre i livelli necessari per garantire prestazioni accettabili. Idealmente, le istanze di SQL Server in una macchina virtuale non devono competere con i processi di deallocazione della memoria proattiva dell'host virtuale.

• SQL Server non esegue necessariamente l'allocazione della quantità di memoria specificata in min server memory (MB). Se il carico sul server non richiede mai l'allocazione della quantità di memoria specificata in min server memory (MB), SQL Server verrà eseguito con una quantità di memoria inferiore.

Max server memory

Usare max server memory (MB) per garantire che il sistema operativo e le altre applicazioni non subiscano una pressione dannosa sulla memoria da parte di SQL Server.

• Prima di impostare la configurazione di max server memory (MB), monitorare l'utilizzo complessivo della memoria da parte del server che ospita l'istanza di SQL Server, durante il normale funzionamento, per determinare la disponibilità e i requisiti di memoria. Per una configurazione iniziale o qualora non fosse possibile raccogliere i dati di utilizzo della memoria da parte dei processi di SQL Server nel corso del tempo, usare l'approccio consigliato generalizzato seguente per configurare max server memory (MB) per una singola istanza:
  • Dalla memoria totale del sistema operativo sottrarre l'equivalente delle allocazioni potenziali di memoria dei thread di SQL Server al di fuori del controllo di max server memory (MB), ovvero la dimensione dello stack¹ moltiplicata per il numero massimo di thread di lavoro calcolato².
  • Sottrarre quindi il 25% per le altre allocazioni di memoria al di fuori del controllo di max server memory (MB), ad esempio buffer di backup, DLL di stored procedure estese, oggetti creati usando procedure di Automazione (chiamate a sp_OA) e allocazioni da provider di server collegati. Si tratta di un'approssimazione generica e ogni situazione può essere diversa.
  • Il valore restante dovrebbe corrispondere all'impostazione di max server memory (MB) per la configurazione di una singola istanza.

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

² 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.

Impostare manualmente le opzioni

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

Usare Transact-SQL

min server memory (MB) e max server memory (MB) sono opzioni avanzate. Quando si usa 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. Per altre informazioni, vedere sp_configure.

L'esempio seguente imposta l'opzione max server memory (MB) su 12.288 MB o 12 GB. Anche se sp_configure specifica il nome dell'opzione come max server memory (MB), è possibile omettere (MB).

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

La query seguente restituisce informazioni sui valori attualmente configurati e il valore attualmente in uso. Questa query restituisce risultati indipendentemente dal fatto che l'opzione sp_configure 'show advanced options' sia abilitata.

SELECT [name], [value], [value_in_use]
FROM sys.configurations
WHERE [name] = 'max server memory (MB)' OR [name] = 'min server memory (MB)';

Usare SQL Server Management Studio

Usare min server memory (MB) e max server memory (MB) per riconfigurare la quantità di memoria (in megabyte) gestita tramite Gestione memoria di SQL Server per un'istanza di SQL Server.

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

2. Selezionare la pagina Memoria della finestra Proprietà server. Vengono visualizzati i valori correnti di Memoria minima per il server e Memoria massima per il server.

3. In Opzioni per la memoria del server immettere i numeri desiderati per Memoria minima per il server e Memoria massima per il server. Per i valori consigliati, vedere min server memory (MB) e max server memory (MB) in questo articolo.

Lo screenshot seguente illustra tutti e tre i passaggi:

Blocco di pagine in memoria

Le applicazioni basate su Windows possono usare le API AWE (Address Windowing Extensions) di Windows per allocare e mappare la memoria fisica nello spazio indirizzi del processo. Questi criteri di LPIM Windows determinano gli account autorizzati ad accedere all'API per mantenere i dati nella memoria fisica, impedendo al sistema di eseguire il paging dei dati nella memoria virtuale su disco. La memoria allocata con AWE è bloccata finché l'applicazione non la libera esplicitamente o non esce. L'uso delle API AWE per la gestione della memoria in SQL Server a 64 bit viene spesso definito pagine bloccate. 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 di pagine in memoria è abilitata nelle istanze di SQL Server Standard Edition ed edizioni superiori quando all'account con i privilegi per l'esecuzione di sqlservr.exe è stato concesso il diritto utente di Windows Blocco di pagine in memoria.

Per disabilitare l'opzione Blocco di pagine in memoria per SQL Server, rimuovere il diritto utente Blocco di pagine in memoria per l'account con i privilegi per l'esecuzione di sqlservr.exe (l'account di avvio di SQL Server).

L'uso del blocco di pagine in memoria non influisce sulla gestione dinamica della memoria di SQL Server, consentendone l'espansione o la riduzione su richiesta di altri clerk di memoria. Quando si usa il diritto utente Blocco di pagine in memoria è consigliabile impostare un limite superiore per max server memory (MB) come descritto in dettaglio in precedenza. Per altre informazioni, vedere max server memory (MB).

Il blocco di pagine in memoria deve essere usato in presenza di segnali di page out del processo sqlservr. In questo caso, verrà segnalato l'errore 17890 nel log degli errori, simile a quello riportato nell'esempio seguente:

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: ##%.

L'uso del blocco di pagine in memoria con un'impostazione max server memory (MB) configurata in modo non corretto, senza tenere in considerazione gli altri consumer di memoria del sistema, potrebbe essere causa di instabilità, a seconda della quantità di memoria richiesta dagli altri processi o degli altri requisiti di memoria di SQL Server non inclusi nell'ambito di max server memory (MB). Per altre informazioni, vedere max server memory. Se viene concesso il privilegio Blocco di pagine in memoria (nei sistemi a 32 bit o a 64 bit), è consigliabile impostare max server memory (MB) su un valore specifico, invece di lasciare l'impostazione predefinita 2.147.483.647 megabyte (MB).

Abilitare Blocco di pagine in memoria

Dopo aver valutato le informazioni precedenti, per abilitare l'opzione Blocco di pagine in memoria concedendo il privilegio all'account del servizio per l'istanza di SQL Server, vedere Abilitare l'opzione Blocco di pagine in memoria (Windows).

Per determinare l'account del servizio per l'istanza di SQL Server, fare riferimento a Gestione configurazione SQL Server o eseguire una query su service_account da sys.dm_server_services. Per altre informazioni, vedere sys.dm_server_services (Transact-SQL).

Visualizzare lo stato di Blocco di pagine in memoria

Per determinare se il privilegio Blocco di pagine in memoria viene concesso all'account del servizio per l'istanza di SQL Server, usare la query seguente. Questa query è supportata in SQL Server 2016 (13.x) SP1 e versioni successive.

SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;

I valori seguenti di sql_memory_model_desc indicano lo stato del blocco di pagine in memoria:

• CONVENTIONAL. Il privilegio Blocco di pagine in memoria non viene concesso.
• LOCK_PAGES. Il privilegio Blocco di pagine in memoria viene concesso.
• LARGE_PAGES. Il privilegio Blocco di pagine in memoria viene concesso in modalità Enterprise con il flag di traccia 834 abilitato. Si tratta di una configurazione avanzata non consigliata per la maggior parte degli ambienti. Per altre informazioni e precisazioni importanti, vedere Flag di traccia 834.

Usare i metodi seguenti per determinare se l'istanza di SQL Server usa pagine bloccate:

• L'output della seguente query Transact-SQL indica valori diversi da zero per locked_page_allocations_kb:

  SELECT osn.node_id, osn.memory_node_id, osn.node_state_desc, omn.locked_page_allocations_kb 
  FROM sys.dm_os_memory_nodes omn 
  INNER JOIN sys.dm_os_nodes osn ON (omn.memory_node_id = osn.memory_node_id) 
  WHERE osn.node_state_desc <> 'ONLINE DAC';
  

• Il log corrente degli errori di SQL Server segnala il messaggio Using locked pages in the memory manager durante l'avvio del server.

• La sezione Memory Manager dell'output DBCC MEMORYSTATUS mostra un valore diverso da zero per l'elemento AWE Allocated.

Istanze multiple di SQL Server

Quando si eseguono più istanze del motore di database, è possibile gestire la memoria in modi diversi:

• Usare max server memory (MB) in ogni istanza per controllare l'utilizzo della memoria, come descritto in dettaglio in precedenza. 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à inutilizzata.

• Usare min server memory (MB) in ogni istanza per controllare l'utilizzo della memoria, come descritto in dettaglio in precedenza. Stabilire le impostazioni minime per ogni istanza, in modo che la somma di tali minimi sia di 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 consente inoltre di riservare a SQL Server una quantità ragionevole di memoria quando sullo stesso computer vengono eseguiti anche altri processi particolarmente onerosi. 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.

• Usare sia max server memory (MB) che min server memory (MB) in ogni istanza per controllare l'utilizzo della memoria, osservando e ottimizzando l'utilizzo massimo e la protezione minima della memoria di ogni istanza in un'ampia gamma di livelli potenziali di utilizzo della memoria.

• Non intervenire in alcun modo (non consigliato). Le prime istanze sottoposte a carico di lavoro tendono 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.

Esempi

R. Impostare l'opzione di memoria massima del server su 4 GB

L'esempio seguente imposta l'opzione max server memory (MB) su 4096 MB o 4 GB. Anche se sp_configure specifica il nome dell'opzione come max server memory (MB), è possibile omettere (MB).

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

L'output risultante sarà un'istruzione simile a Configuration option 'max server memory (MB)' changed from 2147483647 to 4096. Run the RECONFIGURE statement to install. Il nuovo limite della memoria avrà effetto immediatamente all'atto dell'esecuzione di RECONFIGURE. Per altre informazioni, vedere sp_configure.

B. Determinare l'allocazione di memoria corrente

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;

C. Visualizzare il valore di max server memory (MB)

La query seguente restituisce informazioni sul valore attualmente configurato e il valore in uso. Questa query restituisce risultati indipendentemente dal fatto che l'opzione sp_configure 'show advanced options' sia abilitata.

SELECT [value], [value_in_use]
FROM sys.configurations WHERE [name] = 'max server memory (MB)';

Passaggi successivi

• Guida all'architettura di gestione della memoria
• Monitoraggio e ottimizzazione delle prestazioni
• RECONFIGURE (Transact-SQL)
• Opzioni di configurazione del server (SQL Server)
• sp_configure (Transact-SQL)
• Opzioni di avvio del servizio del motore di database
• Memory Limits for Windows and Windows Server Releases (Limiti di memoria per le diverse versioni di Windows e Windows Server)