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

Questions raised by Sys.dm_os_waiting_tasks (next)

Written in front of two, in fact, not only to say that the application of sys.dm_os_waiting_tasks, research a very long time of parallelism, they have some understanding, so to share out the hope that there is any understanding of the wrong place the great God in time to correct!!

Give the first two connections:

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

Questions raised by SQL Server application Sys.dm_os_waiting_tasks (middle)

The previous two writing has a puzzle ... The original thought of parallelism such as this statement:

SELECT * from t1 inner join t2 on t1.a = t2.a

In my understanding of parallelism is to open a few threads to get T1 data, the other several threads get T2 data, and then the association results form the final result set. But the experiment only then discovered oneself originally thought and saw the result is not very same AH!!!!

Below we use the first two examples to continue to do the experiment ...

This time we have 2 watches locked at the same time to see what is happening in the waiting.

BEGIN Tran
Update T1 Set B = getdate ()
Update t2 Set B = getdate ()

View Sys.dm_os_waiting_tasks (3 article statement code to facilitate all screenshots, scene simulation of the code is very simple, do not post it)

The same is 21 ... But note that I purposely put four resource_description of data threads in the front:

Keylock hobtid=72057594039042048 dbid=7 id=lock1ee280f00 mode=x associatedobjectid=72057594039042048

This time the lock is T2 (sys.objects is the sub-database ...) The more anxious the more chaos haha in master check partition_id = 72057594039042048 also have value queue_messages_1067150847, internal_table directly to me the whole circle!! Details Yes ~ Details But it can be seen that the parallel is not as I understand the two tables will be scanned simultaneously. The execution plan can be seen to scan the T2 table first, so this example only locks the T2, if the way I want to execute (while scanning T1, T2) should appear T1, T2 two tables have lck_m_s wait.

Statement and execution plan to be posted once again:

　　　　

The individual guesses that parallelism is actually a simultaneous manipulation of the multithreading of every physical operator, but this example alone does not explain the problem. SQL is not stupid to parallel is only operator level it? This did not find a clear answer, continue to study for a conclusion!!!

Another question union all why can't each union part execute at the same time? Is it really the operator-level multithreading parallel?

Hope the great God to answer AH!!!!

This article is the content of their own understanding, if there are errors please the Great God in time to point out!! Thank you

Space limit, the following given a small section of the test code, did not tidy their own pick it!

This is the query execution has been to obtain sys.dm_os_waiting_tasks wait information, and @a for grouping, marking a wait to crawl, so we can see the whole statement in parallel waiting.

DECLARE @a int
Set @a = 0
while 1=1
begin 
inserts into WAITING_ECEC
Select @a, * from sys.dm_os_waiting _tasks a WHERE session_id > 
set @a = @a + 1 end 
truncate TABLE WAITING_ECEC
select * from Waiting_ec EC 
Select A.resource_description,a.waiting_task_address,a.session_id,a.exec_context_id,a.wait_type, blocking_task_address,blocking_exec_context_id,blocking_session_id,
e.task_address,e.parent_task_address, Worker_address from Sys.dm_os_waiting_tasks a left
join Sys.dm_os_tasks e on a.waiting_task_address =e.task_ Address 
and a.exec_context_id = e.exec_context_id 
where a.session_id >
SELECT session_id,status, Blocking_session_id,wait_type,last_wait_type,scheduler_id,task_address from sys.dm_exec_requests where session_id = 53