Résoudre les problèmes de performances lentes ou de mémoire insuffisante causés par les allocations de mémoire dans SQL Server
Que sont les allocations de mémoire ?
Les allocations de mémoire, également appelées réservations d’exécution de requête (QE), mémoire d’exécution de requête, mémoire d’espace de travail et réservations de mémoire, décrivent l’utilisation de la mémoire au moment de l’exécution de la requête. SQL Server alloue cette mémoire pendant l’exécution de la requête pour un ou plusieurs des objectifs suivants :
- Opérations de tri
- Opérations de hachage
- Opérations de copie en bloc (problème non courant)
- Création d’index, y compris l’insertion dans des index COLUMNSTORE, car les tables/dictionnaires de hachage sont utilisés lors de l’exécution pour la génération d’index (problème non courant)
Pour fournir un certain contexte, pendant sa durée de vie, une requête peut demander de la mémoire à différents allocateurs ou commis de mémoire en fonction de ce qu’elle doit faire. Par exemple, lorsqu’une requête est analysée et compilée initialement, elle consomme de la mémoire de compilation. Une fois la requête compilée, cette mémoire est libérée et le plan de requête résultant est stocké dans la mémoire du cache du plan. Une fois qu’un plan est mis en cache, la requête est prête pour l’exécution. Si la requête effectue des opérations de tri, des opérations de correspondance de hachage (JOIN ou agrégats) ou des insertions dans des index COLUMNSTORE, elle utilise la mémoire de l’allocateur d’exécution de requête. Initialement, la requête demande cette mémoire d’exécution et, plus tard, si cette mémoire est accordée, la requête utilise tout ou partie de la mémoire pour les résultats de tri ou les compartiments de hachage. Cette mémoire allouée pendant l’exécution de la requête est ce que l’on appelle les allocations de mémoire. Comme vous pouvez l’imaginer, une fois l’opération d’exécution de requête terminée, l’allocation de mémoire est libérée à SQL Server à utiliser pour d’autres travaux. Par conséquent, les allocations d’allocation de mémoire sont temporaires par nature, mais peuvent encore durer longtemps. Par exemple, si une exécution de requête effectue une opération de tri sur un ensemble de lignes très volumineux en mémoire, le tri peut prendre plusieurs secondes ou minutes, et la mémoire accordée est utilisée pendant la durée de vie de la requête.
Exemple de requête avec allocation de mémoire
Voici un exemple de requête qui utilise la mémoire d’exécution et son plan de requête montrant l’octroi :
SELECT *
FROM sys.messages
ORDER BY message_id
Cette requête sélectionne un ensemble de lignes de plus de 300 000 lignes et le trie. L’opération de tri induit une demande d’allocation de mémoire. Si vous exécutez cette requête dans SSMS, vous pouvez afficher son plan de requête. Lorsque vous sélectionnez l’opérateur le plus SELECT à gauche du plan de requête, vous pouvez afficher les informations d’allocation de mémoire pour la requête (appuyez sur F4 pour afficher Propriétés) :
En outre, si vous cliquez avec le bouton droit dans l’espace blanc du plan de requête, vous pouvez choisir Afficher le code XML du plan d’exécution... et rechercher un élément XML qui affiche les mêmes informations d’allocation de mémoire.
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="41232" RequiredMemory="5248" DesiredMemory="46016" RequestedMemory="46016" GrantWaitTime="0" GrantedMemory="46016" MaxUsedMemory="45816" MaxQueryMemory="277688" LastRequestedMemory="0" IsMemoryGrantFeedbackAdjusted="No: First Execution" />
Plusieurs termes doivent être expliqués ici. Une requête peut nécessiter une certaine quantité de mémoire d’exécution (DesiredMemory) et demanderait généralement cette quantité (RequestedMemory). Au moment de l’exécution, SQL Server accorde tout ou partie de la mémoire demandée en fonction de la disponibilité (GrantedMemory). En fin de compte, la requête peut utiliser plus ou moins de la mémoire initialement demandée (MaxUsedMemory). Si l’optimiseur de requête a surestimé la quantité de mémoire nécessaire, il utilise moins que la taille demandée. Mais cette mémoire est perdue, car elle aurait pu être utilisée par une autre requête. En revanche, si l’optimiseur a sous-estimé la taille de la mémoire nécessaire, les lignes excédentaires peuvent être déversées sur le disque pour effectuer le travail au moment de l’exécution. Au lieu d’allouer plus de mémoire que la taille initialement demandée, SQL Server envoie les lignes supplémentaires sur le disque et l’utilise comme espace de travail temporaire. Pour plus d’informations, consultez Workfiles and Worktables in Memory Grant Considerations.
Terminologie
Examinons les différents termes que vous pouvez rencontrer concernant ce consommateur de mémoire. Là encore, tous ces concepts décrivent les mêmes allocations de mémoire.
Mémoire d’exécution des requêtes (mémoire QE) : Ce terme est utilisé pour mettre en évidence le fait que la mémoire de tri ou de hachage est utilisée pendant l’exécution d’une requête. Généralement, la mémoire QE est le plus grand consommateur de mémoire pendant la durée d’une requête.
Réservations d’exécution de requête (QE) ou réservations de mémoire : Lorsqu’une requête a besoin de mémoire pour les opérations de tri ou de hachage, elle effectue une demande de réservation pour la mémoire. Cette demande de réservation est calculée au moment de la compilation en fonction de la cardinalité estimée. Plus tard, lorsque la requête s’exécute, SQL Server accorde cette requête partiellement ou entièrement en fonction de la disponibilité de la mémoire. En fin de compte, la requête peut utiliser un pourcentage de la mémoire accordée. Il existe un commis de mémoire (comptable de la mémoire) nommé « MEMORYCLERK_SQLQERESERVATIONS » qui effectue le suivi de ces allocations de mémoire (case activée dbCC MEMORYSTATUS ou sys.dm_os_memory_clerks).
Allocations de mémoire : Quand SQL Server accorde la mémoire demandée à une requête en cours d’exécution, il est dit qu’une allocation de mémoire s’est produite. Il existe quelques compteurs de performances qui utilisent le terme « octroi ». Ces compteurs,
Memory Grants OutstandingetMemory Grants Pending, affichent le nombre d’allocations de mémoire satisfaites ou en attente. Ils ne représentent pas la taille de l’allocation de mémoire. Une seule requête aurait pu consommer, par exemple, 4 Go de mémoire pour effectuer un tri, mais cela n’est pas reflété dans l’un ou l’autre de ces compteurs.La mémoire de l’espace de travail est un autre terme qui décrit la même mémoire. Souvent, vous pouvez voir ce terme dans le compteur
Granted Workspace Memory (KB)Perfmon , qui reflète la quantité globale de mémoire actuellement utilisée pour les opérations de tri, de hachage, de copie en bloc et de création d’index, exprimée en Ko. LeMaximum Workspace Memory (KB), un autre compteur, prend en compte la quantité maximale de mémoire de l’espace de travail disponible pour toutes les demandes qui peuvent avoir besoin d’effectuer des opérations de hachage, de tri, de copie en bloc et de création d’index. Le terme Mémoire de l’espace de travail est rarement rencontré en dehors de ces deux compteurs.
Impact sur les performances de l’utilisation importante de la mémoire QE
Dans la plupart des cas, lorsqu’un thread demande de la mémoire dans SQL Server pour effectuer une opération et que la mémoire n’est pas disponible, la requête échoue avec une erreur de mémoire insuffisante. Toutefois, il existe quelques scénarios d’exception dans lesquels le thread n’échoue pas, mais attend que la mémoire soit disponible. L’un de ces scénarios est l’allocation de mémoire, et l’autre est la mémoire de compilation des requêtes. SQL Server utilise un objet de synchronisation de threads appelé sémaphore pour effectuer le suivi de la quantité de mémoire allouée pour l’exécution des requêtes. Si SQL Server manque de l’espace de travail QE prédéfini, au lieu d’échouer avec une erreur de mémoire insuffisante, la requête attend. Étant donné que la mémoire de l’espace de travail est autorisée à prendre un pourcentage significatif de la mémoire SQL Server globale, l’attente de la mémoire dans cet espace a de graves conséquences sur les performances. Un grand nombre de requêtes simultanées ont demandé de la mémoire d’exécution et, ensemble, elles ont épuisé le pool de mémoire QE, ou quelques requêtes simultanées ont chacune demandé des allocations très importantes. Dans les deux cas, les problèmes de performances qui en résultent peuvent présenter les symptômes suivants :
- Les pages de données et d’index d’un cache de mémoire tampon ont probablement été vidées pour faire de l’espace pour les demandes d’allocation de mémoire volumineuses. Cela signifie que les lectures de page provenant des demandes de requête doivent être satisfaites à partir du disque (opération beaucoup plus lente).
- Les demandes d’autres allocations de mémoire peuvent échouer avec des erreurs de mémoire insuffisante, car la ressource est liée à des opérations de tri, de hachage ou de génération d’index.
- Les demandes qui ont besoin de mémoire d’exécution attendent que la ressource soit disponible et prennent beaucoup de temps. En d’autres termes, pour l’utilisateur final, ces requêtes sont lentes.
Par conséquent, si vous observez des attentes sur la mémoire d’exécution des requêtes dans Perfmon, les vues de gestion dynamique (DMV) ou DBCC MEMORYSTATUS, vous devez agir pour résoudre ce problème, en particulier si le problème se produit fréquemment. Pour plus d’informations, consultez Que peut faire un développeur sur les opérations de tri et de hachage.
Comment identifier les attentes pour la mémoire d’exécution des requêtes
Il existe plusieurs façons de déterminer les attentes pour les réservations QE. Choisissez celles qui vous conviennent le mieux pour voir l’image plus large au niveau du serveur. Certains de ces outils peuvent ne pas être disponibles (par exemple, Perfmon n’est pas disponible dans Azure SQL Database). Une fois que vous avez identifié le problème, vous devez descendre dans le détail au niveau de la requête individuelle pour voir quelles requêtes nécessitent un réglage ou une réécriture.
Au niveau du serveur, utilisez les méthodes suivantes :
- Sémaphore de ressources DMV sys.dm_exec_query_resource_semaphores Pour plus d’informations, consultez sys.dm_exec_query_resource_semaphores.
- Analyseur de performances compteurs Pour plus d’informations, consultez SQL Server’objet Memory Manager.
- DBCC MEMORYSTATUS Pour plus d’informations, consultez DBCC MEMORYSTATUS.
- Sys.dm_os_memory_clerks DMV des commis de mémoire Pour plus d’informations, consultez sys.dm_os_memory_clerks.
- Identifier les allocations de mémoire à l’aide d’événements étendus (XEvents) Pour plus d’informations, consultez Événements étendus (XEvents) .
Au niveau de la requête individuelle, utilisez les méthodes suivantes :
- Identifier des requêtes spécifiques avec sys.dm_exec_query_memory_grants : requêtes en cours d’exécution. Pour plus d’informations, consultez sys.dm_exec_query_memory_grants.
- Identifier des requêtes spécifiques avec sys.dm_exec_requests : requêtes en cours d’exécution. Pour plus d’informations, consultez sys.dm_exec_requests.
- Identifier des requêtes spécifiques avec sys.dm_exec_query_stats : Statistiques historiques sur les requêtes. Pour plus d’informations, consultez sys.dm_exec_query_stats.
- Identifiez des requêtes spécifiques à l’aide de Magasin des requêtes (QDS) avec sys.query_store_runtime_stats : Statistiques historiques sur les requêtes avec QDS. Pour plus d’informations, consultez sys.query_store_runtime_stats.
Agréger les statistiques d’utilisation de la mémoire
Sys.dm_exec_query_resource_semaphores DMV du sémaphore de ressources
Cette DMV décompose la mémoire de réservation de requête par pool de ressources (interne, par défaut et créé par l’utilisateur) et resource_semaphore (requêtes de requête standard et de petite taille). Une requête utile peut être :
SELECT
pool_id
,total_memory_kb
,available_memory_kb
,granted_memory_kb
,used_memory_kb
,grantee_count, waiter_count
,resource_semaphore_id
FROM sys.dm_exec_query_resource_semaphores rs
L’exemple de sortie suivant montre qu’environ 900 Mo de mémoire d’exécution de requête sont utilisés par 22 requêtes, et 3 autres sont en attente. Cela se produit dans le pool par défaut (pool_id = 2) et le sémaphore de requête standard (resource_semaphore_id = 0).
pool_id total_memory_kb available_memory_kb granted_memory_kb used_memory_kb grantee_count waiter_count resource_semaphore_id
------- --------------- ------------------- ----------------- -------------- ------------- ------------ ---------------------
1 30880 30880 0 0 0 0 0
1 5120 5120 0 0 0 0 1
2 907104 0 907104 898656 22 3 0
2 40960 40960 0 0 0 0 1
(4 rows affected)
Analyseur de performances compteurs
Des informations similaires sont disponibles via Analyseur de performances compteurs, où vous pouvez observer les demandes actuellement accordées (Memory Grants Outstanding), les demandes d’octroi en attente (Memory Grants Pending) et la quantité de mémoire utilisée par les allocations de mémoire (Granted Workspace Memory (KB)). Dans l’image suivante, les subventions en suspens sont de 18, les allocations en attente sont de 2 et la mémoire de l’espace de travail accordée est de 828 288 Ko. Le Memory Grants Pending compteur Perfmon avec une valeur différente de zéro indique que la mémoire a été épuisée.
Pour plus d’informations, consultez SQL Server objet Memory Manager.
- SQLServer, Gestionnaire de mémoire : Mémoire maximale de l’espace de travail (Ko)
- SQLServer, Gestionnaire de mémoire : Allocations de mémoire en attente
- SQLServer, Gestionnaire de mémoire : Allocations de mémoire en attente
- SQLServer, Gestionnaire de mémoire : Mémoire de l’espace de travail accordée (Ko)
DBCC MEMORYSTATUS
Un autre endroit où vous pouvez voir des détails sur la mémoire de réservation de requête est DBCC MEMORYSTATUS (section Objets de mémoire de requête). Vous pouvez examiner la Query Memory Objects (default) sortie des requêtes utilisateur. Si vous avez activé Resource Governor avec un pool de ressources nommé PoolAdmin, par exemple, vous pouvez examiner Query Memory Objects (default) et Query Memory Objects (PoolAdmin).
Voici un exemple de sortie d’un système où 18 requêtes ont reçu une mémoire d’exécution de requête et 2 demandes attendent de la mémoire. Le compteur disponible est égal à zéro, ce qui indique qu’il n’y a plus de mémoire d’espace de travail disponible. Ce fait explique les deux demandes en attente. affiche Wait Time le temps écoulé en millisecondes depuis qu’une requête a été placée dans la file d’attente. Pour plus d’informations sur ces compteurs, consultez Interroger des objets mémoire.
Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 18
Waiting 2
Available 0
Current Max 103536
Future Max 97527
Physical Max 139137
Next Request 5752
Waiting For 8628
Cost 16
Timeout 401
Wait Time 2750
(11 rows affected)
Small Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 0
Waiting 0
Available 5133
Current Max 5133
Future Max 5133
DBCC MEMORYSTATUS affiche également des informations sur le commis de mémoire qui effectue le suivi de la mémoire d’exécution des requêtes. La sortie suivante montre que les pages allouées pour les réservations d’exécution de requête (QE) dépassent 800 Mo.
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
------------------------------------------------------------------------ -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 824640
Sys.dm_os_memory_clerks DMV des commis de mémoire
Si vous avez besoin de plus d’un jeu de résultats tabulaire, différent de celui basé sur DBCC MEMORYSTATUSune section, vous pouvez utiliser sys.dm_os_memory_clerks pour obtenir des informations similaires. Recherchez le commis à la MEMORYCLERK_SQLQERESERVATIONS mémoire. Toutefois, les objets de mémoire de requête ne sont pas disponibles dans cette DMV.
SELECT type, memory_node_id, pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS'
Voici un exemple de sortie :
type memory_node_id pages_kb
----------------------------------------------- -------------- --------------
MEMORYCLERK_SQLQERESERVATIONS 0 824640
MEMORYCLERK_SQLQERESERVATIONS 64 0
Identifier les allocations de mémoire à l’aide d’événements étendus (XEvents)
Plusieurs événements étendus fournissent des informations d’allocation de mémoire et vous permettent de capturer ces informations via une trace :
- sqlserver.additional_memory_grant : se produit lorsqu’une requête tente d’obtenir davantage d’allocation de mémoire pendant l’exécution. L’échec de l’obtention de cette allocation de mémoire supplémentaire peut entraîner un ralentissement de la requête.
- sqlserver.query_memory_grant_blocking : se produit lorsqu’une requête bloque d’autres requêtes en attendant une allocation de mémoire.
- sqlserver.query_memory_grant_info_sampling : se produit à la fin des requêtes échantillonnées de manière aléatoire fournissant des informations d’allocation de mémoire (elles peuvent être utilisées, par exemple, pour la télémétrie).
- sqlserver.query_memory_grant_resource_semaphores : se produit à des intervalles de cinq minutes pour chaque pool de ressources Resource Governor.
- sqlserver.query_memory_grant_usage : se produit à la fin du traitement des requêtes pour les requêtes avec des allocations de mémoire supérieure à 5 Mo pour informer les utilisateurs des inexactitudes d’allocation de mémoire.
- sqlserver.query_memory_grants : se produit à des intervalles de cinq minutes pour chaque requête avec une allocation de mémoire.
Événements étendus de commentaires sur l’allocation de mémoire
Pour plus d’informations sur les fonctionnalités de commentaires d’allocation de mémoire de traitement des requêtes, consultez Commentaires sur l’allocation de mémoire.
- sqlserver.memory_grant_feedback_loop_disabled : se produit lorsque la boucle de rétroaction d’allocation de mémoire est désactivée.
- sqlserver.memory_grant_updated_by_feedback : se produit lorsque l’allocation de mémoire est mise à jour par les commentaires.
Avertissements d’exécution de requête liés aux allocations de mémoire
- sqlserver.execution_warning : se produit lorsqu’une instruction T-SQL ou une procédure stockée attend plus d’une seconde pour une allocation de mémoire ou lorsque la tentative initiale d’obtention de la mémoire échoue. Utilisez cet événement en combinaison avec des événements qui identifient les attentes pour résoudre les problèmes de contention qui ont un impact sur les performances.
- sqlserver.hash_spill_details : se produit à la fin du traitement du hachage si la mémoire est insuffisante pour traiter l’entrée de build d’une jointure de hachage. Utilisez cet événement avec l’un des événements ou
query_post_execution_showplanpour déterminer quelle opération dans le plan généré est à l’originequery_pre_execution_showplandu déversement de hachage. - sqlserver.hash_warning : se produit quand la mémoire est insuffisante pour traiter l’entrée de build d’une jointure de hachage. Cela entraîne soit une récursivité de hachage lorsque l’entrée de build est partitionnée, soit un resserrage de hachage lorsque le partitionnement de l’entrée de build dépasse le niveau de récursivité maximal. Utilisez cet événement avec l’un
query_pre_execution_showplandes événements ouquery_post_execution_showplanpour déterminer quelle opération dans le plan généré est à l’origine de l’avertissement de hachage. - sqlserver.sort_warning : se produit lorsque l’opération de tri sur une requête en cours d’exécution ne tient pas dans la mémoire. Cet événement n’est pas généré pour les opérations de tri provoquées par la création d’index, mais uniquement pour les opérations de tri dans une requête. (Par exemple, dans
Order ByuneSelectinstruction.) Utilisez cet événement pour identifier les requêtes qui s’exécutent lentement en raison de l’opération de tri, en particulier lorsque =warning_type2, indiquant que plusieurs passes sur les données sont nécessaires pour le tri.
Planifier la génération d’événements qui contiennent des informations d’allocation de mémoire
Le plan de requête suivant générant des événements étendus contient des champs granted_memory_kb et ideal_memory_kb par défaut :
- sqlserver.query_plan_profile
- sqlserver.query_post_execution_plan_profile
- sqlserver.query_post_execution_showplan
- sqlserver.query_pre_execution_showplan
Génération d’index de magasin de colonnes
L’une des zones couvertes par XEvents est la mémoire d’exécution utilisée lors de la génération du magasin de colonnes. Voici une liste d’événements disponibles :
- sqlserver.column_store_index_build_low_memory : le moteur de stockage a détecté une insuffisance de mémoire et la taille du rowgroup a été réduite. Vous trouverez ici plusieurs colonnes intéressantes.
- sqlserver.column_store_index_build_memory_trace : tracez l’utilisation de la mémoire pendant la génération de l’index.
- sqlserver.column_store_index_build_memory_usage_scale_down : Le moteur de stockage a été réduit.
- sqlserver.column_store_index_memory_estimation : affiche le résultat de l’estimation de la mémoire pendant la génération de rowgroup COLUMNSTORE.
Identifier des requêtes spécifiques
Il existe deux types de requêtes que vous pouvez trouver lorsque vous examinez le niveau de la demande individuelle. Les requêtes qui consomment une grande quantité de mémoire d’exécution de requête et celles qui attendent la même mémoire. Ce dernier groupe peut être constitué de demandes avec des besoins modestes en allocations de mémoire, et si c’est le cas, vous pouvez concentrer votre attention ailleurs. Mais ils pourraient également être les coupables s’ils demandent des tailles de mémoire énormes. Concentrez-vous sur eux si vous trouvez que c’est le cas. Il peut être courant de constater qu’une requête particulière est le délinquant, mais de nombreux cas de celle-ci sont générés. Les instances qui obtiennent les allocations de mémoire font en sorte que d’autres instances de la même requête attendent l’octroi. Quelles que soient les circonstances spécifiques, vous devez enfin identifier les requêtes et la taille de la mémoire d’exécution demandée.
Identifier des requêtes spécifiques avec sys.dm_exec_query_memory_grants
Pour afficher les requêtes individuelles et la taille de mémoire qu’elles ont demandée et qui ont été accordées, vous pouvez interroger la sys.dm_exec_query_memory_grants vue de gestion dynamique. Cette vue de gestion dynamique affiche des informations sur les requêtes en cours d’exécution, et non des informations historiques.
L’instruction suivante obtient les données de la DMV et extrait également le texte de la requête et le plan de requête en conséquence :
SELECT
session_id
,requested_memory_kb
,granted_memory_kb
,used_memory_kb
,queue_id
,wait_order
,wait_time_ms
,is_next_candidate
,pool_id
,text
,query_plan
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Voici un exemple de sortie abrégé de la requête pendant la consommation de mémoire QE active. La mémoire de la plupart des requêtes est accordée, comme l’indique granted_memory_kb et used_memory_kb étant des valeurs numériques non NULL. Les requêtes qui n’ont pas obtenu leur demande accordée attendent de la mémoire d’exécution, et le granted_memory_kb = NULL. En outre, ils sont placés dans une file d’attente avec un queue_id = 6. Leur wait_time_ms indique environ 37 secondes d’attente. La session 72 est la suivante pour obtenir une allocation comme indiqué par wait_order = 1, tandis que la session 74 vient après elle avec wait_order = 2.
session_id requested_memory_kb granted_memory_kb used_memory_kb queue_id wait_order wait_time_ms is_next_candidate pool_id
---------- -------------------- -------------------- -------------------- -------- ----------- -------------------- ----------------- -------
80 41232 41232 40848 NULL NULL NULL NULL 2
83 41232 41232 40848 NULL NULL NULL NULL 2
84 41232 41232 40848 NULL NULL NULL NULL 2
74 41232 NULL NULL 6 2 37438 0 2
78 41232 41232 40848 NULL NULL NULL NULL 2
81 41232 41232 40848 NULL NULL NULL NULL 2
71 41232 41232 40848 NULL NULL NULL NULL 2
75 41232 NULL NULL 6 0 37438 1 2
82 41232 41232 40848 NULL NULL NULL NULL 2
76 41232 41232 40848 NULL NULL NULL NULL 2
79 41232 41232 40848 NULL NULL NULL NULL 2
85 41232 41232 40848 NULL NULL NULL NULL 2
70 41232 41232 40848 NULL NULL NULL NULL 2
55 41232 41232 40848 NULL NULL NULL NULL 2
59 41232 NULL NULL 6 3 37438 0 2
62 41232 41232 40848 NULL NULL NULL NULL 2
54 41232 41232 40848 NULL NULL NULL NULL 2
77 41232 41232 40848 NULL NULL NULL NULL 2
52 41232 41232 40848 NULL NULL NULL NULL 2
72 41232 NULL NULL 6 1 37438 0 2
69 41232 41232 40848 NULL NULL NULL NULL 2
73 41232 41232 40848 NULL NULL NULL NULL 2
66 41232 NULL NULL 6 4 37438 0 2
68 41232 41232 40848 NULL NULL NULL NULL 2
63 41232 41232 40848 NULL NULL NULL NULL 2
Identifier des requêtes spécifiques avec sys.dm_exec_requests
Il existe un type d’attente dans SQL Server qui indique qu’une requête attend une allocation RESOURCE_SEMAPHOREde mémoire . Vous pouvez observer ce type d’attente pour sys.dm_exec_requests les demandes individuelles. Cette dernière DMV est le meilleur point de départ pour identifier les requêtes qui sont victimes d’une mémoire d’allocation insuffisante. Vous pouvez également observer l’attente RESOURCE_SEMAPHORE dans sys.dm_os_wait_stats sous forme de points de données agrégés au niveau SQL Server. Ce type d’attente s’affiche lorsqu’une demande de mémoire de requête ne peut pas être accordée en raison de l’utilisation de mémoire par d’autres requêtes simultanées. Un nombre élevé de requêtes en attente et de longs temps d’attente indiquent un nombre excessif de requêtes simultanées utilisant la mémoire d’exécution ou des tailles de requête de mémoire élevées.
Remarque
Le temps d’attente pour les allocations de mémoire est limité. Après une attente excessive (par exemple, plus de 20 minutes), SQL Server fois la requête et génère l’erreur 8645 : « Un délai d’attente s’est produit en attendant que les ressources mémoire exécutent la requête. Réexécutez la requête. » Vous pouvez voir la valeur du délai d’expiration définie au niveau du serveur en examinant timeout_sec dans sys.dm_exec_query_memory_grants. La valeur du délai d’expiration peut varier légèrement entre SQL Server versions.
Avec l’utilisation de sys.dm_exec_requests, vous pouvez voir quelles requêtes ont reçu de la mémoire et la taille de cette autorisation. En outre, vous pouvez identifier les requêtes en attente d’une allocation de mémoire en recherchant le type d’attente RESOURCE_SEMAPHORE . Voici une requête qui affiche les demandes accordées et en attente :
SELECT session_id, wait_type, wait_time, granted_query_memory, text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE granted_query_memory > 0
OR wait_type = 'RESOURCE_SEMAPHORE'
Un exemple de sortie montre que deux demandes ont reçu de la mémoire et que deux douzaines d’autres sont en attente d’allocations. La granted_query_memory colonne indique la taille en pages de 8 Ko. Par exemple, une valeur de 34 709 signifie 34 709 * 8 Ko = 277 672 Ko de mémoire accordée.
session_id wait_type wait_time granted_query_memory text
---------- ----------------------------------- -------------------- -------------------------------------------------------------------
65 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
66 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
67 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
68 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
69 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
70 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
71 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
72 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
73 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
74 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
75 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
76 ASYNC_NETWORK_IO 11 34709 select * from sys.messages order by message_id option (maxdop 1)
77 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
78 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
79 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
80 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
81 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
82 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
83 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
84 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
85 ASYNC_NETWORK_IO 14 34709 select * from sys.messages order by message_id option (maxdop 1)
86 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
87 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
88 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
89 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
Identifier des requêtes spécifiques avec sys.dm_exec_query_stats
Si le problème d’allocation de mémoire ne se produit pas pour le moment, mais que vous souhaitez identifier les requêtes incriminables, vous pouvez examiner les données de requête historiques via sys.dm_exec_query_stats. La durée de vie des données est liée au plan de requête de chaque requête. Lorsqu’un plan est supprimé du cache du plan, les lignes correspondantes sont supprimées de cette vue. En d’autres termes, la DMV conserve en mémoire des statistiques qui ne sont pas conservées après un redémarrage SQL Server ou après une sollicitation de la mémoire qui provoque une mise en cache du plan. Cela dit, vous pouvez trouver les informations ici précieuses, en particulier pour les statistiques de requête agrégées. Quelqu’un a peut-être récemment signalé avoir vu des allocations de mémoire volumineuses à partir de requêtes, mais lorsque vous examinez la charge de travail du serveur, vous pouvez découvrir que le problème a disparu. Dans ce cas, sys.dm_exec_query_stats peut fournir les informations que d’autres machines virtuelles DVM ne peuvent pas. Voici un exemple de requête qui peut vous aider à trouver les 20 principales instructions qui ont consommé les plus grandes quantités de mémoire d’exécution. Cette sortie affiche des instructions individuelles même si leur structure de requête est identique. Par exemple, SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 5 est une ligne distincte de SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 100 (seule la valeur du prédicat de filtre varie). La requête obtient les 20 premières instructions avec une taille d’octroi maximale supérieure à 5 Mo.
SELECT TOP 20
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
,CONVERT(DECIMAL (10,2), max_grant_kb /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), min_grant_kb /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (total_grant_kb / execution_count) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), max_used_grant_kb /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), min_used_grant_kb /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_used_grant_kb/ execution_count) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ execution_count) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ 1024.0)) AS total_grant_for_all_executions_mb
,execution_count
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
WHERE max_grant_kb > 5120 -- greater than 5 MB
ORDER BY max_grant_kb DESC
Vous pouvez obtenir des insights encore plus puissants en examinant les requêtes agrégées par query_hash. Cet exemple montre comment rechercher les tailles d’octroi moyennes, maximales et minimales d’une instruction de requête sur toutes ses instances depuis que le plan de requête a été mis en cache pour la première fois.
SELECT TOP 20
MAX(SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1)) AS sample_statement_text
,CONVERT(DECIMAL (10,2), SUM(max_grant_kb) /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), SUM(min_grant_kb) /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (SUM(total_grant_kb) / SUM(execution_count)) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), SUM(max_used_grant_kb) /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), SUM(min_used_grant_kb) /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_used_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_ideal_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), SUM(total_grant_kb) /1024.0) AS total_grant_all_executions_mb
,SUM(execution_count) AS execution_count
,query_hash
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
GROUP BY query_hash
HAVING SUM(max_grant_kb) > 5120 -- greater than 5 MB
ORDER BY SUM(max_grant_kb) DESC
OPTION (MAX_GRANT_PERCENT = 5)
La Sample_Statement_Text colonne montre un exemple de structure de requête qui correspond au hachage de requête, mais elle doit être lue sans tenir compte des valeurs spécifiques de l’instruction. Par exemple, si une instruction contient WHERE Id = 5, vous pouvez la lire sous sa forme plus générique : WHERE Id = @any_value.
Voici un exemple de sortie abrégé de la requête avec uniquement les colonnes sélectionnées affichées :
sample_statement_text max_grant_mb avg_grant_mb max_grant_used_mb avg_grant_used_mb avg_ideal_grant_mb total_grant_all_executions_mb execution_count
----------------------------------------- ------------ ------------- ----------------- ----------------- ------------------ ----------------------------- ----------------
select de.ObjectName,de.CounterName,d 282.45 282.45 6.50 6.50 282.45 282.45 1
SELECT SCHEMA_NAME(udf.schema_id) AS [Sch 33.86 8.55 7.80 1.97 8.55 42.74 5
insert into #tmpCounterDateTime (CounterD 32.45 32.45 3.11 3.11 32.45 32.45 1
select db_id() dbid, db_name() dbname, * 20.80 1.30 5.75 0.36 1.30 20.80 16
SELECT SCHEMA_NAME(obj.schema_id) AS [Sch 20.55 5.19 5.13 1.28 5.19 25.93 5
SELECT xmlplan FROM (SELECT ROW_NUMBER() 19.69 1.97 1.09 0.11 1.97 19.69 10
if ( select max(cast(countervalue as floa 16.39 8.20 0.77 0.38 8.20 16.39 2
SELECT udf.name AS [Name], udf.object_id 11.36 5.08 1.66 0.83 5.08 20.33 4
select --* Database_I 10.94 5.47 1.98 0.99 5.47 10.94 2
IF (select max(cast(dat.countervalue as f 8.00 1.00 0.00 0.00 0.53 8.00 8
insert into #tmpCounterDateTime (CounterD 5.72 2.86 1.98 0.99 2.86 5.72 2
INSERT INTO #tmp (CounterDateTime, Counte 5.39 1.08 1.64 0.33 1.08 6.47 6
Identifier des requêtes spécifiques à l’aide de Magasin des requêtes (QDS) avec sys.query_store_runtime_stats
Si vous avez Magasin des requêtes activé, vous pouvez tirer parti de ses statistiques historiques persistantes. Contrairement aux données de sys.dm_exec_query_stats, ces statistiques survivent à un redémarrage SQL Server ou à une sollicitation de la mémoire, car elles sont stockées dans une base de données. QDS a également des limites de taille et une stratégie de rétention. Pour plus d’informations, consultez les sections Définir le mode de capture Magasin des requêtes optimal et Conserver les données les plus pertinentes dans Magasin des requêtes dans Meilleures pratiques pour la gestion des Magasin des requêtes.
Identifiez si vos bases de données ont Magasin des requêtes activées à l’aide de cette requête :
SELECT name, is_query_store_on FROM sys.databases WHERE is_query_store_on = 1Exécutez la requête de diagnostic suivante dans le contexte d’une base de données spécifique que vous souhaitez examiner :
SELECT MAX(qtxt.query_sql_text) AS sample_sql_text ,CONVERT(DECIMAL(10,2), SUM(rts.avg_query_max_used_memory) / 128) AS avg_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.min_query_max_used_memory) / 128) AS min_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.max_query_max_used_memory) / 128) AS max_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.stdev_query_max_used_memory) / 128) AS stdev_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.last_query_max_used_memory) / 128) AS last_mem_grant_used_mb ,SUM(count_executions) AS count_query_executions FROM sys.query_store_runtime_stats rts JOIN sys.query_store_plan p ON p.plan_id = rts.plan_id JOIN sys.query_store_query q ON p.query_id = q.query_id LEFT OUTER JOIN sys.query_store_query_text qtxt ON q.query_text_id = qtxt.query_text_id GROUP BY q.query_hash HAVING SUM(rts.avg_query_max_used_memory) /128 > 5 -- greater than 5 MB ORDER BY SUM(avg_query_max_used_memory) DESC OPTION (MAX_GRANT_PERCENT = 5)Les principes ici sont les mêmes que
sys.dm_exec_query_stats; vous voyez des statistiques agrégées pour les instructions. Toutefois, une différence réside dans le fait qu’avec QDS, vous examinez uniquement les requêtes dans l’étendue de cette base de données, et non l’ensemble de la SQL Server. Par conséquent, vous devrez peut-être connaître la base de données dans laquelle une demande d’allocation de mémoire particulière a été exécutée. Sinon, exécutez cette requête de diagnostic dans plusieurs bases de données jusqu’à ce que vous trouviez les allocations de mémoire conséquentes.Voici un exemple de sortie abrégé :
sample_sql_text avg_mem_grant_used_mb min_mem_grant_used_mb max_mem_grant_used_mb stdev_mem_grant_used_mb last_mem_grant_used_mb count_query_executions ----------------------------------------- ---------------------- ---------------------- ---------------------- ------------------------ ----------------------- ---------------------- SELECT qtxt.query_sql_text ,CONVERT(D 550.16 550.00 550.00 0.00 550.00 1 SELECT qtxt.query_sql_text ,rts.avg_q 61.00 36.00 65.00 10.87 51.00 14 SELECT qtxt.query_sql_text ,q.* ,rts 25.46 25.00 25.00 0.00 25.00 2 insert into #tmpStats select 5 'Database 13.69 13.00 13.00 0.03 13.00 16 SELECT q.* ,rts 11.93 11.00 12.00 0.23 12.00 2 SELECT * ,rts.avg_query_max_used_memory 9.70 9.00 9.00 0.00 9.00 1 SELECT qtxt.query_sql_text ,rts.avg_q 9.32 9.00 9.00 0.00 9.00 1 select db_id() dbid, db_name() dbname, * 7.33 7.00 7.00 0.00 7.00 9 SELECT q.* ,rts.avg_query_max_used_memo 6.65 6.00 6.00 0.00 6.00 1 (@_msparam_0 nvarchar(4000),@_msparam_1 5.17 4.00 5.00 0.68 4.00 2
Une requête de diagnostic personnalisée
Voici une requête qui combine les données de plusieurs vues, y compris les trois répertoriées précédemment. Il fournit une vue plus approfondie des sessions et de leurs subventions via sys.dm_exec_requests et sys.dm_exec_query_memory_grants, en plus des statistiques au niveau du serveur fournies par sys.dm_exec_query_resource_semaphores.
Remarque
Cette requête retourne deux lignes par session en raison de l’utilisation de sys.dm_exec_query_resource_semaphores (une ligne pour le sémaphore de ressource standard et une autre pour le sémaphore de ressource de petite requête).
SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime
, r.session_id
, r.wait_time
, r.wait_type
, mg.request_time
, mg.grant_time
, mg.requested_memory_kb
/ 1024 requested_memory_mb
, mg.granted_memory_kb
/ 1024 AS granted_memory_mb
, mg.required_memory_kb
/ 1024 AS required_memory_mb
, max_used_memory_kb
/ 1024 AS max_used_memory_mb
, rs.pool_id as resource_pool_id
, mg.query_cost
, mg.timeout_sec
, mg.resource_semaphore_id
, mg.wait_time_ms AS memory_grant_wait_time_ms
, CASE mg.is_next_candidate
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Memory has been granted'
END AS 'Next Candidate for Memory Grant'
, r.command
, ltrim(rtrim(replace(replace (substring (q.text, 1, 1000), char(10), ' '), char(13), ' '))) [text]
, rs.target_memory_kb
/ 1024 AS server_target_grant_memory_mb
, rs.max_target_memory_kb
/ 1024 AS server_max_target_grant_memory_mb
, rs.total_memory_kb
/ 1024 AS server_total_resource_semaphore_memory_mb
, rs.available_memory_kb
/ 1024 AS server_available_memory_for_grants_mb
, rs.granted_memory_kb
/ 1024 AS server_total_granted_memory_mb
, rs.used_memory_kb
/ 1024 AS server_used_granted_memory_mb
, rs.grantee_count AS successful_grantee_count
, rs.waiter_count AS grant_waiters_count
, rs.timeout_error_count
, rs.forced_grant_count
, mg.dop
, r.blocking_session_id
, r.cpu_time
, r.total_elapsed_time
, r.reads
, r.writes
, r.logical_reads
, r.row_count
, s.login_time
, d.name
, s.login_name
, s.host_name
, s.nt_domain
, s.nt_user_name
, s.status
, c.client_net_address
, s.program_name
, s.client_interface_name
, s.last_request_start_time
, s.last_request_end_time
, c.connect_time
, c.last_read
, c.last_write
, qp.query_plan
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_connections c
ON r.connection_id = c.connection_id
INNER JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
INNER JOIN sys.databases d
ON r.database_id = d.database_id
INNER JOIN sys.dm_exec_query_memory_grants mg
ON s.session_id = mg.session_id
INNER JOIN sys.dm_exec_query_resource_semaphores rs
ON mg.resource_semaphore_id = rs.resource_semaphore_id
CROSS APPLY sys.dm_exec_sql_text (r.sql_handle ) AS q
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) qp
OPTION (MAXDOP 1, LOOP JOIN )
Remarque
L’indicateur LOOP JOIN est utilisé dans cette requête de diagnostic pour éviter une allocation de mémoire par la requête elle-même, et aucune clause n’est ORDER BY utilisée. Si la requête de diagnostic finit par attendre une allocation elle-même, son objectif de diagnostic des allocations de mémoire serait rejeté. L’indicateur LOOP JOIN peut potentiellement ralentir la requête de diagnostic, mais dans ce cas, il est plus important d’obtenir les résultats du diagnostic.
Voici un exemple de sortie abrégé de cette requête de diagnostic avec uniquement les colonnes sélectionnées.
| Session_id | wait_time | wait_type | requested_memory_mb | granted_memory_mb | required_memory_mb | max_used_memory_mb | resource_pool_id |
|---|---|---|---|---|---|---|---|
| 60 | 0 | NULL | 9 | 9 | 7 | 1 | 1 |
| 60 | 0 | NULL | 9 | 9 | 7 | 1 | 2 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 1 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 2 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 1 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 2 |
L’exemple de sortie illustre clairement comment une requête envoyée par session_id = 60 a obtenu l’allocation de mémoire de 9 Mo demandée, mais seulement 7 Mo étaient nécessaires pour démarrer correctement l’exécution de la requête. En fin de compte, la requête n’a utilisé que 1 Mo sur les 9 Mo qu’elle a reçus du serveur. La sortie montre également que les sessions 75 et 86 attendent des allocations de mémoire, d’où .RESOURCE_SEMAPHOREwait_type Leur temps d’attente a été de plus de 1 300 secondes (21 minutes), et leur granted_memory_mb est NULL.
Cette requête de diagnostic étant un exemple, n’hésitez pas à la modifier selon vos besoins. Une version de cette requête est également utilisée dans les outils de diagnostic utilisés par Microsoft SQL Server support.
Outils de diagnostic
Microsoft SQL Server support technique utilise des outils de diagnostic pour collecter les journaux et résoudre plus efficacement les problèmes. Sql LogScout et Pssdiag Configuration Manager (avec SQLDiag) collectent les sorties des DMV et des compteurs Analyseur de performances précédemment décrits qui peuvent vous aider à diagnostiquer les problèmes d’allocation de mémoire.
Si vous exécutez SQL LogScout avec des scénarios LightPerf, GeneralPerf ou DetailedPerf , l’outil collecte les journaux nécessaires. Vous pouvez ensuite examiner manuellement le YourServer_PerfStats.out et rechercher -- dm_exec_query_resource_semaphores -- les sorties et -- dm_exec_query_memory_grants -- . Ou, au lieu d’un examen manuel, vous pouvez utiliser SQL Nexus pour importer la sortie provenant de SQL LogScout ou PSSDIAG dans une base de données SQL Server. SQL Nexus crée deux tables, tbl_dm_exec_query_resource_semaphores et tbl_dm_exec_query_memory_grants, qui contiennent les informations nécessaires pour diagnostiquer les allocations de mémoire. SQL LogScout et PSSDIAG collectent également les journaux Perfmon sous la forme . Fichiers BLG, qui peuvent être utilisés pour passer en revue les compteurs de performances décrits dans la section compteurs Analyseur de performances.
Pourquoi les allocations de mémoire sont-elles importantes pour un développeur ou un administrateur de base de données
En fonction de l’expérience de support Microsoft, les problèmes d’allocation de mémoire ont tendance à être parmi les problèmes liés à la mémoire les plus courants. Les applications exécutent souvent des requêtes apparemment simples qui peuvent entraîner des problèmes de performances sur le SQL Server en raison d’opérations de tri ou de hachage énormes. Ces requêtes consomment non seulement beaucoup de mémoire SQL Server, mais font également en sorte que d’autres requêtes attendent que la mémoire soit disponible, ce qui entraîne le goulot d’étranglement des performances.
À l’aide des outils décrits ici (DMV, compteurs Perfmon et plans de requête réels), vous pouvez identifier les requêtes qui sont des consommateurs d’octrois importants. Vous pouvez ensuite paramétrer ou réécrire ces requêtes pour résoudre ou réduire l’utilisation de la mémoire de l’espace de travail.
Que peut faire un développeur sur les opérations de tri et de hachage
Une fois que vous avez identifié des requêtes spécifiques qui consomment une grande quantité de mémoire de réservation de requête, vous pouvez prendre des mesures pour réduire les allocations de mémoire en reconceptionant ces requêtes.
Causes des opérations de tri et de hachage dans les requêtes
La première étape consiste à prendre conscience des opérations d’une requête qui peuvent entraîner des allocations de mémoire.
Raisons pour lesquelles une requête utilise un opérateur SORT :
ORDER BY (T-SQL) entraîne le tri des lignes avant d’être diffusées en continu comme résultat final.
GROUP BY (T-SQL) peut introduire un opérateur de tri dans un plan de requête avant le regroupement si un index sous-jacent qui commande les colonnes groupées n’est pas présent.
DISTINCT (T-SQL) se comporte de la même façon que
GROUP BY. Pour identifier les lignes distinctes, les résultats intermédiaires sont classés, puis les doublons sont supprimés. L’optimiseur utilise unSortopérateur antérieur à cet opérateur si les données ne sont pas déjà triées en raison d’une recherche ou d’une analyse d’index ordonnée.L’opérateur De jointure de fusion, lorsqu’il est sélectionné par l’optimiseur de requête, nécessite que les deux entrées jointes soient triées. SQL Server pouvez déclencher un tri si un index cluster n’est pas disponible sur la colonne de jointure dans l’une des tables.
Raisons pour lesquelles une requête utilise un opérateur de plan de requête HASH :
Cette liste n’est pas exhaustive, mais inclut les raisons les plus fréquemment rencontrées pour les opérations de hachage. Analysez le plan de requête pour identifier les opérations de correspondance de hachage.
JOIN (T-SQL) : lors de la jointure de tables, SQL Server a le choix entre trois opérateurs physiques,
Nested Loop,Merge JoinetHash Join. Si SQL Server finit par choisir une jointure de hachage, elle a besoin de mémoire QE pour stocker et traiter les résultats intermédiaires. En règle générale, un manque d’index corrects peut conduire à cet opérateur de jointure le plus coûteux en ressources,Hash Join. Pour examiner le plan de requête afin d’identifierHash Match, consultez Informations de référence sur les opérateurs logiques et physiques.DISTINCT (T-SQL) : un
Hash Aggregateopérateur peut être utilisé pour éliminer les doublons dans un ensemble de lignes. Pour rechercher unHash Match(Aggregate) dans le plan de requête, consultez Informations de référence sur les opérateurs logiques et physiques.UNION (T-SQL) : ceci est similaire à
DISTINCT. UnHash Aggregatepeut être utilisé pour supprimer les doublons de cet opérateur.SUM/AVG/MAX/MIN (T-SQL) : toute opération d’agrégation peut potentiellement être effectuée en tant que
Hash Aggregate. Pour rechercher unHash Match(Aggregate) dans le plan de requête, consultez Informations de référence sur les opérateurs logiques et physiques.
Le fait de connaître ces raisons courantes peut vous aider à éliminer, autant que possible, les demandes d’allocation de mémoire volumineuses qui arrivent à SQL Server.
Moyens de réduire les opérations de tri et de hachage ou la taille d’octroi
- Tenez à jour les statistiques . Cette étape fondamentale, qui améliore les performances des requêtes à de nombreux niveaux, garantit que l’optimiseur de requête dispose des informations les plus précises lors de la sélection des plans de requête. SQL Server détermine la taille à demander pour son allocation de mémoire en fonction des statistiques. Les statistiques obsolètes peuvent entraîner une surestimation ou une sous-estimation de la demande d’octroi et entraîner ainsi une demande d’octroi inutilement élevée ou des résultats sur le disque, respectivement. Vérifiez que les statistiques de mise à jour automatique sont activées dans vos bases de données et/ou conservez les statistiques à jour avec UPDATE STATISTICS ou sp_updatestats.
- Réduisez le nombre de lignes provenant de tables. Si vous utilisez un filtre WHERE ou join plus restrictif et que vous réduisez le nombre de lignes, un tri ultérieur dans le plan de requête permet d’ordonner ou d’agréger un jeu de résultats plus petit. Un jeu de résultats intermédiaire plus petit nécessite moins de mémoire de jeu de travail. Il s’agit d’une règle générale que les développeurs peuvent suivre non seulement pour enregistrer la mémoire du jeu de travail, mais également pour réduire le processeur et les E/S (cette étape n’est pas toujours possible). Si des requêtes bien écrites et efficaces en ressources sont déjà en place, cette directive a été respectée.
- Créez des index sur des colonnes de jointure pour faciliter les jointures de fusion. Les opérations intermédiaires dans un plan de requête sont affectées par les index de la table sous-jacente. Par exemple, si une table n’a pas d’index sur une colonne de jointure et qu’une jointure de fusion est l’opérateur de jointure le plus économique, toutes les lignes de cette table doivent être triées avant l’exécution de la jointure. Si, au lieu de cela, un index existe sur la colonne, une opération de tri peut être éliminée.
- Créez des index pour éviter les opérations de hachage. En règle générale, le réglage de base des requêtes commence par vérifier si vos requêtes ont des index appropriés pour les aider à réduire les lectures et à réduire ou éliminer les tris volumineux ou les opérations de hachage dans la mesure du possible. Les jointures de hachage sont généralement sélectionnées pour traiter des entrées volumineuses, non triées et non indexées. La création d’index peut modifier cette stratégie d’optimiseur et accélérer la récupération des données. Pour obtenir de l’aide sur la création d’index, consultez Assistant Paramétrage du moteur de base de données et Optimiser les index non cluster avec des suggestions d’index manquants.
- Utilisez les index COLUMNSTORE le cas échéant pour les requêtes d’agrégation qui utilisent
GROUP BY. Les requêtes analytiques qui traitent des ensembles de lignes très volumineux et effectuent généralement des agrégations « group by » peuvent nécessiter des blocs de mémoire volumineux pour effectuer le travail. Si aucun index n’est disponible et fournit des résultats triés, un tri est automatiquement introduit dans le plan de requête. Une sorte de résultat très important peut entraîner une allocation de mémoire coûteuse. - Supprimez le
ORDER BYsi vous n’en avez pas besoin. Dans les cas où les résultats sont diffusés en continu vers une application qui trie les résultats de sa propre manière ou qui permet à l’utilisateur de modifier l’ordre des données consultées, vous n’avez pas besoin d’effectuer un tri côté SQL Server. Il suffit de diffuser les données vers l’application dans l’ordre dans lequel le serveur les produit et de laisser l’utilisateur final les trier lui-même. Les applications de création de rapports comme Power BI ou Reporting Services sont des exemples de ces applications qui permettent aux utilisateurs finaux de trier leurs données. - Envisagez, bien que prudent, l’utilisation d’un indicateur LOOP JOIN quand des jointures existent dans une requête T-SQL. Cette technique peut éviter les jointures de hachage ou de fusion qui utilisent des allocations de mémoire. Toutefois, cette option n’est suggérée qu’en dernier recours, car forcer une jointure peut entraîner une requête beaucoup plus lente. Testez votre charge de travail pour vous assurer qu’il s’agit d’une option. Dans certains cas, une jointure de boucle imbriquée peut même ne pas être une option. Dans ce cas, SQL Server peut échouer avec l’erreur MSSQLSERVER_8622 « Le processeur de requête n’a pas pu produire de plan de requête en raison des indicateurs définis dans cette requête ».
Indicateur de requête d’allocation de mémoire
Depuis SQL Server 2012 SP3, il existe un indicateur de requête qui vous permet de contrôler la taille de votre allocation de mémoire par requête. Voici un exemple d’utilisation de cet indicateur :
SELECT Column1, Column2
FROM Table1
ORDER BY Column1
OPTION (MIN_GRANT_PERCENT = 3, MAX_GRANT_PERCENT = 5 )
Nous vous recommandons d’utiliser ici des valeurs conservatrices, en particulier dans les cas où vous vous attendez à ce que de nombreuses instances de votre requête soient exécutées simultanément. Veillez à tester votre charge de travail pour qu’elle corresponde à votre environnement de production et déterminez les valeurs à utiliser.
Pour plus d’informations, consultez MAX_GRANT_PERCENT et MIN_GRANT_PERCENT.
Resource Governor
La mémoire QE est la mémoire que Resource Governor limite réellement lorsque les paramètres MIN_MEMORY_PERCENT et MAX_MEMORY_PERCENT sont utilisés. Une fois que vous avez identifié les requêtes qui provoquent des allocations de mémoire volumineuses, vous pouvez limiter la mémoire utilisée par les sessions ou les applications. Il convient de mentionner que le default groupe de charge de travail permet à une requête de prendre jusqu’à 25 % de la mémoire qui peut être accordée sur un SQL Server instance. Pour plus d’informations, consultez Resource Governor pools de ressources et CREATE WORKLOAD GROUP.
Traitement des requêtes adaptatifs et retour d’allocation de mémoire
SQL Server 2017 a introduit la fonctionnalité de retour d’allocation de mémoire. Il permet au moteur d’exécution de requête d’ajuster l’octroi accordé à la requête en fonction de l’historique précédent. L’objectif est de réduire la taille de l’allocation si possible ou de l’augmenter lorsque davantage de mémoire est nécessaire. Cette fonctionnalité a été publiée en trois vagues :
- Commentaires sur l’allocation de mémoire en mode Batch dans SQL Server 2017
- Retour d’allocation de mémoire en mode ligne dans SQL Server 2019
- Commentaires sur la persistance de l’allocation de mémoire sur le disque à l’aide de l’Magasin des requêtes et de l’octroi de centile dans SQL Server 2022
Pour plus d’informations, consultez Commentaires sur l’allocation de mémoire. La fonctionnalité d’allocation de mémoire peut réduire la taille des allocations de mémoire pour les requêtes au moment de l’exécution et ainsi réduire les problèmes liés aux demandes d’octroi volumineuses. Avec cette fonctionnalité en place, en particulier sur SQL Server 2019 et les versions ultérieures, où le traitement adaptatif en mode ligne est disponible, vous pouvez même ne pas remarquer de problèmes de mémoire provenant de l’exécution des requêtes. Toutefois, si vous avez cette fonctionnalité en place (activée par défaut) et que vous constatez toujours une consommation de mémoire QE importante, appliquez les étapes décrites précédemment pour réécrire les requêtes.
Augmenter la mémoire SQL Server ou du système d’exploitation
Une fois que vous avez pris les mesures nécessaires pour réduire les allocations de mémoire inutiles pour vos requêtes, si vous rencontrez toujours des problèmes de mémoire insuffisante associés, la charge de travail nécessite probablement plus de mémoire. Par conséquent, envisagez d’augmenter la mémoire pour SQL Server à l’aide du max server memory paramètre s’il y a suffisamment de mémoire physique sur le système pour le faire. Suivez les recommandations pour laisser environ 25 % de la mémoire pour le système d’exploitation et d’autres besoins. Pour plus d’informations, consultez Options de configuration de la mémoire du serveur. Si la mémoire est insuffisante sur le système, envisagez d’ajouter de la RAM physique ou, s’il s’agit d’une machine virtuelle, augmentez la RAM dédiée pour votre machine virtuelle.
Internes d’allocation de mémoire
Pour en savoir plus sur certains éléments internes sur la mémoire d’exécution des requêtes, consultez le billet de blog Présentation de l’allocation de mémoire SQL Server .
Comment créer un scénario de performances avec une utilisation intensive de la mémoire
Enfin, l’exemple suivant montre comment simuler une consommation importante de mémoire d’exécution des requêtes et introduire des requêtes en attente RESOURCE_SEMAPHOREde . Vous pouvez le faire pour apprendre à utiliser les outils et techniques de diagnostic décrits dans cet article.
Avertissement
Ne l’utilisez pas sur un système de production. Cette simulation est fournie pour vous aider à comprendre le concept et à mieux l’apprendre.
Sur un serveur de test, installez les utilitaires RML et SQL Server.
Utilisez une application cliente comme SQL Server Management Studio pour réduire le paramètre de mémoire maximale du serveur de votre SQL Server à 1 500 Mo :
EXEC sp_configure 'max server memory', 1500 RECONFIGUREOuvrez une invite de commandes et remplacez le répertoire par le dossier des utilitaires RML :
cd C:\Program Files\Microsoft Corporation\RMLUtilsUtilisez ostress.exe pour générer plusieurs demandes simultanées sur votre SQL Server de test. Cet exemple utilise 30 sessions simultanées, mais vous pouvez modifier cette valeur :
ostress.exe -E -S. -Q"select * from sys.messages order by message_id option (maxdop 1)" -n30Utilisez les outils de diagnostic décrits précédemment pour identifier les problèmes d’allocation de mémoire.
Résumé des méthodes de gestion des allocations de mémoire volumineuses
- Réécrire des requêtes.
- Mettez à jour les statistiques et mettez-les à jour régulièrement.
- Créez des index appropriés pour la ou les requêtes identifiées. Les index peuvent réduire le grand nombre de lignes traitées, ce qui modifie les
JOINalgorithmes et réduit la taille des octrois ou les élimine complètement. - Utilisez l’indicateur
OPTION(min_grant_percent = XX, max_grant_percent = XX). - Utilisez Resource Governor.
- SQL Server 2017 et 2019 utilisent le traitement de requêtes adaptatif, ce qui permet au mécanisme de rétroaction d’allocation de mémoire d’ajuster dynamiquement la taille de l’allocation de mémoire au moment de l’exécution. Cette fonctionnalité peut d’abord éviter les problèmes d’allocation de mémoire.
- Augmentez SQL Server ou la mémoire du système d’exploitation.
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour