sys.dm_os_waiting_tasks (Transact-SQL)sys.dm_os_waiting_tasks (Transact-SQL)

• 03/13/2017
• 3 minutes de lecture
• • W
  • o

S’applique à :Applies to: SQL ServerSQL Server (toutes les versions prises en charge) SQL ServerSQL Server (all supported versions) Azure SQL DatabaseAzure SQL DatabaseAzure SQL DatabaseAzure SQL Database Azure SQL Managed InstanceAzure SQL Managed InstanceAzure SQL Managed InstanceAzure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics Parallel Data WarehouseParallel Data WarehouseParallel Data WarehouseParallel Data WarehouseS’applique à :Applies to: SQL ServerSQL Server (toutes les versions prises en charge) SQL ServerSQL Server (all supported versions) Azure SQL DatabaseAzure SQL DatabaseAzure SQL DatabaseAzure SQL Database Azure SQL Managed InstanceAzure SQL Managed InstanceAzure SQL Managed InstanceAzure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics Parallel Data WarehouseParallel Data WarehouseParallel 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.

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