September 11, 2019 at 9:06 am
What are the relations among Sys.dm_exec_connections? sys.dm_exec_sessions and sys.dm_exec_requests
and what are the relations among Sys.dm_exec_connections? sys.dm_exec_sessions ?sys.dm_exec_requests and sys.sysprocesses ?
I check it on MSDN,but not clear the explanation yet, could you please help me to make a detailed explanation ? thanks
September 11, 2019 at 9:14 am
See session as the main concept. It is identified by the session_id, and it exists as long as the user is logged in. There are also sessions which are internal to the engine. This include system procedure and Service Broker activiation.
There is only a row in sys.dm_exec_requests when there is something actually executing in the session. In most cases you only have one request per session executing at the same time. The one exception is when MARS (Multiple Active Results Sets) is enabled for the session (this is set in the connection string). In that case there can be more than one request per session at the same time.
When it comes to sys.dm_exec_connections there is one row per session that logged in from the outside of SQL Server. For this reason, there are fewer rows in this DMV than there are in sys.dm_exec_session. Again, MARS, muddles the picture a little: when MARS is enabled, there can be multiple rows for the same session in sys.dm_exec_connections.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 12, 2019 at 9:59 am
if so, can we understand it as below ? thanks
1. first, create connection, then create session, finally create request?
2. one connection maybe includes more than one session and one session maybe includes more than one request ?
September 12, 2019 at 10:30 am
I don't know in which order connections and sessions are created, and I don't think it is relevant. I would say that for all practical purposes, they appear at the same time.
Requests appear when there is something to run.
A connection is tied to exactly one session, but if MARS is in effect there can be more than one connection for a session. (And for system-internal sessions there are no connections.)
And when MARS is in effect, there can be more than one request for the same session.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 18, 2019 at 4:06 am
Anyway , Erland Sommarskog , thank you for your kind help !
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply