Come posso risolvere i problemi di memoria libera insufficiente nella mia istanza RDS per SQL Server?

Risoluzione

Monitoraggio dell'utilizzo della memoria nell'istanza

Per monitorare l'utilizzo della memoria, puoi utilizzare le metriche di Amazon CloudWatch o Monitoraggio avanzato.

Utilizza le metriche di CloudWatch

Per stabilire se la memoria è insufficiente, monitora la metrica FreeableMemory di CloudWatch utilizzando la console Amazon RDS.

Per identificare un aumento del carico di lavoro quando la memoria disponibile è insufficiente, monitora le seguenti metriche:

• DatabaseConnections
• CPUUtilization
• ReadIOPS
• ReadThroughput
• WriteIOPS
• WriteThroughput

Per informazioni sulle metriche precedenti, consulta Metriche di Amazon CloudWatch a livello di istanza per Amazon RDS.

Attiva Monitoraggio avanzato

Per monitorare le metriche del sistema operativo per Microsoft SQL Server, utilizza Monitoraggio avanzato. Quando attivi Monitoraggio avanzato, puoi impostare l'intervallo di raccolta delle metriche su 1, 5, 10, 15, 30 o 60 secondi. L'impostazione predefinita è una granularità di 60 secondi, ma è consigliabile impostare la granularità su 1 o 5 secondi.

Puoi anche utilizzare Monitoraggio avanzato per creare allarmi CloudWatch che ti consentano di monitorare l'utilizzo della memoria dell'istanza database Amazon RDS per SQL Server.

Limita la memoria utilizzata dall'istanza Amazon RDS

Determina il valore max_server_memory per l'istanza, quindi scegli un valore di impostazione di max_server_memory che non causi una pressione sulla memoria a livello di sistema.

Determina il valore max_server_memory

Per determinare il valore max_server_memory per l'istanza, utilizza il seguente calcolo:

max_server_memory = total_RAM - (1 GB per il sistema operativo + memory_basis_amount_of_RAM_on_the_server)

total_RAM è la memoria totale del tipo di istanza.

memory_basis_amount_of_RAM_on_the_server è determinato come segue:

• Se la RAM del server è compresa tra 4 GB e 16 GB, mantieni 1 GB ogni 4 GB di RAM. Ad esempio, mantieni 4 GB per un server con 16 GB di RAM.

• Se la RAM del server supera i 16 GB, mantieni 1 GB ogni 4 GB di RAM fino a 16 GB. Mantieni 1 GB ogni 8 GB di RAM oltre i 16 GB.

Ad esempio, se un server dispone di 64 GB di RAM, il calcolo di max_server_memory è il seguente:

• 1 GB per il sistema operativo
• Fino a 16 GB di RAM: 16 / 4 = 4 GB
• RAM restante oltre i 16 GB: (64 - 16) / 8 = 6 GB
• (1 GB per il sistema operativo + memory_basis_amount_of_RAM_on_the_server) = 1 + 4 + 6 = 11 GB
• max_server_memory: 64 - 11 = 53 GB

Imposta max_server_memory

Per modificare max_server_memory, configura il valore utilizzando un gruppo di parametri personalizzato. Indica il valore max_server_memory in Max server memory (MB) (Memoria massima del server (MB)). Poiché max_server_memory è un parametro dinamico, non serve riavviare per rendere effettive le modifiche.

Nota: dopo aver configurato max_server_memory, devi monitorare continuamente FreeableMemory per stabilire se aumentare o diminuire la memoria allocata.

Verifica se utilizzi le opzioni SSIS, SSAS o SSRS nell'istanza database

Controlla il gruppo di opzioni RDS per stabilire se utilizzi le opzioni SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS) o SQL Server Reporting Services (SSRS) nell'istanza database. La memoria utilizzata dalle opzioni esiste al di fuori dell'impostazione max_server_memory e aumenta l'ingombro di memoria dell'istanza. Se non utilizzi queste opzioni, modifica il gruppo di opzioni per rimuoverle.

Nota: se utilizzi SSIS, SSAS o SSRS nell'istanza, devi regolare max_server_memory in modo che tenga conto delle opzioni.

Ad esempio, se utilizzi SSRS, imposta il valore Memoria massima SSRS sul 10% della memoria totale dell'istanza database. Ad esempio, il 10% per un'istanza con 64 GiB di memoria equivale a circa 6,4 GiB. Quindi regola il valore max_server_memory approssimativamente su 46 GiB (64 - 11 - 6,4 = 46 GiB).

Controlla le connessioni al database

Ogni connessione all'istanza database richiede un'allocazione di memoria al di fuori del pool di buffer per i thread di worker. Pertanto, un picco di DatabaseConnections può causare un calo della memoria liberabile.

Monitora l'istanza database con Performance Insights

Puoi utilizzare Performance Insights per analizzare le prestazioni del database e identificare i potenziali colli di bottiglia che rallentano l'istanza. Utilizza la dashboard di Performance Insights per monitorare il carico del database, le attese, le query, gli host e gli utenti.

Esegui una manutenzione regolare dell'istanza database

