sys.dm_os_waiting_tasks (Transact-SQL)sys.dm_os_waiting_tasks (Transact-SQL)
S’applique à :Applies to: SQL ServerSQL Server (toutes les versions prises en charge)
SQL ServerSQL Server (all supported versions)
Azure SQL DatabaseAzure SQL Database
Azure SQL DatabaseAzure SQL Database
Azure SQL Managed InstanceAzure SQL Managed Instance
Azure SQL Managed InstanceAzure SQL Managed Instance
Azure Synapse AnalyticsAzure Synapse Analytics
Azure Synapse AnalyticsAzure Synapse Analytics
Parallel Data WarehouseParallel Data Warehouse
Parallel Data WarehouseParallel Data Warehouse
SQL ServerSQL Server (toutes les versions prises en charge)
SQL ServerSQL Server (all supported versions)
Azure SQL DatabaseAzure SQL Database
Azure SQL DatabaseAzure SQL Database
Azure SQL Managed InstanceAzure SQL Managed Instance
Azure SQL Managed InstanceAzure SQL Managed Instance
Azure Synapse AnalyticsAzure Synapse Analytics
Azure Synapse AnalyticsAzure Synapse Analytics
Parallel Data WarehouseParallel Data Warehouse
Parallel Data WarehouseParallel Data Warehouse
Renvoie les informations sur la file d'attente des tâches en attente de certaines ressources.Returns information about the wait queue of tasks that are waiting on some resource. Pour plus d’informations sur les tâches, consultez le Guide d’architecture des threads et des tâches.For more information about tasks, see the Thread and Task Architecture Guide.
Notes
Pour appeler cette valeur à partir de Azure Synapse AnalyticsAzure Synapse Analytics ou Parallel Data WarehouseParallel Data Warehouse , utilisez le nom sys.dm_pdw_nodes_os_waiting_tasks.To call this from Azure Synapse AnalyticsAzure Synapse Analytics or Parallel Data WarehouseParallel Data Warehouse, use the name sys.dm_pdw_nodes_os_waiting_tasks.
Nom de la colonneColumn name | Type de donnéesData type | DescriptionDescription |
---|---|---|
waiting_task_addresswaiting_task_address | varbinary (8)varbinary(8) | Adresse de la tâche en attente.Address of the waiting task. |
session_idsession_id | smallintsmallint | ID de la session associée à la tâche.ID of the session associated with the task. |
exec_context_idexec_context_id | intint | ID du contexte d'exécution associé à la tâche.ID of the execution context associated with the task. |
wait_duration_mswait_duration_ms | bigintbigint | Temps d'attente total de ce type d'attente (en millisecondes).Total wait time for this wait type, in milliseconds. Cette heure est comprise entre signal_wait_time.This time is inclusive of signal_wait_time. |
wait_typewait_type | nvarchar(60)nvarchar(60) | Nom du type d'attente.Name of the wait type. |
resource_addressresource_address | varbinary (8)varbinary(8) | Adresse de la ressource que la tâche attend.Address of the resource for which the task is waiting. |
blocking_task_addressblocking_task_address | varbinary (8)varbinary(8) | Tâche qui mobilise actuellement cette ressource.Task that is currently holding this resource |
blocking_session_idblocking_session_id | smallintsmallint | ID de la session qui bloque la demande.ID of the session that is blocking the request. Si cette colonne est NULL, la demande n'est pas bloquée, ou les informations de session de la session bloquant la demande ne sont pas disponibles (ou ne peuvent pas être identifiées).If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified). -2 = La ressource qui bloque la demande appartient à une transaction distribuée orpheline.-2 = The blocking resource is owned by an orphaned distributed transaction. -3 = La ressource qui bloque la demande appartient à une transaction de récupération différée.-3 = The blocking resource is owned by a deferred recovery transaction. -4 = L'ID de session du propriétaire du verrou qui bloque la demande n'a pas pu être déterminé en raison de transitions d'état de verrou interne.-4 = Session ID of the blocking latch owner could not be determined due to internal latch state transitions. |
blocking_exec_context_idblocking_exec_context_id | intint | ID du contexte d'exécution de la tâche bloquante.ID of the execution context of the blocking task. |
resource_descriptionresource_description | nvarchar (3072)nvarchar(3072) | Description de la ressource actuellement mobilisée.Description of the resource that is being consumed. Pour plus d'informations, consultez la liste ci-dessous.For more information, see the list below. |
pdw_node_idpdw_node_id | intint | S’applique à: Azure Synapse AnalyticsAzure Synapse Analytics , Parallel Data WarehouseParallel Data WarehouseApplies to: Azure Synapse AnalyticsAzure Synapse Analytics, Parallel Data WarehouseParallel Data Warehouse Identificateur du nœud sur lequel cette distribution se trouve.The identifier for the node that this distribution is on. |
Colonne resource_descriptionresource_description column
La colonne resource_description comporte les valeurs possibles suivantes.The resource_description column has the following possible values.
Propriétaire des ressources de pool de threads :Thread-pool resource owner:
- ID ThreadPool = planificateur<hex-address>threadpool id=scheduler<hex-address>
Propriétaire de ressources de requêtes parallèles :Parallel query resource owner:
- exchangeEvent ID = {port | Pipe} <hex-address> WaitType = <exchange-wait-type> NodeId =<exchange-node-id>exchangeEvent id={Port|Pipe}<hex-address> WaitType=<exchange-wait-type> nodeId=<exchange-node-id>
Exchange-wait-type :Exchange-wait-type:
e_waitNonee_waitNone
e_waitPipeNewRowe_waitPipeNewRow
e_waitPipeGetRowe_waitPipeGetRow
e_waitSynchronizeConsumerOpene_waitSynchronizeConsumerOpen
e_waitPortOpene_waitPortOpen
e_waitPortClosee_waitPortClose
e_waitRangee_waitRange
Propriétaire de ressources de verrouillage :Lock resource owner:
<type-specific-description> ID = verrou <lock-hex-address> mode = <mode> associatedObjectId =<associated-obj-id><type-specific-description> id=lock<lock-hex-address> mode=<mode> associatedObjectId=<associated-obj-id>
<type-specific-description> peut être :<type-specific-description> can be:
Pour la base de données : databaselock Resource = <databaselock-subresource> dbid =<db-id>For DATABASE: databaselock subresource=<databaselock-subresource> dbid=<db-id>
Pour le fichier : filelock fileid = <file-id> Resource = <filelock-subresource> dbid =<db-id>For FILE: filelock fileid=<file-id> subresource=<filelock-subresource> dbid=<db-id>
Pour Object : objectlock lockPartition = <lock-partition-id> objID = <obj-id> Resource = <objectlock-subresource> dbid =<db-id>For OBJECT: objectlock lockPartition=<lock-partition-id> objid=<obj-id> subresource=<objectlock-subresource> dbid=<db-id>
Pour la page : PageLock fileid = <file-id> pageid = <page-id> dbid = Resource <db-id> =<pagelock-subresource>For PAGE: pagelock fileid=<file-id> pageid=<page-id> dbid=<db-id> subresource=<pagelock-subresource>
Pour Key : Keylock hobtid = <hobt-id> dbid =<db-id>For Key: keylock hobtid=<hobt-id> dbid=<db-id>
Pour extent : extentlock fileid = <file-id> pageid = <page-id> dbid =<db-id>For EXTENT: extentlock fileid=<file-id> pageid=<page-id> dbid=<db-id>
Pour RID : ridlock fileid = <file-id> pageid = <page-id> dbid =<db-id>For RID: ridlock fileid=<file-id> pageid=<page-id> dbid=<db-id>
Pour APPLICATION : applicationlock hash = <hash> databasePrincipalId = <role-id> dbid =<db-id>For APPLICATION: applicationlock hash=<hash> databasePrincipalId=<role-id> dbid=<db-id>
Pour les métadonnées : metadatalock Resource = <metadata-subresource> ClassID = <metadatalock-description> dbid =<db-id>For METADATA: metadatalock subresource=<metadata-subresource> classid=<metadatalock-description> dbid=<db-id>
Pour HoBT : hobtlock hobtid = <hobt-id> Resource = <hobt-subresource> dbid =<db-id>For HOBT: hobtlock hobtid=<hobt-id> subresource=<hobt-subresource> dbid=<db-id>
Pour ALLOCATION_UNIT : allocunitlock hobtid = <hobt-id> Resource = <alloc-unit-subresource> dbid =<db-id>For ALLOCATION_UNIT: allocunitlock hobtid=<hobt-id> subresource=<alloc-unit-subresource> dbid=<db-id>
<mode> peut être :<mode> can be:
Sch-S, Sch-M, S, U, X, IS, IU, IX, SIU, SIX, UIX, BU, RangeS-S, RangeS-U, RangeI-N, RangeI-S, RangeI-U, RangeI-X, RangeX-, RangeX-U, RangeX-XSch-S, Sch-M, S, U, X, IS, IU, IX, SIU, SIX, UIX, BU, RangeS-S, RangeS-U, RangeI-N, RangeI-S, RangeI-U, RangeI-X, RangeX-, RangeX-U, RangeX-X
Propriétaire de ressources externes :External resource owner:
- Externalressource externe =<wait-type>External ExternalResource=<wait-type>
Propriétaire de ressources génériques :Generic resource owner:
Espace de travail TransactionInfo TransactionMutex =<workspace-id>TransactionMutex TransactionInfo Workspace=<workspace-id>
MutexMutex
CLRTaskJoinCLRTaskJoin
CLRMonitorEventCLRMonitorEvent
CLRRWLockEventCLRRWLockEvent
resourceWaitresourceWait
Propriétaire de ressources de verrou :Latch resource owner:
<db-id>:<file-id>:<page-in-file><db-id>:<file-id>:<page-in-file>
<GUID>
<latch-class> (<latch-address>)<latch-class> (<latch-address>)
AutorisationsPermissions
Sur SQL ServerSQL Server , requiert l' VIEW SERVER STATE
autorisation.On SQL ServerSQL Server, requires VIEW SERVER STATE
permission.
Sur SQL Database objectifs de service de base, S0 et S1, et pour les bases de données dans des pools élastiques, le compte d' administrateur de serveur ou le compte d' administrateur Azure Active Directory est requis.On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account or the Azure Active Directory admin account is required. Pour tous les autres SQL Database objectifs de service, l' VIEW DATABASE STATE
autorisation est requise dans la base de données.On all other SQL Database service objectives, the VIEW DATABASE STATE
permission is required in the database.
ExempleExample
R.A. Identifiez les tâches des sessions bloquées.Identify tasks from blocked sessions.
SELECT * FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL;
B.B. Afficher les tâches en attente par connexionView waiting tasks per connection
SELECT st.text AS [SQL Text], c.connection_id, w.session_id,
w.wait_duration_ms, w.wait_type, w.resource_address,
w.blocking_session_id, w.resource_description, c.client_net_address, c.connect_time
FROM sys.dm_os_waiting_tasks AS w
INNER JOIN sys.dm_exec_connections AS c ON w.session_id = c.session_id
CROSS APPLY (SELECT * FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)) AS st
WHERE w.session_id > 50 AND w.wait_duration_ms > 0
ORDER BY c.connection_id, w.session_id
GO
C.C. Afficher les tâches en attente pour tous les processus utilisateur avec des informations supplémentairesView waiting tasks for all user processes with additional information
SELECT 'Waiting_tasks' AS [Information], owt.session_id,
owt.wait_duration_ms, owt.wait_type, owt.blocking_session_id,
owt.resource_description, es.program_name, est.text,
est.dbid, eqp.query_plan, er.database_id, es.cpu_time,
es.memory_usage*8 AS memory_usage_KB
FROM sys.dm_os_waiting_tasks owt
INNER JOIN sys.dm_exec_sessions es ON owt.session_id = es.session_id
INNER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) est
OUTER APPLY sys.dm_exec_query_plan (er.plan_handle) eqp
WHERE es.is_user_process = 1
ORDER BY owt.session_id;
GO
Voir aussiSee Also
SQL Server vues de gestion dynamique liées au système d’exploitation (Transact-SQL) SQL Server Operating System Related Dynamic Management Views (Transact-SQL)
Guide d’architecture de thread et de tâcheThread and Task Architecture Guide