Optimiser la consommation de mémoire élevée dans les instances

Ce document explique comment examiner et optimiser une instance Cloud SQL pour SQL Server si cette instance est identifiée par l'outil de recommandation d'instances sous-provisionnées comme ayant une consommation élevée de mémoire.

Mémoire SQL Server

La mémoire SQL Server peut être divisée en plusieurs parties comme suit :

Caches

Il s'agit d'objets sur un disque qui peuvent être actualisés, tels que des pages de base de données et des procédures stockées. Par conséquent, SQL Server peut augmenter ou réduire ces objets en fonction de l'utilisation de la mémoire. Les caches incluent les pools de tampons et les caches de plans.

Mémoire fixe

La mémoire fixe peut augmenter ou diminuer. Elle ne diminue que lorsqu'elle n'est pas utilisée. Par exemple, lorsque le nombre de connexions ou le nombre de requêtes en cours d'exécution diminue. Cela diffère des caches. Si la mémoire fixe est insuffisante, SQL Server peut manquer de mémoire. La mémoire fixe inclut la mémoire de connexion et les allocations de mémoire.

Coûts SQL Server

Les coûts liés à SQL Server incluent les threads et les piles.

OLTP en mémoire

OLTP en mémoire inclut les tables et les groupes de fichiers en mémoire.

La consommation de mémoire par SQL Server est contrôlée en définissant maximum server memory et memory.memory.limitmb. Le paramètre memory.memory.limitmb est défini automatiquement par Cloud SQL.

Pour en savoir plus sur memory.memory.limitmb, consultez la documentation Microsoft.

Vous devez définir la limite max server memory sur une valeur appropriée. Dans SQL Server, si max server memory n'est pas défini, les pages de base de données peuvent consommer la majeure partie de la mémoire proche et jusqu'à 100 %. Cela peut parfois être trompeur.

Options d'optimisation de la mémoire

Pour déterminer si une instance nécessite davantage de réglages de mémoire, procédez comme suit :

Définissez la valeur de max server memory. Il est généralement recommandé de définir max server memory sur 80 % pour empêcher SQL Server de consommer toute la mémoire disponible. Dans le cas d'instances disposant d'une grande quantité de mémoire, une valeur de 80 % peut être trop faible et entraîner un gaspillage de mémoire.
Surveiller Page life expectancy

Page life expectancy indique la durée, en secondes, pendant laquelle la page la plus ancienne reste dans le pool de mémoire tampon. Cette valeur doit être supérieure à 300, comme recommandé par Microsoft. Si elle est systématiquement inférieure à 300, cela peut indiquer que l'instance est soumise à une utilisation intensive de la mémoire. Exécutez la requête suivante pour surveiller 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'
```
Vérifier Memory Grants Pending

Memory Grants Pending spécifie le nombre total de processus en attente d'une allocation de mémoire d'espace de travail. Exécutez la requête suivante pour vérifier Memory Grants Pending. Si cette requête affiche systématiquement les allocations en attente, cela indique une utilisation intensive de la mémoire. Vous pouvez la réduire en interrogeant les bases de données et en ajustant les instructions en attente de mémoire.
```
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'
```