Opzioni di configurazione del server Server MemoryServer Memory Server Configuration Options

In questo argomento si applica a: SìSQL ServernonDatabase SQL di AzurenonAzure SQL Data Warehouse non Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Usare le due opzioni per la memoria del server min server memory e max server memoryper 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.Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) that is managed by the SQL Server Memory Manager for a SQL Server process used by an instance of SQL ServerSQL Server.

L'impostazione predefinita per min server memory è 0, mentre quella per max server memory è 2.147.483.647 MB.The default setting for min server memory is 0, and the default setting for max server memory is 2,147,483,647 megabytes (MB). Per impostazione predefinita, in SQL ServerSQL Server i requisiti di memoria possono variare dinamicamente in base alle risorse di sistema disponibili.By default, SQL ServerSQL Server can change its memory requirements dynamically based on available system resources. Per altre informazioni, vedere Gestione della memoria dinamica.For more information, see dynamic memory management.

La quantità di memoria minima consentita per max server memory è 128 MB.The minimum memory amount allowable for max server memory is 128 MB.

Importante

Se si imposta max server memory su un valore troppo alto, una singola istanza di SQL ServerSQL Server potrebbe essere in competizione per la memoria con altre istanze di SQL ServerSQL Server ospitate nello stesso host.Setting max server memory value too high can cause a single instance of SQL ServerSQL Server might have to compete for memory with other SQL ServerSQL Server instances hosted on the same host. Tuttavia, un valore troppo basso per questa impostazione potrebbe causare un utilizzo molto elevato di memoria e problemi di prestazioni.However, setting this value too low could cause significant memory pressure and performance problems. L'impostazione di max server memory sul valore minimo può persino impedire l'avvio di SQL ServerSQL Server.Setting max server memory to the minimum value can even prevent SQL ServerSQL Server from starting. 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.If you cannot start SQL ServerSQL Server after changing this option, start it using the –f startup option and reset max server memory to its previous value. Per altre informazioni, vedere Opzioni di avvio del servizio del motore di database.For more information, see Database Engine Service Startup Options.

SQL ServerSQL Server può 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. can use memory dynamically; however, you can set the memory options manually and restrict the amount of memory that SQL ServerSQL Server can access. 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, per le allocazioni di memoria non controllate dall'impostazione max server memory 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.Before you set the amount of memory for SQL ServerSQL Server, determine the appropriate memory setting by subtracting, from the total physical memory, the memory required for the OS, memory allocations not controlled by the max_server_memory setting, and any other instances of SQL ServerSQL Server (and other system uses, if the computer is not wholly dedicated to SQL ServerSQL Server). La differenza così ottenuta rappresenta la quantità di memoria massima assegnabile all'istanza corrente di SQL ServerSQL Server.This difference is the maximum amount of memory you can assign to the current SQL ServerSQL Server instance.

Impostazione manuale delle opzioni per la memoriaSetting the memory options manually

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.The server options min server memory and max server memory can be set to span a range of memory values. 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 o altre istanze di SQL ServerSQL Server eseguite nello stesso host.This method is useful for system or database administrators to configure an instance of SQL ServerSQL Server in conjunction with the memory requirements of other applications, or other instances of SQL ServerSQL Server that run on the same host.

Nota

min server memory e max server memory sono opzioni avanzate.The min server memory and max server memory options are advanced options. 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.If you are using the sp_configure system stored procedure to change these settings, you can change them only when show advanced options is set to 1. Queste impostazioni diventano effettive immediatamente e non richiedono il riavvio del server.These settings take effect immediately without a server restart.

Usare min server memory per garantire una quantità minima di memoria disponibile per lo strumento di gestione della memoria di SQL ServerSQL Server per un'istanza di SQL ServerSQL Server.Use min_server_memory to guarantee a minimum amount of memory available to the SQL ServerSQL Server Memory Manager for an instance of SQL ServerSQL Server. SQL ServerSQL Server non alloca immediatamente la quantità di memoria specificata in min server memory all'avvio. will not immediately allocate the amount of memory specified in min server memory on startup. 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 .However, after memory usage has reached this value due to client load, SQL ServerSQL Server cannot free memory unless the value of min server memory is reduced. Ad esempio, quando più istanze di SQL ServerSQL 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.For example, when several instances of SQL ServerSQL Server can exist concurrently in the same host, set the min_server_memory parameter instead of max_server_memory for the purpose of reserving memory for an instance. In un ambiente virtualizzato, inoltre, è essenziale impostare un valore min server memory per assicurarsi che un utilizzo elevato di memoria dall'host sottostante non tenti di deallocare memoria dal pool di buffer in una macchina virtuale SQL ServerSQL Server guest, oltre i livelli necessari per garantire prestazioni accettabili.Also, setting a min_server_memory value is essential in a virtualized environment to ensure memory pressure from the underlying host does not attempt to deallocate memory from the buffer pool on a guest SQL ServerSQL Server virtual machine (VM) beyond what is needed for acceptable performance.

