DMVs - Relating tasks to branches

Question

In this excellent article, Paul White demonstrates branches in parallel plans.

https://sqlperformance.com/2013/10/sql-plan/parallel-plans-branches-threads

At the most fine grained level, we can look at tasks using sys.dm_os_tasks

Is there a way of relating tasks to a branch? Is there a way of determining the count of branches for a current request?

Answer 1

Is there a way of determining the count of branches for a current request?

You can kind of get at that information with a query like this:

SELECT 
    r.session_id, 
    r.dop, 
    r.parallel_worker_count
FROM sys.dm_exec_requests r 
WHERE r.session_id = 62;

Note that there's nothing special about 62, it's just where my parallel query happened to be running.

You can see this query is running at DOP 4, and has a parallel worker count of 12. So this means that the query likely has 3 parallel branches. In this case it definitely does, as I'm reusing a demo query from my blog post about CXCONSUMER waits:

I'm not sure how this works out if SQL Server is able to reserve less than DOP * branches - the situation Paul describes in the blog post you linked to:

The presence of blocking operators means that one or more parallel branches might be guaranteed to complete before others can start. Where this occurs, SQL Server can reuse the threads used to process a completed branch for a later branch in the sequence. SQL Server is very conservative about thread reservation, so only branches that are guaranteed to complete before another commences make use of this thread-reservation optimization.

To your other question:

Is there a way of relating tasks to a branch?

That's covered in detail in Paul's answer on this other question: What's the easiest and most accurate way to visualize parallel thread usage in SQL Server?

The way that query relates threads to branches is through the "nodeId" property, which you can find in the query's execution plan by hovering over the different operators.

As far as I know, there's nothing explicit in execution plans that identifies branches, or where they start and end, or which nodes belong to them. It's just known that branches are bounded by exchange operators.

If you really wanted to "assign" a number to different branches for a running request, you could divide the exec_context_id by dop, letting it round to the nearest integer.

Modifying Paul's query from the linked answer, that idea would look like this:

DECLARE @session_id smallint = 51;

SELECT
    DOT.task_state,
    DOT.scheduler_id,
    DOT.exec_context_id,
    CASE 
        WHEN DOT.exec_context_id = 0 THEN 'N/A' 
        ELSE CONVERT(varchar(3), ((DOT.exec_context_id - 1) / DER.dop) + 1)
    END AS branch_id,
    DEQP.physical_operator_name,
    DEQP.node_id,
    DEQP.thread_id,
    DEQP.row_count,
    DOWT.wait_duration_ms,
    DOWT.wait_type,
    DOWT.resource_description
FROM sys.dm_os_tasks AS DOT
INNER JOIN sys.dm_exec_requests DER
    ON DOT.session_id = DER.session_id
OUTER APPLY
(
    -- What each thread did most recently
    SELECT * 
    FROM sys.dm_exec_query_profiles AS DEQP
    WHERE
        DEQP.session_id = DOT.session_id
        AND DEQP.request_id = DOT.request_id
        AND DEQP.task_address = DOT.task_address
    ORDER BY
        DEQP.last_active_time DESC
    OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY
) AS DEQP
OUTER APPLY
(
    -- Longest wait, if any
    SELECT * 
    FROM sys.dm_os_waiting_tasks AS DOWT
    WHERE
        DOWT.session_id = DOT.session_id
        AND DOWT.exec_context_id = DOT.exec_context_id
    ORDER BY
        DOWT.wait_duration_ms DESC
    OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY
) AS DOWT
WHERE
    DOT.session_id = @session_id
    --AND DOT.task_state = N'RUNNING'
ORDER BY
    DEQP.node_id,
    DOT.exec_context_id;