Running sessions

Often when a server seems to work slow or if I want to see what my store procedure is doing now, I use the “Running sessions” script.

It helps me to get a fast overview of what are the SQL Server doing right now. Also blocking sessions are showed and if I uncomment the two commented lines, I can often get the SQL Plan which can help me to give a hint to an missing an index.

USE master
GO

SELECT SPID       = er.session_id
      ,STATUS         = ses.STATUS
      ,[Login]        = ses.login_name
      ,Host           = ses.host_name
      ,BlkBy          = er.blocking_session_id
      ,DBName         = DB_Name(er.database_id)
      ,CommandType    = er.command
      ,ObjectName     = OBJECT_NAME(st.objectid)
      ,CPUTime        = er.cpu_time
      ,StartTime      = er.start_time
      ,TimeElapsed    = CAST(GETDATE() - er.start_time AS TIME)
      ,SQLStatement   = st.text
  FROM sys.dm_exec_requests er
  OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
  LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
  LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
 WHERE st.text IS NOT NULL
   AND er.session_Id NOT IN (@@SPID)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT er.session_Id AS [Spid]
      ,sp.ecid
      ,er.start_time
      ,DATEDIFF(SS,er.start_time,GETDATE()) as [Age Seconds]
      ,sp.nt_username
      ,er.status
      ,er.wait_type
      ,SUBSTRING (qt.text, (er.statement_start_offset/2) + 1, ((
         CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
              ELSE er.statement_end_offset END - er.statement_start_offset)/2) + 1) AS [Individual Query]
      ,qt.text AS [Parent Query]
      ,sp.program_name
      ,sp.Hostname
      ,sp.nt_domain
      ,er.plan_handle
--      ,qp.query_plan
  FROM sys.dm_exec_requests er
  JOIN sys.sysprocesses sp ON er.session_id = sp.spid
  CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
--  CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
 WHERE session_Id > 50
   AND session_Id NOT IN (@@SPID)
 ORDER BY session_Id, ecid