Mfcttrf

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?

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.