Sessions and Requests

• Kwisatz78

  SSChampion

  Points: 12478

  More actions

  December 3, 2012 at 6:47 am

  #270878

  Hi all

  I am just trying to understand the relationship between sessions and requests. If I understand correctly then a request has to run in a session and can only have a 1 to 1 relationship with that session at runtime i.e. the request can not run across multiple session IDs.

  In trying to understand this I have been using the following queries, the first pulls information about a session, while the second is about a request.

  SELECTdes.session_id,

  des.status,

  des.login_name,

  des.[HOST_NAME],

  der.blocking_session_id,

  DB_NAME(der.database_id) AS database_name,

  der.command,

  des.cpu_time,

  des.reads,

  des.writes,

  dec.last_write,

  des.[program_name],

  der.wait_type,

  der.wait_time,

  der.last_wait_type,

  der.wait_resource,

  CASE des.transaction_isolation_level

  WHEN 0 THEN 'Unspecified'

  WHEN 1 THEN 'Readuncommitted'

  WHEN 2 THEN 'ReadCommitted'

  WHEN 3 THEN 'Repeastable'

  WHEN 4 THEN 'Serializable'

  WHEN 5 THEN 'Snapshot'

  END AS transaction_isolation_level,

  OBJECT_NAME(dest.objectid, der.database_id) AS [OBJECT_NAME],

  SUBSTRING(dest.text, der.statement_start_offset / 2,

  (CASE WHEN der.statement_end_offset = -1

  THEN DATALENGTH(dest.text)

  ELSE der.statement_end_offset

  END - der.statement_start_offset) / 2)

  AS [executing statement],

  deqp.query_plan

  FROMsys.dm_exec_sessions des

  LEFT JOIN sys.dm_exec_requests der

  ON des.session_id = der.session_id

  LEFT JOIN sys.dm_exec_connections dec

  ON des.session_id = dec.session_id

  CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest

  CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) deqp

  WHEREdes.session_id <> @@spid

  ORDER BYdes.session_id

  SELECTdest.dbid,

  dest.objectid,

  dest.number,

  dest.text AS Full_Statement,

  --Offset code to pull back actual statement executing.

  SUBSTRING(dest.text, der.statement_start_offset/2,

  (CASEWHEN der.statement_end_offset = -1

  THEN DATALENGTH(dest.text)

  ELSE der.statement_end_offset

  END - der.statement_start_offset) /2) AS Statement_executing,

  deqp.query_plan,

  session_id,

  start_time,

  status,

  command,

  --sql_handle,

  plan_handle,

  DB_NAME(database_id) AS DBName,

  user_id,

  blocking_session_id,

  wait_type,

  wait_time,

  last_wait_type,

  wait_resource,

  cpu_time,

  total_elapsed_time,

  reads,

  writes,

  logical_reads

  FROM sys.dm_exec_requests der

  CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS dest

  CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS deqp --could alos use sys.dm_exec_text_query for text version fo plan

  --WHERE status NOT IN ('Background')

  --AND database_id <> 1 --Filter out Master DB

  --AND session_id <> @@SPID --Filter out this query

  WHERE session_id <> @@SPID

  My question is why when I run these queries to I sometime see requests with a session_id but no corresponding session in the session query?

  Thanks