.NET executes a SQL query and Active Monitor shows multiple rows blocking each other

Question

I use .NET to execute SQL operations on SQL Server 2014, here's the code used:

using(SqlConnection conn = new SqlConnection(connectionString)){
    //https://stackoverflow.com/questions/1880471/capture-stored-procedure-print-output-in-net
    conn.InfoMessage += new SqlInfoMessageEventHandler(logSqlMessages);
    conn.Open();
    using(SqlCommand stmt = new SqlCommand{
        Connection = conn,
        CommandText = sql,
        CommandTimeout = 30000 // The time in seconds to wait for the command to execute. The default is 30 seconds.
        //,CommandType = CommandType.StoredProcedure
    })
    {
        affectedRecords = stmt.ExecuteNonQuery();
    } // using stmt
} // using conn

When I look at Active Monitor, there are tens of rows referencing the same operation. They all have the same session_id, some of them have Task State running and most of them are suspended. Some of them have LastWaitTime CXPACKET and most are PAGEIOLATCH_SH.

I also ran a query on SQL Server and same behavior happened on Active Monitor.

Maybe it's a normal behavior of it, but it's odd that a SELECT operation creates multiple rows and blocks itself like that. Any idea of what may be causing it?

Answer 1

Multiple rows in activity monitor for the same SPID means your query has been chosen to be executed in parallel across multiple threads.

Each row on Activity Monitor actually represents one ECID, not one SPID.

Activity monitor only exposes the SPID column (session_id), but there is an additional column exposed in sys.sysprocesses called ECID (Execution Context ID) - this is a unique identifier for each thread the query is utilising.

The sysprocesses system view is deprecated, but you can find ECID by another name (exec_context_id) in the sys.dm_os_tasks view (as well as other task-related views).

Here is a sample query that captures all of the execution context IDs associated with queries running in a particular session, and what, if anything, they are waiting on:

SELECT 
    dot.session_id,
    dot.exec_context_id,
    dot.task_state,
    dowt.wait_type,
    dowt.wait_duration_ms,
    dowt.blocking_session_id,
    dowt.resource_description
FROM sys.dm_os_tasks dot
    LEFT JOIN sys.dm_os_waiting_tasks dowt
        ON  dowt.exec_context_id = dot.exec_context_id
            AND dowt.session_id = dot.session_id
WHERE dot.session_id = 51
ORDER BY exec_context_id;

In SQL 2016 two new columns were added to sys.dm_exec_requests - DOP and parallel_worker_count - these can be used to check the whether the request is running in parallel or not.

Another key item to note is that the CXPACKET wait is inherently a parallelism wait - this by itself tells us the query is using more than one thread.

The PAGEIOLATCH_SH is a wait indicating those threads are reading data from disk into memory.