Same session ID(with same SQL text) but 3 different connection ids

Question

I was listing out all sessions open in my sql server 2016 production server. I have issues with slow performance and i have been investigating it. I see that i have several same sessions(session_id) with multiple connection_ids. All are in suspended state(must be waiting for some resource..investigating this also) and the sql text also is same.

I wonder why i have 3 different connection_ids for the same session_id.

When i looked at the sys.dm_exec_connections for those connection ids i see the below info.

Can someone help me to understand this.?

Additional Info:

That query runs in parallel.So if this indicates the parallel execution then i have the below scenarios where:

• The scalar function is executing with three diff connection_ids and with same session_id.That function is not touching any tables as it takes a value are format(does substring) it to display in a different way.There is no way that the particular function can run in parallel as per my understanding.
• As shown in the screeshot ,the return statement from the same function also have 3 different connection_ids.
• I have a MAXDOP of 8 set,so if this indicated parallel execution,then the number can be greater than 3 as per my understanding.

Answer 1

The clue here is that those connections each share a "parent_connection_id," which per the docs:

Identifies the primary connection that the MARS session is using

I noticed the aliases used in those queries heavily implies your app is using Entity Framework. Default connection settings (scaffolded out by the Visual Studio project templates) will enable Multiple Active Result Sets (MARS) in the Entity Framework application's connection string.

So this is normal behavior of MARS - where multiple queries will share a "parent connection" (and thus share a session), interleaving the queries submitted to it by the application.

The most common reason cited for turning on MARS with EF is to help facilitate lazy loading - which is probably why all of the queries look similar (it'll be pulling data in by ID as needed).

---

As far as performance problems related to this, it's hard to know without seeing the queries and code. With the MARS and EF stuff, I'd be tempted to guess that it's the N+1 query problem, which I've discussed on my blog. The solution is to change the .NET code so that it's not executing a query for each row of an already-retrieved resultset.