sys.dm_exec_query_memory_grants (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Retourne des informations sur toutes les requêtes qui ont demandé et qui attendent une allocation de mémoire ou qui 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 ORDER BY clause n’auront pas d’allocation de mémoire.

Dans Azure SQL base de données, les vues de gestion dynamique ne peuvent pas exposer des informations qui auraient un impact sur le confinement de la base de données ou 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 des colonnes scheduler_id, wait_order, pool_idsont group_id filtrées ; la valeur de colonne est définie sur NULL.

Notes

Pour l’appeler à partir de Azure Synapse Analytics ou d’Analytics Platform System (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 identique à 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.

Note: 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à allouée.
wait_order int Ordre séquentiel des requêtes en attente dans le 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 un délai d’expiration. 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à allouée.
plan_handle varbinary(64) Identificateur de ce plan de requête. Utilisez sys.dm_exec_query_plan pour extraire le plan XML réel.
sql_handle varbinary(64) Identificateur du texte Transact-SQL pour cette requête. Utilisez sys.dm_exec_sql_text pour 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 (à partir de SQL Server 2016 (13.x)) et Azure SQL Database
used_worker_count bigint Nombre de threads de travail utilisés pour le moment.

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

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

S’applique à : SQL Server (à partir 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, requiert l’autorisation VIEW DATABASE STATE dans la base de données.

Autorisations pour SQL Server 2022 et versions ultérieures

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 à partir de 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’expiration des requêtes peut examiner les éléments suivants :

  • Recherchez l'état général de la mémoire système à l'aide de sys.dm_os_memory_clerks, sys.dm_os_sys_info et de différents compteurs de performance.

  • Vérifiez les réservations de mémoire d’exécution de requête dans où sys.dm_os_memory_clerkstype = 'MEMORYCLERK_SQLQERESERVATIONS'.

  • Recherchez les requêtes en attente1 pour les octrois à l’aide de sys.dm_exec_query_memory_grants:

    --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).

  • Cache de recherche pour les requêtes avec 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’emballement est suspectée, examinez le plan d’exécution dans la query_plan colonne de sys.dm_exec_query_plan et interrogez le lot text à partir de sys.dm_exec_sql_text. Examinez plus en détail les requêtes nécessitant beaucoup de 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