Nota

SQL ServerSQL Server non esegue necessariamente l'allocazione della quantità di memoria specificata in min server memory. is not guaranteed to allocate the amount of memory specified 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.If the load on the server never requires allocating the amount of memory specified in min server memory, SQL ServerSQL Server will run with less memory.

Usare max server memory per garantire che il sistema operativo non subisca un pregiudizievole utilizzo elevato di memoria.Use max_server_memory to guarantee the OS does not experience detrimental memory pressure. Per impostare la configurazione di max server memory, monitorare il consumo complessivo del processo SQL ServerSQL Server per determinare i requisiti di memoria.To set max server memory configuration, monitor overall consumption of the SQL ServerSQL Server process in order to determine memory requirements. Per ottenere calcoli più precisi per una singola istanza:To be more accurate with these calculations for a single instance:

  • Dalla memoria totale del sistema operativo riservare 1-4 GB al sistema operativo stesso.From the total OS memory, reserve 1GB-4GB to the OS itself.
  • Sottrarre quindi l'equivalente delle allocazioni di memoria di SQL ServerSQL Server potenziali dal controllo max server memory, costituito da dimensioni dello stack 1 * numero massimo thread di lavoro calcolati 2 + parametro di avvio -g 3 (o 256 MB per impostazione predefinita se -g non è impostato).Then subtract the equivalent of potential SQL ServerSQL Server memory allocations outside the max server memory control, which is comprised of stack size 1 * calculated max worker threads 2 + -g startup parameter 3 (or 256MB by default if -g is not set). Il valore restante dovrebbe corrispondere all'impostazione di max server memory per la configurazione di una singola istanza.What remains should be the max_server_memory setting for a single instance setup.

1 Fare riferimento alla Guida sull'architettura di gestione della memoria per informazioni sulle dimensioni degli stack di thread per ogni architettura.1 Refer to the Memory Management Architecture guide for information on thread stack sizes per architecture.

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.2 Refer to the documentation page on how to Configure the max worker threads Server Configuration Option, for information on the calculated default worker threads for a given number of affinitized CPUs in the current host.

3 Fare riferimento alla pagina della documentazione Opzioni di avvio del servizio del motore di database per informazioni sul parametro di avvio -g.3 Refer to the documentation page on Database Engine Service Startup Options for information on the -g startup parameter.

Come configurare le opzioni di memoria tramite SQL Server Management StudioSQL Server Management StudioHow to configure memory options using SQL Server Management StudioSQL 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 dallo strumento di gestione della memoria di SQL ServerSQL Server per un'istanza di SQL ServerSQL Server.Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) managed by the SQL ServerSQL Server Memory Manager for an instance of SQL ServerSQL Server. Per impostazione predefinita, in SQL ServerSQL Server i requisiti di memoria possono variare dinamicamente in base alle risorse di sistema disponibili.By default, SQL ServerSQL Server can change its memory requirements dynamically based on available system resources.

Per impostare una quantità di memoria fissa:To set a fixed amount of memory:

  1. In Esplora oggetti fare clic con il pulsante destro del mouse su un server e scegliere Proprietà.In Object Explorer, right-click a server and select Properties.

  2. Fare clic sul nodo Memoria .Click the Memory node.

  3. In Opzioni per la memoria del server immettere la stessa quantità da usare per Memoria minima per il server e Memoria massima per il server.Under Server Memory Options, enter the same amount that you want for Minimum server memory and Maximum server memory.

    Usare le impostazioni predefinite per consentire a SQL ServerSQL Server di modificare i requisiti di memoria in base alle risorse di sistema disponibili.Use the default settings to allow SQL ServerSQL Server to change its memory requirements dynamically based on available system resources. È consigliabile impostare il valore max server memory come descritto in dettaglio in precedenza.It is recommended to set a max server memory as detailed above.

Blocco di pagine in memoriaLock Pages in Memory (LPIM)

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.This Windows policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. Il blocco delle pagine in memoria può garantire il corretto funzionamento del server quando si verifica il paging della memoria su disco.Locking pages in memory may keep the server responsive when paging memory to disk occurs. L'opzione Blocco di pagine in memoria viene impostata su ON per istanze di SQL ServerSQL 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.The Lock Pages in Memory option is set to ON in instances of SQL ServerSQL Server Standard edition and higher when the account with privileges to run sqlservr.exe has been granted the Windows Lock Pages in Memory (LPIM) user right.

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 con i privilegi per l'esecuzione di sqlservr.exe (l'account di avvio di SQL ServerSQL Server).To disable the Lock Pages In Memory option for SQL ServerSQL Server, remove the Lock Pages in Memory user right for the account with privileges to run sqlservr.exe (the SQL ServerSQL Server startup account) startup account.

Questa opzione non influisce sulla gestione dinamica della memoria di SQL ServerSQL Server, consentendone l'espansione o la riduzione su richiesta di altri clerk di memoria.Setting this option does not affect SQL ServerSQL Server dynamic memory management, allowing it to expand or shrink at the request of other memory clerks. Quando si usa il diritto utente Blocco di pagine in memoria è consigliabile impostare un limite superiore per max server memory come descritto in dettaglio in precedenza.When using the Lock Pages in Memory user right it is recommended to set an upper limit for max server memory as detailed above.

Importante

L'impostazione di questa opzione deve essere usata solo quando necessario, ovvero 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: ##%. A partire da SQL Server 2012SQL Server 2012, il flag di traccia 845 non è necessario per l'uso del blocco di pagine nell'edizione Standard.Setting this option should only be used when necessary, namely if there are signs that sqlservr process is being paged out. In this case, error 17890 will be reported in the Errorlog, resembling the below example: 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: ##%. Starting with SQL Server 2012SQL Server 2012, trace flag 845 is not needed for Standard Edition to use Locked Pages.

Per abilitare l'opzione Blocco di pagine in memoriaTo enable Lock Pages in Memory

Per abilitare l'opzione Blocco di pagine in memoria:To enable the lock pages in memory option:

  1. Fare clic sul menu Start e scegliere Esegui.On the Start menu, click Run. Nella casella Apri digitare gpedit.msc.In the Open box, type gpedit.msc.

    Viene visualizzata la finestra di dialogo Criteri gruppo .The Group Policy dialog box opens.

  2. Nella console Criteri di gruppo espandere Configurazione computere quindi espandere Impostazioni di Windows.On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.

  3. Espandere Impostazioni sicurezzae quindi espandere Criteri locali.Expand Security Settings, and then expand Local Policies.

  4. Selezionare la cartella Assegnazione diritti utente .Select the User Rights Assignment folder.

    I criteri verranno visualizzati nel riquadro dei dettagli.The policies will be displayed in the details pane.

  5. Nel riquadro fare doppio clic su Blocco di pagine in memoria.In the pane, double-click Lock pages in memory.

  6. Nella finestra di dialogo Impostazioni criteri di sicurezza locali aggiungere l'account con i privilegi per l'esecuzione di sqlservr.exe (account di avvio di SQL ServerSQL Server).In the Local Security Policy Setting dialog box, add the account with privileges to run sqlservr.exe (the SQL ServerSQL Server startup account).

Esecuzione di più istanze di SQL ServerSQL ServerRunning multiple instances of SQL ServerSQL Server

