Diagnostiquer les allocations mémoire SQL Server

Un rapport peut être rapide seul et provoquer une file d'attente lorsque plusieurs utilisateurs le lancent ensemble. Une explication possible est la mémoire de travail réservée pour les tris et les opérations de hachage. Mesurer uniquement une exécution isolée masque la quantité immobilisée et le délai imposé aux autres requêtes.

Observer les demandes pendant l'incident

Collectez les informations avant la fin des rapports. La requête suivante rapproche les demandes mémoire actives des requêtes courantes. Elle ne modifie rien, mais la visibilité serveur exige les droits adaptés. Cette DMV nécessite VIEW SERVER PERFORMANCE STATE à partir de SQL Server 2022 et généralement VIEW SERVER STATE sur les versions antérieures.

SELECT
    mg.session_id,
    mg.request_id,
    mg.request_time,
    mg.grant_time,
    mg.requested_memory_kb,
    mg.granted_memory_kb,
    mg.required_memory_kb,
    mg.used_memory_kb,
    mg.max_used_memory_kb,
    mg.wait_time_ms,
    r.status,
    r.wait_type,
    r.total_elapsed_time,
    txt.text AS batch_text
FROM sys.dm_exec_query_memory_grants AS mg
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = mg.session_id
   AND r.request_id = mg.request_id
OUTER APPLY sys.dm_exec_sql_text(mg.sql_handle) AS txt
ORDER BY mg.requested_memory_kb DESC;

Le texte affiché correspond au lot entier. Une procédure peut donc contenir plusieurs instructions sans rapport avec l'allocation étudiée. Utilisez les positions de l'instruction dans la requête ou le plan pour préciser l'analyse. Conservez ensemble les identifiants de session et de requête ; une même session peut effectuer un autre travail à l'observation suivante.

Une valeur grant_time absente indique une allocation encore attendue. Comparez les kilooctets demandés et accordés avec l'utilisation actuelle et maximale observée. Il s'agit d'un état vivant, pas d'un historique terminé. Une requête à peine démarrée peut normalement utiliser peu de sa réservation. Prenez plusieurs échantillons horodatés.

RESOURCE_SEMAPHORE concerne l'admission liée à la mémoire d'exécution. RESOURCE_SEMAPHORE_QUERY_COMPILE concerne la compilation. Examinez aussi les requêtes qui possèdent déjà de grandes allocations. Celle qui attend peut être la victime d'une autre qui occupe l'espace disponible.

Distinguer trois mécanismes

Une allocation excessive réserve beaucoup plus que nécessaire. Une allocation insuffisante peut pousser un tri ou un hachage à déborder vers tempdb. Une file peut également apparaître avec des allocations individuelles raisonnables lorsque trop d'opérations volumineuses se chevauchent. Un pourcentage global de mémoire ne permet pas de choisir entre ces explications.

Imaginez un rapport qui demande 600 Mo et n'en utilise régulièrement que 25. Vérifiez le nombre de lignes estimé et leur largeur avant les opérateurs gourmands. Ne concluez pas à un gaspillage à partir d'un seul échantillon précoce. Confirmez avec les informations du plan réel terminé et avec les paramètres produisant les plus gros résultats.

Pour un débordement, comparez les entrées estimées et réelles de l'opérateur concerné. Un résultat de jointure sous-estimé peut gonfler fortement une table de hachage. Des colonnes inutiles peuvent rendre coûteux un nombre modéré de lignes. Réduire la projection avant un tri peut aider, à condition de préserver exactement le résultat demandé.

Un index fournissant l'ordre voulu peut parfois supprimer le tri. Une préagrégation au bon niveau peut réduire les lignes d'une jointure. Ces changements ont des contreparties : coût des écritures pour l'index, risque de sommes incorrectes pour une réécriture mal conçue. Vérifiez la sémantique avant de célébrer la baisse mémoire.

Mesurer la concurrence réelle

Exécutez le mélange représentatif de rapports en parallèle. Mesurez les requêtes terminées par minute et les latences élevées, pas seulement la durée moyenne individuelle. Une réservation plus petite peut améliorer l'admission tout en augmentant les accès tempdb et en dégradant le débit total.

Memory Grant Feedback peut ajuster les allocations entre exécutions dans les versions et modes pris en charge. Son comportement et sa persistance dépendent de la version et de la configuration. Observez ses effets sans supposer que la première exécution ou une nouvelle compilation sera forcément bien dimensionnée.

Évitez de commencer par des hints généralisés ou une hausse globale de mémoire serveur. Identifiez d'abord estimation, largeur, ordre ou chevauchement excessif. Décaler plusieurs exports lourds peut résoudre la pression plus efficacement que réduire chaque allocation.

Terminez avec des mesures prises sur une fenêtre comparable : moins de demandes en attente, débordements acceptables, débit stable et résultats corrects. Gardez les plans et les horodatages pour expliquer si la modification a réduit le besoin, corrigé l'estimation ou déplacé l'attente vers une autre ressource.

Références techniques: Microsoft Learn: Memory grant diagnostics · Microsoft Learn: sys.dm_exec_query_memory_grants · Microsoft Learn: Memory grant feedback.