Partage via

sys.dm_exec_query_memory_grants (Transact-SQL)

  • Article

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Retourne des informations sur toutes les requêtes demandées et attendent une allocation de mémoire ou ont reçu une allocation de mémoire. Les requêtes qui ne nécessitent pas d’allocation de mémoire n’apparaissent pas dans cette vue. Par exemple, les opérations de tri et de jointure de hachage ont des allocations de mémoire pour l’exécution des requêtes, tandis que les requêtes sans clause n’ont pas d’allocation ORDER BY de mémoire.

Dans Azure SQL Database, les vues de gestion dynamique ne peuvent pas exposer les informations susceptibles d’avoir un impact sur le contenu de la base de données ou d’exposer des informations sur d’autres bases de données auxquelles l’utilisateur a accès. Pour éviter d’exposer ces informations, chaque ligne qui contient des données qui n’appartiennent pas au locataire connecté est filtrée. En outre, les valeurs dans les colonnes scheduler_id, , pool_idwait_order, group_id sont filtrées ; la valeur de colonne est définie sur NULL.

Remarque

Pour appeler cela à partir d’Azure Synapse Analytics ou du système de plateforme Analytics (PDW), utilisez le nom sys.dm_pdw_nodes_exec_query_memory_grants. Cette syntaxe n’est pas prise en charge par le pool SQL serverless dans Azure Synapse Analytics.

Nom de la colonne Type de données Description
session_id smallint ID (SPID) de la session dans laquelle cette requête est en cours d'exécution.
request_id int ID de la demande. Unique dans le contexte de la session.
scheduler_id int ID du planificateur qui planifie cette requête.
Dop smallint Degré de parallélisme de cette requête.
request_time datetime Date et heure auxquelles cette requête a demandé l'allocation de mémoire.
grant_time datetime Date et heure auxquelles la mémoire a été allouée pour cette requête. NULL si la mémoire n'a pas encore été allouée.
requested_memory_kb bigint Quantité totale de mémoire demandée, en kilo-octets.
granted_memory_kb bigint Quantité totale de mémoire actuellement allouée, en kilo-octets. Peut être NULL si la mémoire n'a pas encore été allouée. Pour une situation classique, cette valeur doit être la même que requested_memory_kb. Pour la création d'index, le serveur peut autoriser de la mémoire à la demande supplémentaire au-delà de la mémoire allouée initialement.
required_memory_kb bigint Mémoire minimale requise pour exécuter cette requête, en kilo-octets. requested_memory_kb est identique ou supérieur à ce montant.
used_memory_kb bigint Mémoire physique utilisée à ce moment, en kilo-octets.
max_used_memory_kb bigint Mémoire physique maximale utilisée jusqu'à ce moment, en kilo-octets.
query_cost float Coût estimé de la requête.
timeout_sec int Délai d'expiration, en secondes, avant que cette requête abandonne la demande d'allocation de la mémoire.
resource_semaphore_id smallint ID non unique du sémaphore de ressource sur lequel attend cette requête.

Remarque : Cet ID est unique dans les versions de SQL Server antérieures à SQL Server 2008 (10.0.x). Cette modification peut affecter l'exécution de la requête de résolution des problèmes. Pour plus d’informations, consultez la section « Remarques » plus loin dans cet article.
queue_id smallint ID de la file d'attente dans laquelle cette requête attend l'allocation de mémoire. NULL si la mémoire est déjà accordée.
wait_order int Ordre séquentiel des requêtes en attente dans le fichier spécifié queue_id. Cette valeur peut changer pour une requête donnée si d’autres requêtes obtiennent des allocations de mémoire ou expirent. NULL si la mémoire est déjà accordée.
is_next_candidate bit Candidat pour l'allocation mémoire suivante.

1 = Oui

0 = Non

NULL = La mémoire est déjà allouée.
wait_time_ms bigint Temps d'attente en millisecondes. NULL si la mémoire est déjà accordée.
plan_handle varbinary(64) Identificateur de ce plan de requête. Permet sys.dm_exec_query_plan d’extraire le plan XML réel.
sql_handle varbinary(64) Identificateur du texte Transact-SQL pour cette requête. Permet sys.dm_exec_sql_text d’obtenir le texte Transact-SQL réel.
group_id int ID du groupe de charge de travail dans lequel cette requête est exécutée.
pool_id int ID du pool de ressources auquel appartient ce groupe de charge de travail.
is_small tinyint Si la valeur est définie sur 1, cette allocation utilise le sémaphore de ressource le plus petit. Si la valeur est définie sur 0, c'est que le sémaphore de ressource ordinaire est utilisé.
ideal_memory_kb bigint Taille de l'allocation mémoire, en kilo-octets (Ko) pour l'ajuster à la mémoire physique. Elle est basée sur l'estimation de la cardinalité.
pdw_node_id int Identificateur du nœud sur lequel cette distribution est activée.

