0

sys.dm_exec_requests - these are the active requests

sys.dm_exec_sessions - these are the active sessions

A session can have 1 or more requests.

From perspective of detecting and resolving blocking, I am exploring the above dmvs. I am observing that session_id that exist in sys.dm_exe_sessions doesn't exist in sys.dm_exec_requests.

For example - In the sys.db_exec_requests there is a record with Wait type as LCK_M_X, and wait resource as KEY: 2....... The corresponding blocking session id is not showing in sys.dm_exec_requests.

It shows in the sys.dm_exec_sessions with status as sleeping. What does this scenario mean - that is - request blocked due to a sleeping session?

variable
  • 3,591
  • 3
  • 28
  • 81
  • 1
    This is not a duplicate because it asks What does a record in the sys.dm_exec_requests with corresponding blocking session id is not showing in the same result set mean? where as the one marked as the duplicate is how to identify the blocking session's query. – variable Jan 05 '23 at 09:37

2 Answers2

5

In SQL Server's connection model, there are three concepts: a connection, a session and a request.

The session represents the logical state of the relationship between client and server, whereas the connection represents the actual physical network connection.

A client can make multiple physical connections, and can have multiple sessions. Normally these have one-to-one correspondence, except for system sessions which have no underlying connection. (Theoretically it's possible to have multiple client connections connected to a single session also, but this is unusual).

A session can then make one or more requests, these are in sys.dm_exec_requests and have a many-to-one relationship with sys.dm_exec_sessions.

In cases when MARS is used, it is possible for there to be multiple active requests per session, this is why it is a one-to-many relationship.

A session doesn't have to have an active request. blocking_session_id just shows the session that is blocking, which may have made a request previously which locked the data, but has not committed it and is not currently running any request. A request must have a session, but a session does not need to have a request.

Charlieface
  • 12,780
  • 13
  • 35
  • Does a request always have corresponding session? – variable Sep 01 '22 at 06:49
  • Yes, it does, the column is not nullable – Charlieface Sep 01 '22 at 09:27
  • Could you specifically include the answer for the question - What does a record in the sys.dm_exec_requests with corresponding blocking session id is not showing in the same result set mean? – variable Jan 05 '23 at 09:34
  • 1
    A session doesn't have to have an active request. blocking_session_id just shows the session that is blocking, which may have made a request previously which locked the data, but has not committed it and is not currently running any request. As I said, a request must have a session, but a session does not need to have a requets. – Charlieface Jan 07 '23 at 21:19
  • Since it hasn't committed it, then shouldn't the request be showing as active request. Aren't requests within transaction considered some kind of request? – variable Jan 08 '23 at 05:37
  • No, a request is a currently running command. The fact the session is still just holding locks does not make it a request by itself. – Charlieface Jan 08 '23 at 10:54
2

sys.dm_exec_requests - "Returns information about each request that is executing in SQL Server."

Essentially this is a view of every executing query and database command. The session_id in here should relate to a session in sys.dm_exec_sessions. In a sense, this is a child view to sys.dm_exec_sessions.

sys.dm_exec_sessions - "Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks."

This is essentially a view of every connection to the SQL Server instance. If someone connects to the server but isn't actively running any queries, then you won't see their session_id from this view in the sys.dm_exec_requests view. This is a parent view to sys.dm_exec_requests, in a sense.

J.D.
  • 37,483
  • 8
  • 54
  • 121
  • Could you specifically include the answer for the question - What does a record in the sys.dm_exec_requests with corresponding blocking session id is not showing in the same result set mean? – variable Jan 05 '23 at 09:34