SQL Server - Capture Running Sessions Detail

Whenever performance of SQL Server has started degrading very first thing normally a DBA does:

Checks current active sessions and their details:

           

            1) CPU usage

            2) Memory Usage

            3) Query being execution

            4) how long query is being executed

            5) Any locks on sessions

            6) who is the user of session

            7) From where the connection is coming

            8) When session is started

            9) Reads / writes caused

            10) session id

The details can be retrieved easily from the DMV's of SQL Server.

Please find the query which I use in my day to day operational work to figure out performance issues:

SELECT Object_name(objectid) AS ObjectName,

       Substring(stateText.TEXT, ( statement_start_offset / 2 ) + 1,

            ( ( CASE statement_end_offset WHEN -1 THEN

                  Datalength(stateText.TEXT) ELSE statement_end_offset

                        END - statement_start_offset ) / 2 ) + 1) AS statement_text,

       Db_name(database_id) AS DatabaseName,

       req.cpu_time AS CPU_Time,

       Cast(Cast(Datediff(second, last_request_start_time,

            Getdate()) AS FLOAT) / 60 AS DECIMAL(10, 3)) AS RunningMinutes,

       req.Percent_Complete,

       sess.HOST_NAME AS RunningFrom,

       LEFT(CLIENT_INTERFACE_NAME, 25) AS RunningBy,

       sess.session_id AS SessionID,

       req.blocking_session_id AS BlockingWith,

       req.reads,

       req.writes,

       sess.[program_name],

       sess.login_name,

       sess.status,

       sess.last_request_start_time,

       req.logical_reads

FROM   sys.dm_exec_requests req

       INNER JOIN sys.dm_exec_sessions sess

               ON sess.session_id = req.session_id

                  AND sess.is_user_process = 1

       CROSS APPLY sys.Dm_exec_sql_text(sql_handle) AS stateText