Sample Header Ad - 728x90

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

8 votes
1 answer
1057 views
I use .NET to execute SQL operations on SQL Server 2014, here's the code used:
lang-cs
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?
Asked by Hikari (1603 rep)
Jun 19, 2019, 01:43 PM
Last activity: Jun 20, 2019, 09:23 AM