S’applique à : Azure Synapse Analytics, Analytics Platform System (PDW)
reserved_worker_count bigint Nombre de threads de travail réservés.

S’applique à : SQL Server (à compter de SQL Server 2016 (13.x)) et Azure SQL Database.
used_worker_count bigint Nombre de threads de travail utilisés pour l’instant.

S’applique à : SQL Server (à compter de SQL Server 2016 (13.x)) et Azure SQL Database.
max_used_worker_count bigint Nombre maximal de threads de travail utilisés jusqu’à ce moment.

S’applique à : SQL Server (à compter de SQL Server 2016 (13.x)) et Azure SQL Database.
reserved_node_bitmap bigint Bitmap des nœuds NUMA où les threads de travail sont réservés .

S’applique à : SQL Server (à compter de SQL Server 2016 (13.x)) et Azure SQL Database.

autorisations

Sur SQL Server, nécessite l’autorisation VIEW SERVER STATE.
Sur Azure SQL Database, nécessite l’autorisation VIEW DATABASE STATE dans la base de données.

Autorisations pour SQL Server 2022 (et versions plus récentes)

Nécessite l’autorisation VIEW SERVER PERFORMANCE STATE sur le serveur.

Notes

Les requêtes qui utilisent des vues de gestion dynamique qui incluent ORDER BY ou des agrégats peuvent augmenter la consommation de mémoire et contribuer ainsi au problème qu’elles résolvent.

La fonctionnalité Gouverneur de ressources permet à un administrateur de base de données de répartir des ressources serveur entre plusieurs pools de ressources (64 pools au maximum). À compter de SQL Server 2008 (10.0.x), chaque pool se comporte comme une petite instance de serveur indépendante et nécessite deux sémaphores. Le nombre de lignes retournées sys.dm_exec_query_resource_semaphores peut être jusqu’à 20 fois plus élevé que les lignes retournées dans SQL Server 2005 (9.x).

Exemples

Un scénario de débogage classique pour le délai d’attente des requêtes peut examiner les éléments suivants :

  • Vérifiez l’état global de la mémoire système à l’aide de sys.dm_os_memory_clerks, de sys.dm_os_sys_info et de différents compteurs de performances.

  • Recherchez les réservations de mémoire d’exécution de requête dans l’emplacement où sys.dm_os_memory_clerks type = 'MEMORYCLERK_SQLQERESERVATIONS'.

  • Recherchez les requêtes en attentede 1 pour les subventions à l’aide sys.dm_exec_query_memory_grantsde :

    --Find all queries waiting in the memory queue  
SELECT * FROM sys.dm_exec_query_memory_grants WHERE grant_time IS NULL;

    1 Dans ce scénario, le type d’attente est généralement RESOURCE_SEMAPHORE. Pour plus d’informations, consultez sys.dm_os_wait_stats (Transact-SQL).

  • Rechercher dans le cache des requêtes avec des allocations de mémoire à l’aide de sys.dm_exec_cached_plans (Transact-SQL) et de sys.dm_exec_query_plan (Transact-SQL)

    -- retrieve every query plan from the plan cache  
USE master;  
GO  
SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);  
GO

  • Si une requête d’exécution est soupçonnée, examinez le plan d’exécution dans la query_plan colonne à partir de sys.dm_exec_query_plan et du lot text de requêtes à partir de sys.dm_exec_sql_text. Examinez plus en détail les requêtes gourmandes en mémoire en cours d’exécution, à l’aide de sys.dm_exec_requests.

    --Active requests with memory grants
SELECT
--Session data 
  s.[session_id], s.open_transaction_count
--Memory usage
, r.granted_query_memory, mg.grant_time, mg.requested_memory_kb, mg.granted_memory_kb, mg.required_memory_kb, mg.used_memory_kb, mg.max_used_memory_kb     
--Query 
, query_text = t.text, input_buffer = ib.event_info, query_plan_xml = qp.query_plan, request_row_count = r.row_count, session_row_count = s.row_count
--Session history and status
, s.last_request_start_time, s.last_request_end_time, s.reads, s.writes, s.logical_reads, session_status = s.[status], request_status = r.status
--Session connection information
, s.host_name, s.program_name, s.login_name, s.client_interface_name, s.is_user_process
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests AS r 
    ON r.[session_id] = s.[session_id]
LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS mg 
    ON mg.[session_id] = s.[session_id]
OUTER APPLY sys.dm_exec_sql_text (r.[sql_handle]) AS t
OUTER APPLY sys.dm_exec_input_buffer(s.[session_id], NULL) AS ib 
OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp 
WHERE mg.granted_memory_kb > 0
ORDER BY mg.granted_memory_kb desc, mg.requested_memory_kb desc;
GO

Voir aussi