Quando si eseguono più istanze di Motore di databaseDatabase Engine, è possibile gestire la memoria in tre modi:When you are running multiple instances of the Motore di databaseDatabase Engine, there are three approaches you can use to manage memory:

  • Usare max server memory per controllare l'utilizzo della memoria, come descritto in dettaglio in precedenza.Use max server memory to control memory usage, as detailed above. Stabilire le impostazioni massime per ogni istanza, accertandosi che il totale non sia superiore alla memoria fisica disponibile sul computer.Establish maximum settings for each instance, being careful that the total allowance is not more than the total physical memory on your machine. È possibile rendere la memoria di ogni istanza proporzionale al relativo carico di lavoro previsto o alle dimensioni del database.You might want to give each instance memory proportional to its expected workload or database size. Questo approccio presenta il vantaggio di rendere la memoria libera immediatamente disponibile ad ogni nuovo processo o istanza.This approach has the advantage that when new processes or instances start up, free memory will be available to them immediately. Lo svantaggio è che se non vengono eseguite tutte le istanze, parte della memoria resterà inusata.The drawback is that if you are not running all of the instances, none of the running instances will be able to utilize the remaining free memory.

  • Usare min server memory per controllare l'utilizzo della memoria, come descritto in dettaglio in precedenza.Use min server memory to control memory usage, as detailed above. 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.Establish minimum settings for each instance, so that the sum of these minimums is 1-2 GB less than the total physical memory on your machine. Anche questi minimi possono essere resi proporzionali al carico previsto dell'istanza.Again, you may establish these minimums proportionately to the expected load of that instance. Con questo approccio, quando non vengono eseguite tutte le istanze contemporaneamente, quelle in esecuzione potranno usare la memoria libera rimanente.This approach has the advantage that if not all instances are running at the same time, the ones that are running can use the remaining free memory. 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.This approach is also useful when there is another memory-intensive process on the computer, since it would insure that SQL ServerSQL Server would at least get a reasonable amount of memory. 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.The drawback is that when a new instance (or any other process) starts, it may take some time for the running instances to release memory, especially if they must write modified pages back to their databases to do so.

  • Non intervenire in alcun modo (non consigliato).Do nothing (not recommended). Le prime istanze sottoposte a carico di lavoro tenderanno ad allocare tutta la memoria.The first instances presented with a workload will tend to allocate all of memory. Alle istanze inattive o a quelle avviate in un secondo momento verrà destinata solo una minima quantità di memoria.Idle instances, or instances started later, may end up running with only a minimal amount of memory available. SQL ServerSQL Server non viene ripartita in alcun modo la memoria tra le diverse istanze. makes no attempt to balance memory usage across instances. Tutte le istanze, tuttavia, risponderanno ai segnali di Windows Memory Notification correggendo di conseguenza le dimensioni dei rispettivi footprint di memoria.All instances will, however, respond to Windows Memory Notification signals to adjust the size of their memory footprint. In Windows la memoria non viene bilanciata tra le applicazioni tramite l'API di Windows Memory Notification.Windows does not balance memory across applications with the Memory Notification API. Offre invece un semplice feedback globale sulla disponibilità di memoria nel sistema.It merely provides global feedback as to the availability of memory on the system.

    Poiché è possibile modificare queste impostazioni senza riavviare le istanze, sarà possibile provare agevolmente valori diversi fino a individuare quelli più adatti alle esigenze.You can change these settings without restarting the instances, so you can easily experiment to find the best settings for your usage pattern.

Assegnazione della quantità massima di memoria a SQL ServerProviding the maximum amount of memory to SQL Server

La memoria può essere configurata fino al limite dello spazio degli indirizzi virtuali del processo in tutte le edizioni di SQL ServerSQL Server.Memory can be configured up to the process virtual address space limit in all SQL ServerSQL Server editions. Per altre informazioni, vedere Memory Limits for Windows and Windows Server Releases (Limiti di memoria per le diverse versioni di Windows e Windows Server).For more information, see Memory Limits for Windows and Windows Server Releases.

EsempiExamples

Esempio AExample A

Nell'esempio seguente viene impostata l'opzione max server memory su 4 GB.The following example sets the max server memory option to 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 correnteExample B. Determining Current Memory Allocation

La query seguente restituisce le informazioni sulla memoria attualmente allocata.The following query returns information about currently allocated memory.

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;  

Vedere ancheSee Also

Guida sull'architettura di gestione della memoria Memory Management Architecture Guide
Monitoraggio e ottimizzazione delle prestazioni Monitor and Tune for Performance
RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
Opzioni di configurazione del server (SQL Server) Server Configuration Options (SQL Server)
sp_configure (Transact-SQL) sp_configure (Transact-SQL)
Opzioni di avvio del servizio del motore di database Database Engine Service Startup Options
Edizioni e le funzionalità supportate di SQL Server 2016 Editions and supported features of SQL Server 2016
Edizioni e funzionalità supportate di SQL Server 2017 Editions and supported features of SQL Server 2017
Edizioni e funzionalità supportate di SQL Server 2017 in Linux Editions and supported features of SQL Server 2017 on Linux
Memory Limits for Windows and Windows Server Releases (Limiti di memoria per le diverse versioni di Windows e Windows Server)Memory Limits for Windows and Windows Server Releases