Résolution des problèmes de lenteur des performances ou de faible mémoire causés par l’octroi de mémoire dans SQL Server
Qu’est-ce que 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 de l’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 à une ou plusieurs des fins suivantes :
- Opérations de tri
- Opérations de hachage
- Opérations de copie en bloc (pas un problème courant)
- Création d’index, y compris l’insertion dans des index COLUMNSTORE, car les dictionnaires/tables de hachage sont utilisés au moment de l’exécution pour la génération d’index (pas un problème courant)
Pour fournir un contexte, pendant sa durée de vie, une requête peut demander de la mémoire à partir de différents allocateurs de mémoire ou de commis 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 à être exécutée. 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 ultérieurement 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 des allocations de mémoire. Comme vous pouvez l’imaginer, une fois l’opération d’exécution de requête terminée, l’octroi de mémoire est remis à SQL Server pour l’utiliser pour d’autres travaux. Par conséquent, les allocations d’allocation de mémoire sont temporaires par nature, mais peuvent 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 pour la durée de vie de la requête.
Exemple de requête avec une 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 entraîne une demande d’octroi 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 de la requête (appuyez sur F4 pour afficher les propriétés) :
En outre, si vous cliquez avec le bouton droit dans l’espace blanc dans le plan de requête, vous pouvez choisir Afficher le code XML du plan d’exécution... et localiser un élément XML qui affiche les mêmes informations d’octroi 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 ont besoin d’explication ici. Une requête peut souhaiter une certaine quantité de mémoire d’exécution (DesiredMemory) et demanderait généralement ce montant (RequestMemory). Au moment de l’exécution, SQL Server accorde toute ou partie de la mémoire demandée en fonction de la disponibilité (GrantedMemory). À la fin, la requête peut utiliser plus ou moins de la mémoire initiale demandée (MaxUsedMemory). Si l’optimiseur de requête a surestimé la quantité de mémoire nécessaire, elle 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 renversé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 initiale 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 et 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 de requête (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. La mémoire QE est généralement 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. À la fin, 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 (consultez 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 « grant ». Ces compteurs et
Memory Grants Pending,Memory Grants Outstandingaffichent le nombre d’allocations de mémoire satisfaites ou en attente. Ils ne comptent pas pour la taille d’allocation de mémoire. Une seule requête peut avoir consommé, par exemple, 4 Go de mémoire pour effectuer un tri, mais cela n’est pas reflété dans l’un 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ées en Ko. LeMaximum Workspace Memory (KB), un autre compteur, tient compte de la quantité maximale de mémoire de l’espace de travail disponible pour toutes les demandes susceptibles de devoir effectuer ce hachage, trier, copier en bloc et opérations 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 à l’intérieur de SQL Server pour obtenir quelque chose 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 où le thread ne échoue pas, mais attend que la mémoire soit disponible. L’un de ces scénarios est des allocations 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 suivre la quantité de mémoire accordée pour l’exécution de la requête. Si SQL Server manque de l’espace de travail QE prédéfini, au lieu d’échouer à la requête avec une erreur de mémoire insuffisante, elle provoque l’attente de la requête. Étant donné que la mémoire de l’espace de travail est autorisée à prendre un pourcentage significatif de mémoire SQL Server globale, l’attente de la mémoire dans cet espace a de graves implications en termes de performances. Un grand nombre de requêtes simultanées ont demandé de la mémoire d’exécution, et ensemble, ils ont épuisé le pool de mémoire QE, ou quelques requêtes simultanées ont chacun demandé des subventions très volumineuses. Les problèmes de performances résultants peuvent présenter les symptômes suivants :
- Les données et les pages d’index d’un cache de mémoire tampon ont probablement été vidées pour créer de l’espace pour les demandes d’allocation de mémoire volumineuses. Cela signifie que les lectures de pages provenant des requêtes doivent être satisfaites à partir du disque (une opération beaucoup plus lente).
- Les demandes d’autres allocations de mémoire peuvent échouer avec des erreurs de mémoire insuffisantes, car la ressource est liée aux opérations de tri, de hachage ou de génération d’index.
- Les demandes nécessitant une mémoire d’exécution attendent que la ressource devienne disponible et prend beaucoup de temps. En d’autres termes, à 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 Ce que peut faire un développeur sur les opérations de tri et de hachage.
Guide pratique pour identifier les attentes de mémoire d’exécution de requête
Il existe plusieurs façons de déterminer les attentes des réservations QE. Choisissez ceux qui vous servent le mieux pour voir l’image plus grande 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 en détail au niveau de la requête individuelle pour voir quelles requêtes ont besoin de réglage ou de réécriture.
Au niveau du serveur, utilisez les méthodes suivantes :
- Sys.dm_exec_query_resource_semaphores de sémaphore de ressource pour plus d’informations, consultez sys.dm_exec_query_resource_semaphores.
- compteurs de Analyseur de performances Pour plus d’informations, consultez l’objet Gestionnaire de mémoire SQL Server.
- DBCC MEMORYSTATUS Pour plus d’informations, consultez DBCC MEMORYSTATUS.
- Gestionnaires de mémoire sys.dm_os_memory_clerks Pour plus d’informations, consultez sys.dm_os_memory_clerks.
- Identifiez 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 :
- Identifiez des requêtes spécifiques avec sys.dm_exec_query_memory_grants : actuellement en cours d’exécution de requêtes. Pour plus d’informations, consultez sys.dm_exec_query_memory_grants.
- Identifier des requêtes spécifiques avec sys.dm_exec_requests : actuellement en cours d’exécution de requêtes. Pour plus d’informations, consultez sys.dm_exec_requests.
- Identifiez 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égation des statistiques d’utilisation de la mémoire
Sémaphore de ressource sys.dm_exec_query_resource_semaphores
Cette vue DMV décompose la mémoire de réservation de requête par pool de ressources (interne, par défaut et créée par l’utilisateur) et resource_semaphore (requêtes régulières et petites requêtes). 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 régulière (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)
Compteurs Analyseur de performances
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 attente sont de 18, les subventions en attente sont 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 l’objet Gestionnaire de mémoire SQL Server.
- SQLServer, Memory Manager : Mémoire maximale de l’espace de travail (Ko)
- SQLServer, Memory Manager : Allocations de mémoire en attente
- SQLServer, Memory Manager : Allocations de mémoire en attente
- SQLServer, Memory Manager : 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 à la fois 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 requêtes 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. Le Wait Time temps écoulé s’affiche en millisecondes depuis qu’une demande a été placée dans la file d’attente. Pour plus d’informations sur ces compteurs, consultez Les objets de mémoire de requête.
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
Mémoire commis DMV sys.dm_os_memory_clerks
Si vous avez besoin d’un jeu de résultats tabulaire, différent de celui basé sur DBCC MEMORYSTATUSla section, vous pouvez utiliser sys.dm_os_memory_clerks pour obtenir des informations similaires. Recherchez le MEMORYCLERK_SQLQERESERVATIONS commis de mémoire. Toutefois, les objets mémoire de requête ne sont pas disponibles dans cette vue 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’octroi 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 plus 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 le 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 échantillonées aléatoirement fournissant des informations d’octroi de mémoire (il peut être utilisé, par exemple, pour la télémétrie).
- sqlserver.query_memory_grant_resource_semaphores : se produit à 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 de plus de 5 Mo pour informer les utilisateurs des inexactitudes d’allocation de mémoire.
- sqlserver.query_memory_grants : se produit à intervalles de cinq minutes pour chaque requête avec une allocation de mémoire.
Événements étendus d’octroi 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’octroi de mémoire.
- sqlserver.memory_grant_feedback_loop_disabled : se produit lorsque la boucle de rétroaction d’octroi 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 plusieurs secondes 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 les événements qui identifient les attentes pour résoudre les problèmes de contention qui affectent les performances.
- sqlserver.hash_spill_details : se produit à la fin du traitement de hachage en cas d’insuffisance de mémoire pour traiter l’entrée de build d’une jointure de hachage. Utilisez cet événement avec l’un
query_pre_execution_showplandes événements ouquery_post_execution_showplanles événements pour déterminer quelle opération dans le plan généré provoque le déversement de hachage. - sqlserver.hash_warning : se produit en cas d’insuffisance de mémoire pour traiter l’entrée de build d’une jointure de hachage. Cela entraîne une récursivité de hachage lorsque l’entrée de build est partitionnée ou qu’un bail de hachage est effectué lorsque le partitionnement de l’entrée de build dépasse le niveau maximal de récursivité. Utilisez cet événement avec l’un
query_pre_execution_showplandes événements ouquery_post_execution_showplanles événements pour déterminer quelle opération dans le plan généré provoque 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 en 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, uniquement pour les opérations de tri dans une requête. (Par exemple, une
Order Byinstruction dans uneSelectinstruction.) 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 lewarning_type= 2, indiquant plusieurs passes sur les données ont été nécessaires pour trier.
Planifier la génération d’événements qui contiennent des informations d’octroi 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 du magasin de colonnes
L’une des zones couvertes par XEvents est la mémoire d’exécution utilisée pendant la génération du magasin de colonnes. Il s’agit d’une liste d’événements disponibles :
- sqlserver.column_store_index_build_low_memory : le moteur de stockage a détecté une condition de mémoire faible et la taille du rowgroup a été réduite. Il y a plusieurs colonnes intéressantes ici.
- sqlserver.column_store_index_build_memory_trace : tracez l’utilisation de la mémoire pendant la build d’index.
- sqlserver.column_store_index_build_memory_usage_scale_down : le moteur de stockage a diminué.
- sqlserver.column_store_index_memory_estimation : affiche le résultat de l’estimation de la mémoire pendant la build 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 requête individuel. 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 se composer de demandes ayant des besoins modestes en matière d’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 trouver qu’une requête particulière est le délinquant, mais de nombreuses instances de celui-ci sont générées. Ces instances qui obtiennent les allocations de mémoire provoquent l’attente d’autres instances de la même requête. Quelles que soient les circonstances spécifiques, en fin de compte, vous devez 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ées et qui ont été accordées, vous pouvez interroger la sys.dm_exec_query_memory_grants vue de gestion dynamique. Cette vue DMV affiche des informations sur les requêtes en cours d’exécution, et non sur les informations historiques.
L’instruction suivante obtient les données du DMV et récupère é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 abrégé de sortie de la requête pendant la consommation de mémoire QE active. La plupart des requêtes ont leur mémoire accordée, comme indiqué par granted_memory_kb les used_memory_kb valeurs numériques non NULL. Les requêtes qui n’ont pas obtenu leur demande sont en attente de 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. Ils wait_time_ms indiquent environ 37 secondes d’attente. La session 72 est la suivante en ligne pour obtenir une subvention, 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 l’octroi RESOURCE_SEMAPHOREde mémoire. Vous pouvez observer ce type sys.dm_exec_requests d’attente pour les demandes individuelles. Ce dernier 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 en tant que points de données agrégés au niveau de 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 d’autres requêtes simultanées ayant utilisé la mémoire. Un nombre élevé de requêtes en attente et de temps d’attente longs indiquent un nombre excessif de requêtes simultanées à l’aide de la mémoire d’exécution ou de tailles de requêtes de mémoire volumineuses.
Note
Le temps d’attente des allocations de mémoire est fini. Après une attente excessive (par exemple, plus de 20 minutes), SQL Server expire et génère l’erreur 8645 : « Un délai d’attente s’est produit en attendant que les ressources de mémoire exécutent la requête. Réexécutez la requête. » Vous pouvez voir la valeur de 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 les versions de SQL Server.
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 octroi. En outre, vous pouvez identifier les requêtes qui attendent actuellement une allocation de mémoire en recherchant le RESOURCE_SEMAPHORE type d’attente. Voici une requête qui vous montre à la fois les demandes accordées et les demandes 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 été accordées à la mémoire, et deux douzaines d’autres attendent des subventions. La granted_query_memory colonne indique la taille dans les 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’octroi de mémoire ne se produit pas à ce stade, 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 les statistiques en mémoire qui ne sont pas conservées après un redémarrage de SQL Server ou après la pression de la mémoire provoque une mise en cache du plan. Cela étant 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 est disparu. Dans ce cas, sys.dm_exec_query_stats vous pouvez fournir les insights que les autres machines virtuelles virtuelles ne peuvent pas. Voici un exemple de requête qui peut vous aider à trouver les 20 premières 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 il s’agit d’une ligne distincte ( SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 100 seule la valeur de 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
Des insights encore plus puissants peuvent être obtenus en examinant les requêtes agrégées par query_hash. Cet exemple montre comment rechercher les tailles moyennes, maximales et minimales d’octroi pour une instruction de requête sur toutes ses instances, car 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 abrégé de sortie 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 provenant de sys.dm_exec_query_stats, ces statistiques survivent à une pression de mémoire ou de redémarrage SQL Server, 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 le mode de capture d’Magasin des requêtes optimal et conservez les données les plus pertinentes dans les sections Magasin des requêtes des meilleures pratiques pour gérer le 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 est que, avec QDS, vous examinez uniquement les requêtes dans l’étendue de cette base de données, et non dans l’ensemble de SQL Server. Vous devrez peut-être connaître la base de données dans laquelle une demande d’octroi 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 sisables.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
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.
Note
Cette requête retourne deux lignes par session en raison de l’utilisation d’une sys.dm_exec_query_resource_semaphores 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 )
Note
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 subvention elle-même, son objectif de diagnostiquer les allocations de mémoire serait vaincu. L’indicateur LOOP JOIN peut entraîner un ralentissement de la requête de diagnostic, mais dans ce cas, il est plus important d’obtenir les résultats de diagnostic.
Voici un exemple abrégé de sortie de cette requête de diagnostic avec uniquement des 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 avec succès l’octroi de mémoire de 9 Mo demandé, mais seulement 7 Mo ont été nécessaires pour démarrer l’exécution de la requête. À la fin, la requête n’a utilisé que 1 Mo des 9 Mo qu’elle a reçues du serveur. La sortie montre également que les sessions 75 et 86 attendent des allocations de mémoire, donc le RESOURCE_SEMAPHORE wait_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 est un exemple. N’hésitez donc pas à la modifier de manière à répondre à vos besoins. Une version de cette requête est également utilisée dans les outils de diagnostic utilisés par Microsoft SQL Server.
Outils de diagnostic
Il existe des outils de diagnostic utilisés par le support technique Microsoft SQL Server pour collecter les journaux et résoudre plus efficacement les problèmes. SQL LogScout et Pssdiag Configuration Manager (avec SQLDiag) collectent les sorties des vues de gestion dynamiques décrites précédemment et des compteurs de Analyseur de performances qui peuvent vous aider à diagnostiquer les problèmes d’allocation de mémoire.
Si vous exécutez SQL LogScout avec LightPerf, GeneralPerf ou DetailedPerf, l’outil collecte les journaux nécessaires. Vous pouvez ensuite examiner manuellement le YourServer_PerfStats.out et rechercher et -- dm_exec_query_memory_grants -- générer -- dm_exec_query_resource_semaphores -- des sorties. 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 et tbl_dm_exec_query_resource_semaphores tbl_dm_exec_query_memory_grantscontient 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 Analyseur de performances compteurs.
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 certains des problèmes de 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 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 entraînent également l’attente de la mémoire pour 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 à grande subvention. 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 remaniant ces requêtes.
Ce qui provoque les opérations de tri et de hachage dans les requêtes
La première étape consiste à prendre connaissance des opérations d’une requête qui peuvent entraîner des allocations de mémoire.
Raisons pour lesquelles une requête utiliserait 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 de regrouper si un index sous-jacent n’est pas présent qui commande les colonnes groupées.
DISTINCT (T-SQL) se comporte de la même façon que
GROUP BY. Pour identifier des lignes distinctes, les résultats intermédiaires sont classés, puis les doublons sont supprimés. L’optimiseur utilise unSortopérateur avant cet opérateur si les données ne sont pas déjà triées en raison d’une recherche d’index ordonnée ou d’une analyse.L’opérateur Merge Join , lorsqu’il est sélectionné par l’optimiseur de requête, nécessite que les deux entrées jointes soient triées. SQL Server peut 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 couramment 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 LoopetMerge JoinHash Join. Si SQL Server choisit une jointure de hachage, il a besoin de la mémoire QE pour que les résultats intermédiaires soient stockés et traités. En règle générale, un manque de bons index peut entraîner cet opérateur de jointure le plus coûteux en ressources,Hash Join. Pour examiner le plan de requête à identifierHash Match, consultez la référence des 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 la référence des 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 la référence des opérateurs logiques et physiques.
Connaître ces raisons courantes peut vous aider à éliminer, autant que possible, les demandes d’octroi de mémoire volumineuses provenant de SQL Server.
Moyens de réduire les opérations de tri et de hachage ou la taille d’octroi
- Maintenez les statistiques à jour. 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 octroi 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, ce qui entraîne une demande d’octroi inutilement élevée ou un dépassement 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 statiques mises à jour avec UPDATE STATISTICS ou sp_updatestats.
- Réduisez le nombre de lignes provenant de tables. Si vous utilisez un filtre WHERE plus restrictif ou une jointure et réduisez le nombre de lignes, un tri ultérieur dans le plan de requête obtient pour classer ou 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 matière de 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 considérée comme 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, à la place, 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 des requêtes de base 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 opérations de tri ou de hachage volumineuses dans la mesure du possible. Les jointures de hachage sont généralement sélectionnées pour traiter les 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 Moteur de base de données Assistant Paramétrage et Réglage des index non cluster avec des suggestions d’index manquantes.
- Utilisez des index COLUMNSTORE, le cas échéant pour les requêtes d’agrégation qui utilisent
GROUP BY. Analytique requêtes 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 un index n’est pas disponible qui fournit des résultats triés, un tri est automatiquement introduit dans le plan de requête. Un résultat très volumineux 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 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. Diffusez simplement les données vers l’application dans l’ordre dans lequel le serveur le produit et laissez l’utilisateur final le trier seul. Les applications de création de rapports comme Power BI ou Reporting Services sont des exemples de telles applications qui permettent aux utilisateurs finaux de trier leurs données. - Envisagez, bien que avec prudence, l’utilisation d’un indicateur LOOP JOIN lorsque 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 avec contrainte votre charge de travail pour vous assurer qu’il s’agit d’une option. Dans certains cas, une jointure de boucle imbriquée peut ne pas être une option. Dans ce cas, SQL Server peut échouer avec une erreur MSSQLSERVER_8622, « Le processeur de requêtes n’a pas pu produire un plan de requête en raison des indicateurs définis dans cette requête ».
Indicateur de requête d’octroi de mémoire
Depuis SQL Server 2012 SP3, un indicateur de requête vous permet de contrôler la taille de votre allocation de mémoire par requête. Voici un exemple de la façon dont vous pouvez utiliser cet indicateur :
SELECT Column1, Column2
FROM Table1
ORDER BY Column1
OPTION (MIN_GRANT_PERCENT = 3, MAX_GRANT_PERCENT = 5 )
Nous vous recommandons d’utiliser des valeurs conservatrices ici, en particulier dans les cas où vous attendez que de nombreuses instances de votre requête soient exécutées simultanément. Veillez à tester votre charge de travail de manière à ce qu’elle corresponde à votre environnement de production et à déterminer les valeurs à utiliser.
Pour plus d’informations, consultez MAX_GRANT_PERCENT et MIN_GRANT_PERCENT.
gouverneur de ressources
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é des 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 est important 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 une instance SQL Server. Pour plus d’informations, consultez Pools de ressources Resource Governor et CREATE WORKLOAD GROUP.
Commentaires sur le traitement des requêtes adaptatifs et l’octroi de mémoire
SQL Server 2017 a introduit la fonctionnalité de commentaires sur l’octroi de mémoire. Il permet au moteur d’exécution de requête d’ajuster l’octroi donné à la requête en fonction de l’historique précédent. L’objectif est de réduire la taille de l’octroi si possible ou de l’augmenter lorsque plus de mémoire est nécessaire. Cette fonctionnalité a été publiée en trois vagues :
- Commentaires sur l’octroi de mémoire en mode Batch dans SQL Server 2017
- Commentaires sur l’octroi de mémoire en mode ligne dans SQL Server 2019
- Commentaires sur la persistance de l’allocation de mémoire sur disque à l’aide de l’octroi de Magasin des requêtes et de centile dans SQL Server 2022
Pour plus d’informations, consultez Rétroaction d’allocation de mémoire. La fonctionnalité d’octroi 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 versions ultérieures, où le traitement adaptatif en mode ligne est disponible, vous ne remarquerez peut-être même pas les problèmes de mémoire provenant de l’exécution de la requête. Toutefois, si vous disposez de cette fonctionnalité (activée par défaut) et que vous voyez toujours une grande consommation de mémoire QE, appliquez les étapes décrites précédemment pour réécrire des requêtes.
Augmenter la mémoire de 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 faible, la charge de travail nécessite probablement davantage 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 existe suffisamment de mémoire physique sur le système pour ce 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 les options de configuration de la mémoire du serveur. Si aucune mémoire suffisante n’est disponible 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.
Interne 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’octroi de mémoire SQL Server.
Comment créer un scénario de performances avec une utilisation intensive de l’allocation de mémoire
Enfin, l’exemple suivant montre comment simuler une grande consommation de mémoire d’exécution des requêtes et introduire des requêtes en attente RESOURCE_SEMAPHORE. Vous pouvez le faire pour apprendre à utiliser les outils et techniques de diagnostic décrits dans cet article.
Avertissement
N’utilisez pas cela sur un système de production. Cette simulation est fournie pour vous aider à comprendre le concept et à vous aider à l’apprendre mieux.
Sur un serveur de test, installez les utilitaires RML et SQL Server.
Utilisez une application cliente telle que SQL Server Management Studio pour réduire le paramètre maximal de mémoire du serveur de votre serveur 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 requêtes simultanées sur votre serveur 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 conservez-les régulièrement mises à jour.
- Créez des index appropriés pour la requête 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 subventions 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 des requêtes adaptatifs, ce qui permet au mécanisme de retour d’allocation de mémoire d’ajuster dynamiquement la taille d’allocation de mémoire au moment de l’exécution. Cette fonctionnalité peut empêcher les problèmes d’octroi de mémoire en premier lieu.
- Augmentez la mémoire de SQL Server ou du système d’exploitation.