Questions raised by the sys.dm_os_waiting_tasks of SQL Server applications (_MSSQL)

Many people look at the SQL statement waiting for the time is through the sys.dm_exec_requests view, waiting for the type is also through Wait_type, Sys.dm_os_waiting_tasks can see the session waiting so what is the difference? ....

No more nonsense, just open the whole.

Beta version 2012

Sys.dm_os_waiting_tasks's Field Description:

Waiting_task_address	varbinary (8)	Wait for the address of the 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) for this wait type. This time contains signal_wait_time.
Wait_type	nvarchar (60)	The name of the wait type.
Resource_address	varbinary (8)	The address of the resource that the task waits for.
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 for the locked session is not available (or cannot be identified). -2 = The blocking resource is owned by an orphaned distributed transaction. -3 = The blocking resource is owned by a deferred recovery transaction. -4 = The session ID of the blocking latch owner cannot be determined due to an internal latch state conversion.
blocking_exec_context_id	Int	The execution context ID of the task being blocked.

Make a small example:

-----Open a transaction to update a table and do not commit.
BEGIN TRAN 
Update T1 set B = getdate ()
-----Make a query and open parallel
select * from t1 inner JOIN T2 o n t1.a = t2.a
option (Querytraceon 8649)

Query the results of Sys.dm_os_waiting_tasks, Udate:session, Select:session 54, as shown in the session appeared in the

21 Waiting (virtual machine to dual-core 4 threads), you can see that wait_type for lck_m_s four, this can be understood to open parallel up four threads to scan the table T1 all wait state, from resource_description Field information Let's see if we're waiting for the T1 table.

　　　　

From the message "Ridlock fileid=1 pageid=109 dbid=7 id=lock1f03c7700 mode=x associatedobjectid=72057594038910976" We know that Ridlock Fileid=1 pageid=109 dbid=7

DBCC TRACEON (3604)
DBCC page (7,1,109,3)

　　　　

Determined that the lck_m_s four is indeed a scan of the table generated by the wait, then the other cxpacket wait what ghost? It can be seen from the law that Cxpacket waits in four groups of 4 exec_context_id respectively, 5,6,7,8 (four threads waiting to sweep the table), and a 13th line in the previous figure "Exchangeevent id=port1fe7a2200 Waittype=e_waitportopen nodeid=0 "should be a scheduled thread.

Sys.dm_os_waiting_tasks in the execution of the parallel plan Cxpacket and lck_m_s then let's take a look at how the sys.dm_exec_requests is displayed (just take the field out of the test)

　　　　

Blocking_session_id unexpectedly is 0, Wait_type unexpectedly is cxpacket (wait in parallel, we know that the main reason for waiting is not this), in addition, observed that the task_address is the scheduling thread. Other experiments have shown that sys.dm_exec_requests cannot get real wait types and resources in parallel waiting. If you cancel the parallel, execute a serial plan two views the results are the same.

In the example we see the difference between sys.dm_exec_requests and sys.dm_os_waiting_tasks in the actual use of parallelism, but not only this one question, why does the 4 thread parallel plan appear 21 waits? How does a parallel plan execute? Our next article continues to say ....