Questions about SQL Server application sys. dm_ OS _waiting_tasks (below ),

Questions about SQL Server application sys. dm_ OS _waiting_tasks (below ),

Sys. dm_ OS _waiting_tasks (below)

I wrote two articles earlier, not just about sys. the Application of dm_ OS _waiting_tasks has studied Parallel Processing for a long time and has some understanding on its own. Therefore, I want to share my hope that some mistakes can be corrected in time !!

The first two links are provided:

Questions about SQL Server application sys. dm_ OS _waiting_tasks (I)

Questions about SQL Server application sys. dm_ OS _waiting_tasks (medium)

There is a doubt about the preparation of the previous two articles... the parallel statements initially considered are as follows:

select * from t1 inner join t2 on t1.a = t2.aOPTION (querytraceon 8649 ) 

In my understanding, parallelism involves several threads to obtain T1 data, and several other threads to obtain T2 data, and then join the results to form the final result set. After the experiment, I found that what I originally thought was not the same as what I saw !!!!

Next we will continue the experiment with the previous two examples...

This time we lock two tables at the same time to see what is going on in the wait.

Begin tran
Update t1 set B = getdate ()
Update t2 set B = getdate ()

View sys. dm_ OS _waiting_tasks. (For the convenience of all the statement codes in the three articles, the scenario simulation code is very simple and will not be pasted out)

 

21 are also... but note that I put the resource_description of the four data obtaining threads in front:

Keylock hobtid = 72057594039042048 dbid = 7 id = lock1ee280f00 mode = X associatedObjectId = 72057594039042048

This lock is T2 (sys. objects is a sub-database... the more worried, the more messy. Haha, check partition_id = 72057594039042048 in the MASTER. The queue_messages_1067150847 value also exists. INTERNAL_TABLE just gave me a whole circle !! Details ~ Details) But we can see that the parallel operation does not scan two tables at the same time as I understand. The execution plan shows that the T2 table needs to be scanned first, so in this example, only T2 is locked. If I want to scan the T2 table (T1 and T2 at the same time) in the same way, there should be two tables T1 and T2 with lck_m_s waiting.

Statement and execution plan:

　　　　

I personally guess that parallelism is actually a multi-thread simultaneous operation of each physical operator, but this example alone cannot explain the problem. SQL won't be silly to parallel, just at the operator level? No clear answer is found. Continue to study and draw a conclusion !!!

Another problem is: Why can't each union part of union all be executed simultaneously? Is it true that it is Operator-level multi-thread parallel?

Hope you can answer this question !!!!

The content in this article is your own understanding. If you have any mistakes, please let us know in time !! Thank you.

Space limit. The following is a short test code. If you haven't sorted it out, just pick it up!

This means that the system. dm_ OS _waiting_tasks wait information is always obtained during the query execution, and the @ a is used as the group to mark the waiting for capture at a time, so that we can see the parallel waiting of the entire statement.

declare @a intset @a = 0while 1=1begin insert into waiting_ececselect @a ,* from sys.dm_os_waiting_tasks a where session_id > 50 set @a = @a + 1end truncate table waiting_ececselect * from waiting_ecec 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 aleft 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 > 50SELECT session_id,status,blocking_session_id,wait_type,last_wait_type,scheduler_id,task_address FROM sys.dm_exec_requests where session_id = 53

Articles you may be interested in:

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