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.?
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.