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,590
  • 4
  • 37
  • 100

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
  • 17,078
  • 22
  • 44
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.
  • 40,776
  • 12
  • 62
  • 141