Questions raised by sys. dm_ OS _waiting_tasks of SQL Server Applications (I ),

Questions raised by sys. dm_ OS _waiting_tasks of SQL Server Applications (I ),

Many users wait through sys when viewing SQL statements. view dm_exec_requests. The waiting type is also obtained through wait_type, sys. dm_ OS _waiting_tasks can also see the difference between session waiting ....

Not much nonsense.

Test version 2012

Field description of sys. dm_ OS _waiting_tasks:

Waiting_task_address	Varbinary (8)	The address of the waiting task.
Session_id	Smallint	The ID of the session associated with the task.
Exec_context_id	Int	The ID of the execution context associated with the task.
Wait_duration_ms	Int	The total wait time (in milliseconds) of this wait type ). This time includesSignal_wait_time.
Wait_type	Nvarchar (60)	The name of the wait type.
Resource_address	Varbinary (8)	The address of the resource that the task is waiting.
Blocking_task_address	Varbinary (8)	The task that currently holds this resource.
Blocking_session_id	Smallint	The ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the locked session is unavailable (or cannot be identified ). -2 = blocked resources are owned by isolated distributed transactions. -3 = blocked resources are owned by delayed recovery transactions. -4 = the session ID of the blocking lock owner cannot be determined due to the internal lock status transition.
Blocking_exec_context_id	Int	The execution context ID of the blocked task.

For example:

----- Start a transaction to update a table and do not submit it. Begin tran update t1 set B = getdate () ----- perform a query and enable parallel select * from t1 inner join t2 on t1.a = t2.a option (querytraceon 8649)

Query the result of sys. dm_ OS _waiting_tasks, udate: session 55, select: session 54, and check that the session appears

21 waits (the VM provides a dual-core 4-thread). It can be seen that there are four wait_type = LCK_M_S, it can be understood that four threads are running concurrently to scan all the waiting states of table t1. From the resource_description field information, let's see if it is the waiting state of table T1.

　　　　

From the message "ridlock fileid = 1 pageid = 109 dbid = 7 id = lock1f03c7700 mode = X associatedObjectId = 72057594038910976" We know ridlock fileid = 1 pageid = 109 dbid = 7

Dbcc traceon (3604)
Dbcc page (7, 1, 3)

 

　　　　

It is determined that the four LCK_M_S logs are indeed the Waits generated by the scanning table. What are the other CXPACKET waits? From the rule, we can see that CXPACKET waits for four groups of four exec_context_id groups: 5, 6, 7, and 8 (four threads waiting to scan the table ), in the other row, "exchangeEvent id = Port1fe7a2200 WaitType = e_waitPortOpen nodeId = 0" should be the scheduling thread.

In sys. dm_ OS _waiting_tasks, CXPACKET and LCK_M_S appear in the execution of the parallel plan. Let's take a look at how they are displayed in sys. dm_exec_requests (Here we only retrieve the fields used for the test)

　　　　

Blocking_session_id is 0, and wait_type is CXPACKET (parallel wait, we know the main reason is not this). In addition, we can see that the TASK_ADDRESS captured here is the scheduling thread. Other experiments show that sys. dm_exec_requests cannot obtain real waiting types and resources in parallel waiting. If parallel execution is canceled, two views of a serial plan will get the same result.

The example shows sys. dm_exec_requests and sys. in actual use, dm_ OS _waiting_tasks has the difference about parallelism, but not just this question. Why is there 21 waits in the 4-thread parallel plan? How to execute parallel plans? Let's continue with the next article ....

Articles you may be interested in:

• Using GruntJS to build a Web program task
• Questions about SQL Server application sys. dm_ OS _waiting_tasks (medium)
• Questions about SQL Server application sys. dm_ OS _waiting_tasks (below)