Opzioni di configurazione del server Server Memory

QUESTO ARGOMENTO SI APPLICA A:sìSQL Server (a partire dalla versione 2008)noDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Usare le due opzioni per la memoria del server min server memory e max server memory per riconfigurare la quantità di memoria, in megabyte, gestita con Gestione memoria di SQL Server per un processo di SQL Server usato da un'istanza di SQL ServerSQL Server.

L'impostazione predefinita per min server memory è 0, mentre quella per max server memory è 2147483647 MB. Per impostazione predefinita, in SQL ServerSQL Server i requisiti di memoria possono variare dinamicamente in base alle risorse di sistema disponibili.

Nota

L'impostazione di max server memory sul valore minimo provoca un grave peggioramento delle prestazioni di SQL ServerSQL Server, fino a impedirne l'avvio. Se non è possibile avviare SQL ServerSQL Server dopo la modifica di questa opzione, eseguire l'avvio con 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 ServerSQL 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 ServerSQL Server rilascia memoria al sistema operativo. Se è disponibile una quantità maggiore di memoria libera, in SQL ServerSQL Server può essere allocata più memoria. SQL ServerSQL 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.

Vedere l'esempio B per una query che restituisce la memoria usata attualmente. max server memory controlla l'allocazione di memoria di SQL ServerSQL Server, compresi il pool di buffer, la memoria per la compilazione, tutte le cache, le concessioni di memoria qe, la memoria per la gestione blocchi e la memoria clr (in particolare i clerk di memoria presenti 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 ServerSQL Server e la memoria allocata da una DLL non di SQL ServerSQL Server non sono controllati da max server memory.

SQL ServerSQL 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.

È consigliabile consentire a SQL ServerSQL Server di usare la memoria in modo dinamico. È possibile, tuttavia, impostare manualmente le opzioni per la memoria e limitare la quantità di memoria a cui può accedere SQL ServerSQL Server. Prima di impostare la quantità di memoria per SQL ServerSQL Server, determinare l'impostazione appropriata per la memoria sottraendo dalla memoria fisica totale la memoria necessaria per il sistema operativo e per qualsiasi altra istanza di SQL ServerSQL Server, nonché per eventuali altri utilizzi del sistema se il computer non è completamente dedicato a SQL ServerSQL Server. La differenza così ottenuta rappresenta la quantità di memoria massima assegnabile a SQL ServerSQL Server.

Impostazione manuale delle opzioni per la memoria

Impostare min server memory e max server memory per estendere l'intervallo di valori di memoria. Questo metodo è utile per gli amministratori di sistema o di database che desiderano configurare un'istanza di SQL ServerSQL Server insieme ai requisiti di memoria di altre applicazioni eseguite nello stesso computer.

Usare min server memory per garantire una quantità minima di memoria disponibile per Gestione memoria di SQL Server per un'istanza di SQL ServerSQL Server. SQL ServerSQL Server non alloca immediatamente la quantità di memoria specificata in min server memory all'avvio. Se, tuttavia, l'utilizzo della memoria raggiunge tale valore a causa del carico di lavoro del client, SQL ServerSQL Server può liberare memoria solo se si riduce il valore di min server memory.

Nota

SQL ServerSQL Server non esegue necessariamente l'allocazione della 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 ServerSQL Server verrà eseguito con una quantità di memoria inferiore.

La quantità di memoria minima consentita per max server memory è 128 MB.

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

Usare le due opzioni per la memoria del server min server memory e max server memory per riconfigurare la quantità di memoria, in megabyte, gestita tramite Gestione memoria di SQL Server per un'istanza di SQL ServerSQL Server. Per impostazione predefinita, in SQL ServerSQL Server i requisiti di memoria possono variare dinamicamente 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 server immettere la quantità da usare per Memoria minima per il server e Memoria massima per il server.

    Usare le impostazioni predefinite per consentire a SQL ServerSQL Server di modificare i requisiti di memoria in base alle risorse di sistema disponibili. L'impostazione predefinita per min server memory è 0, mentre quella per max server memory è 2147483647 MB.

Ottimizzare la velocità effettiva dei dati per applicazioni di rete

Per ottimizzare l'utilizzo della memoria di sistema per SQL ServerSQL Server, è necessario limitare la quantità di memoria usata dal sistema per la memorizzazione dei file nella cache. 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 Microsoft e 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 di SQL Server Blocco di pagine in memoria viene impostata su ON per istanze di SQL Server 2017SQL Server 2017 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 (LPIM).

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

Per disabilitare l'opzione Blocco di pagine in memoria

Per disabilitare l'opzione Blocco di 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 e 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

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 di Motore di databaseDatabase Engine, è possibile gestire la memoria in tre modi:

  • 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 consente inoltre di riservare a SQL ServerSQL 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.

  • 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. SQL ServerSQL 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

La memoria può essere configurata fino al limite dello spazio degli indirizzi virtuali di processo in tutte le edizioni di SQL ServerSQL Server (8 TB).

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

Esempi

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)