Monitorare l'uso della memoria

  • Articolo

Si applica a: SQL Server

Monitorare periodicamente un'istanza di SQL Server per verificare che l'utilizzo della memoria rientri negli intervalli standard.

Configurare la memoria massima di SQL Server

Per impostazione predefinita, un'istanza di SQL Server può impiegare nel tempo la maggior parte della memoria del sistema operativo Windows disponibile nel server. Una volta acquisita la memoria, non verrà rilasciata a meno che non venga rilevata una pressione di memoria. Questa è un’operazione progettata e non indica una perdita di memoria nel processo di SQL Server. Usare l'opzione max server memory per limitare la quantità di memoria che SQL Server può acquisire per la maggior parte degli usi. Per altre informazioni vedere Guida sull'architettura di gestione della memoria.

In SQL Server in Linux impostare il limite di memoria con lo strumento mssql-conf e l'impostazione memory.memorylimitmb.

Monitorare la memoria del sistema operativo

Per monitorare la quantità di memoria disponibile, usare i seguenti contatori Windows server: È possibile eseguire query su molti contatori di memoria del sistema operativo tramite le viste a gestione dinamica sys.dm_os_process_memory e sys.dm_os_sys_memory.

  • Memoria: Byte disponibili
    Questo contatore indica il numero di byte di memoria disponibili per i processi. I valori bassi per il contatore Byte disponibili possono indicare una carenza complessiva di memoria del sistema operativo. Questo valore può essere sottoposto a query tramite T-SQL con sys.dm_os_sys_memory.available_physical_memory_kb.

  • Memoria: Pagine/sec
    Questo contatore indica il numero di pagine richiamate dal disco o scritte su disco per liberare spazio nel set di lavoro in seguito a errori di pagina. Un valore elevato del contatore Pagine/sec può indicare un paging eccessivo.

  • Memoria: Errori di pagina/sec Questo contatore indica la frequenza di errori di pagina per tutti i processi, inclusi i processi di sistema. Anche nei computer con una notevole quantità di memoria disponibile, la frequenza di paging su disco, e quindi di errori di pagina, dovrebbe essere bassa ma non uguale a zero. Microsoft Windows Virtual Memory Manager (VMM) sottrae pagine a SQL Server e agli altri processi in quanto riduce le dimensioni dei set di lavoro di tali processi. L'attività di VMM tende quindi a causare errori di pagina.

  • Processo: Errori di pagina/sec Questo contatore indica la frequenza di errori di pagina per un determinato processo utente. Monitorare Processo di monitoraggio: Errori di pagina/sec per stabilire se l'attività del disco è causata dal paging da SQL Server. Per determinare se SQL Server o un altro processo provoca un paging eccessivo, monitorare il contatore Processo: Errori di pagina/sec alla ricerca dell'istanza del processo SQL Server.

Per altre informazioni su come evitare il paging eccessivo, vedere la documentazione del sistema operativo.

Isolare la memoria usata da SQL Server

Per monitorare l'utilizzo della memoria di SQL Server, usare i seguenti contatori degli oggetti di SQL Server. È possibile eseguire query su molti contatori di oggetti di SQL Server tramite le viste a gestione dinamica sys.dm_os_performance_counters o sys.dm_os_process_memory.

Per impostazione predefinita, SQL Server gestisce i requisiti di memoria dinamicamente in base alle risorse di sistema disponibili. Se SQL Server necessita di una maggior quantità di memoria, richiede al sistema operativo di determinare se è disponibile memoria fisica e utilizza la memoria disponibile. Se la memoria disponibile per il sistema operativo non è sufficiente, SQL Server libererà memoria per il sistema operativo finché la condizione di memoria insufficiente non sarà stata risolta o finché SQL Server non avrà raggiunto il limite memoria server min. È tuttavia possibile ignorare l'opzione per l'uso dinamico della memoria specificando le opzioni di configurazione del server memoria server mine memoria server max. Per altre informazioni, vedere Opzioni per la memoria server.

Per monitorare la quantità di memoria usata da SQL Server, esaminare i contatori delle prestazioni seguenti:

  • SQL Server: Gestione memoria: Memoria totale server (KB)
    Questo contatore indica la quantità di memoria del sistema operativo dove è attualmente stato eseguito il commit in SQL Server. Questo numero dovrebbe aumentare in base alle esigenze dell'attività effettiva e aumenterà dopo l'avvio di SQL Server. Eseguire una query su questo contatore usando la vista a gestione dinamica sys.dm_os_sys_info nella colonna committed_kb .

  • SQL Server: Gestione memoria: Memoria server di destinazione (KB)
    Questo contatore indica una quantità ideale di memoria che SQL Server potrebbe impiegare sulla base del carico di lavoro recente. Confrontare con Memoria totale del server dopo un periodo di operazione tipica per stabilire se una quantità di memoria desiderata è allocata in SQL Server. Dopo l'operazione tipica, la memoria totale del server e la memoria del server di destinazione devono essere simili. Se la memoria totale del server è notevolmente inferiore alla memoria del server di destinazione, si potrebbe riscontrare un utilizzo elevato di memoria nell'istanza di SQL Server. Dopo l'avvio di SQL Server, la memoria totale del server dovrebbe essere inferiore alla memoria del server di destinazione, mentre aumenta la memoria totale del server. Eseguire una query su questo contatore usando la vista a gestione dinamica sys.dm_os_sys_info nella colonna committed_target_kb. Per altre informazioni e procedure consigliate per la configurazione della memoria, vedere Opzioni di configurazione della memoria del server.

  • Processo: Working set
    Questo contatore indica la quantità di memoria fisica attualmente utilizzata da un processo in base al sistema operativo. Notare l'istanza sqlservr.exe di questo contatore. Eseguire una query su questo contatore usando la vista a gestione dinamica sys.dm_os_process_memory nella colonna physical_memory_in_use_kb.

  • Processo: Byte privati
    Questo contatore indica la quantità di memoria richiesta al sistema operativo per eseguire un processo. Notare l'istanza sqlservr.exe di questo contatore. Dato che questo contatore include tutte le allocazioni di memoria richieste da sqlservr.exe, tra cui quelle non limitate dall'opzione max server memory, è possibile che vengano segnalati valori superiori all'opzione max server memory.

  • SQL Server: Gestione buffer: Pagine database
    Questo contatore indica il numero di pagine con contenuto di database nel pool di buffer. Non include altra memoria del pool nonbuffer all'interno del processo di SQL Server. Eseguire una query su questo contatore usando la visualizzazione a gestione dinamica sys.dm_os_performance_counters.

  • SQL Server: Gestione buffer: Percentuale riscontri cache buffer
    Questo contatore è specifico per SQL Server. È preferibile un rapporto di 90 o superiore. Un valore superiore a 90 indica che è stato soddisfatto oltre il 90% di tutte le richieste di dati dalla cache dei dati in memoria senza doverli leggere dal disco. Per altre informazioni su Gestione buffer di SQL Server, vedere Oggetto Gestione buffer di SQL Server. Eseguire una query su questo contatore usando la visualizzazione a gestione dinamica sys.dm_os_performance_counters.

  • SQL Server, Gestione buffer: Permanenza presunta delle pagine
    Questo contatore misura la quantità di tempo in secondi in cui la pagina meno recente rimane nel pool di buffer. Per i sistemi che usano un'architettura NUMA, si tratta della media tra tutti i nodi NUMA. È meglio un valore più elevato e crescente. Un calo improvviso rappresenta una varianza significativa dei dati all'interno e all'esterno del pool di buffer, e indica che il carico di lavoro non è riuscito a trarre vantaggio dai dati già in memoria. Ogni nodo NUMA ha un proprio nodo del pool di buffer. Nei server con più nodi NUMA visualizzare la permanenza presunta di ogni nodo del pool di buffer usando SQL Server: Nodo buffer: Permanenza presunta delle pagine. Eseguire una query su questo contatore usando la visualizzazione a gestione dinamica sys.dm_os_performance_counters.

Esempi

Determinare l'allocazione di memoria corrente

Le query seguenti restituiscono le informazioni sulla memoria attualmente allocata.

SELECT
(total_physical_memory_kb/1024) AS Total_OS_Memory_MB,
(available_physical_memory_kb/1024)  AS Available_OS_Memory_MB
FROM sys.dm_os_sys_memory;

SELECT  
(physical_memory_in_use_kb/1024) AS Memory_used_by_Sqlserver_MB,  
(locked_page_allocations_kb/1024) AS Locked_pages_used_by_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;

Determinare l'utilizzo attuale della memoria di SQL Server

La query seguente restituisce le informazioni sull’utilizzo attuale della memoria SQL Server.

SELECT
sqlserver_start_time,
(committed_kb/1024) AS Total_Server_Memory_MB,
(committed_target_kb/1024)  AS Target_Server_Memory_MB
FROM sys.dm_os_sys_info;

Determinare la permanenza presunta delle pagine

La query seguente usa sys.dm_os_performance_counters per osservare il valore permanenza presunta della pagina corrente dell'istanza di SQL Server a livello di gestione buffer complessivo e a ogni livello di nodo NUMA.

SELECT
CASE instance_name WHEN '' THEN 'Overall' ELSE instance_name END AS NUMA_Node, cntr_value AS PLE_s
FROM sys.dm_os_performance_counters    
WHERE counter_name = 'Page life expectancy';

Contenuto correlato