Ottimizza il consumo elevato di memoria nelle istanze

Questo documento spiega come esaminare e ottimizzare un'istanza Cloud SQL per SQL Server se viene identificata dal motore per suggerimenti di istanze sottodimensionate come con un elevato consumo di memoria.

Memoria di SQL Server

La memoria di SQL Server può essere suddivisa come segue:

Cache

Si tratta di oggetti su un disco che possono essere ricaricati, come pagine di database e stored procedure. Di conseguenza, SQL Server può aumentare e ridurre le dimensioni di questi oggetti in base all'utilizzo della memoria. Le cache includono pool di buffer e cache dei piani.

Memoria fissa

La memoria fissa può aumentare e diminuire. Si riduce solo quando non è in uso, ad esempio quando il numero di connessioni diminuisce o il numero di query in esecuzione diminuisce. È diverso dalle cache. Se la memoria fissa non è sufficiente, SQL Server può esaurire la memoria. La memoria fissa include la memoria di connessione e le concessioni di memoria.

Oneri di SQL Server

Il sovraccarico di SQL Server include thread e stack.

OLTP in memoria

L'OLTP in memoria include tabelle in memoria e gruppi di file in memoria.

Il consumo di memoria da parte di SQL Server è controllato impostando maximum server memory e memory.memory.limitmb. Il parametro memory.memory.limitmb viene impostato automaticamente da Cloud SQL.

Per scoprire di più su memory.memory.limitmb, consulta la documentazione di Microsoft.

Devi impostare il limite max server memory su un valore appropriato. In SQL Server, se max server memory non è impostato, le pagine del database possono consumare la maggior parte della memoria fino al 100%. A volte questo può essere fuorviante.

Opzioni di ottimizzazione della memoria

Per determinare se un'istanza ha bisogno di una maggiore ottimizzazione della memoria:

  • Imposta il valore di max server memory. Il consiglio generale è impostare max server memory su circa l'80% per impedire a SQL Server di consumare tutta la memoria disponibile. Per le istanze con grandi quantità di memoria, l'80% potrebbe essere troppo basso e potrebbe comportare uno spreco di memoria.

  • Monitor Page life expectancy

    Page life expectancy indica il tempo, in secondi, che la pagina meno recente rimane nel pool di buffer. Questo valore deve essere superiore a 300, come consigliato da Microsoft. Se scende costantemente al di sotto di 300, potrebbe indicare che l'istanza ha un utilizzo elevato della memoria. Esegui la seguente query per monitorare Page life expectancy.

    SELECT 
  [object_name],
  [counter_name],
  [cntr_value] 
FROM 
  sys.dm_os_performance_counters
WHERE 
  [object_name] 
LIKE 
  '%Manager%'
AND 
  [counter_name] = 'Page life expectancy'

  • Controlla Memory Grants Pending

    Memory Grants Pending specifica il numero totale di processi in attesa di una concessione di memoria dello spazio di lavoro. Esegui la seguente query per controllare Memory Grants Pending. Se questa query mostra costantemente concessioni in attesa, indica un utilizzo elevato della memoria. Puoi ridurlo eseguendo query sulle attese del database e ottimizzando qualsiasi istruzione in attesa nella memoria.

    SELECT
  @@SERVERNAME AS [Server Name],
  RTRIM([object_name]) AS [Object Name],
  cntr_value AS [Memory Grants Pending]
FROM 
  sys.dm_os_performance_counters WITH(NOLOCK)
WHERE
  [object_name] 
LIKE 
  N'%Memory Manager%'  -- Handles named instances
AND 
  counter_name = N'Memory Grants Pending'