I was going through the DMVs in SQLServer 2005 to figure out how these views
are related and better understand performance optimization. I have a
question about the same:
I was checking the data returned by sys.dm_exec_connections,
sys.dm_exec_sessions and sys.dm_exec_requests.
Looks like, sys.dm_exec_connections, returns data about the physical
connections such as Client protocol, version, encryption, net packet size
.... etc. Basically these are details that does not change for a connection
till the connection is disconnected and reconnected.
The sys.dm_exec_sessions returns data about session information for the
connection. Typically, these would be settings that can be changed in a
session such as ANSI_NULLS, ANSI_WARNINGS ... etc.
The sys.dm_exec_requests returns data about the sql that is being executed.
This will not return any record when my session has not submitted any sql
So, for my connection to the SQLServer, I see an entry in
sys.dm_exec_connections and sys.dm_exec_sessions. Which means, if there is a
connection there will also be a session.
My question is, at any point in time, will sys.dm_exec_requests return more
than one record for a session? I tried submitting a big batch script with
lot of GO statements and expected to see multiple records returned by
sys.dm_exec_requests but it returned only one record.
If it does return multiple records for a session under what circumstance
does it do so?