Esegui una manutenzione regolare dell'indice e mantieni aggiornate le statistiche. Gli indici molto frammentati possono aumentare l'attività di I/O e causare un maggiore utilizzo della memoria. Inoltre, statistiche obsolete possono comportare una stima imprecisa della cardinalità e indurre il database a selezionare un piano di query non ottimale. Per ulteriori informazioni, consulta Ottimizzare la manutenzione dell'indice per migliorare le prestazioni delle query e ridurre il consumo di risorse sul sito web Microsoft. Inoltre, consulta UPDATE STATISTICS (Transact-SQL) sul sito web Microsoft.

Nota: è consigliabile eseguire la manutenzione degli indici e delle statistiche durante le ore non di punta o durante una finestra di manutenzione.

Monitora i valori PLE e BCHR

Per identificare la pressione sulla memoria, monitora i valori Page Life Expectancy (PLE) e Buffer Cache Hit Ratio (BCHR). Per prestazioni ottimali, verifica che i valori PLE e BCHR siano quanto più alti possibile. Se i valori PLE e BCHR sono costantemente bassi per un periodo di tempo, ottimizza le query che accedono ai dati o aumenta la classe dell'istanza per fornire più memoria.

Per monitorare le metriche utilizzando Performance Insights, completa i seguenti passaggi:

1. Apri la console Amazon RDS.
2. Nel pannello di navigazione scegli Performance Insights.
3. Utilizza il campo di ricerca Filtra un'istanza database per selezionare l'istanza da monitorare.
4. Imposta l'intervallo di tempo per il quale desideri esaminare le metriche.
5. Nella Dashboard dei parametri seleziona Custom dashboard - SQL Server database (Dashboard personalizzata - Database SQL Server), quindi scegli Aggiungi il primo widget.
6. Nel campo di ricerca Filtra i parametri per nome, categoria o ID cerca la metrica Page Life Expectancy e selezionala.
7. Scegli Aggiungi widget.
8. Ripeti i passaggi 6 e 7 per selezionare la metrica Buffer Cache Hit Ratio per la dashboard personalizzata.

Per ulteriori informazioni sulle metriche PLE e BCHR, consulta Oggetti prestazioni di Gestione buffer sul sito web Microsoft.

Quando c'è una pressione sulla memoria dell'istanza e i valori PLE e BCHR sono bassi, l'attesa PAGEIOLATCH aumenta. Microsoft SQL Server è in attesa del caricamento di una pagina dal disco in memoria. Quando la richiesta di memoria di una query ha esito negativo perché la memoria è insufficiente, potresti anche vedere l'attesa RESOURCE_SEMAPHORE. In tal caso, l'utilizzo della CPU aumenta perché le pagine di dati non vengono memorizzate nella cache abbastanza a lungo. Microsoft SQL Server deve dunque accedere ripetutamente ai dati sul disco.

Seleziona la dimensione dell'istanza corretta per il carico di lavoro

La quantità di memoria di un'istanza dipende dal tipo di istanza. Assicurati di selezionare una classe di istanza con risorse sufficienti in modo che l'istanza database disponga di risorse sufficienti per il carico di lavoro. Quando un'istanza dispone di meno risorse, si verificano problemi di prestazioni. Un'istanza sovradimensionata spreca risorse.

Ad esempio, la classe di istanza db.r5.8xlarge fornisce 32 vCPU e 256 GiB di memoria. Quando esegui il provisioning di un'istanza Amazon RDS con db.r5.8xlarge, le seguenti risorse condividono tutti i 256 GiB di memoria nella classe di istanza:

• Sistema operativo
• Processi Amazon RDS
• Motore di database
• Thread di worker
• Applicazioni della suite di Business Intelligence, come SSIS, SSAS e SSRS.

Per ulteriori informazioni sull'utilizzo della memoria da parte di Microsoft SQL Server, consulta Guida all'architettura di gestione della memoria sul sito web Microsoft.

Pianifica l'utilizzo delle risorse

Per stimare l'utilizzo delle risorse nell'istanza, monitora metriche come FreeableMemory, Page Life Expectancy e Buffer Cache Hit Ratio. Se il volume di dati dell'istanza cresce in modo significativo, aumenta il valore max_server_memory. Assicurati di aumentare il valore max_server_memory proporzionalmente alla variazione del volume di dati per mantenere lo stesso livello di prestazioni dell'istanza.

Nota: per identificare i componenti che utilizzano la memoria all'interno di SQL Server, puoi utilizzare gli strumenti di SQL Server, come Report e DMV.

Per esaminare l'utilizzo della memoria di SQL Server tramite SQL Server Management Studio (SSMS), completa i seguenti passaggi:

1. Apri SSMS e connettiti all'istanza Amazon RDS per SQL Server.
2. In Object Explorer (Esplora oggetti) fai clic con il pulsante destro del mouse sul nome dell'endpoint dell'istanza Amazon RDS.
3. Scegli Report, Report standard, quindi Consumo di memoria.

Per scaricare SSMS, consulta Installare SQL Server Management Studio sul sito web Microsoft.

Puoi inoltre eseguire query su sys.dm_os_memory_clerks per identificare i componenti che utilizzano la memoria massima all'interno di SQL Server. Per ulteriori informazioni, consulta sys.dm_os_memory_clerks (Transact-SQL) e Utilizzo interno della memoria da parte del motore di SQL Server sul sito web Microsoft.