Troubleshooting SQL Server Session

--Find session ID by job name

Select A.spid,a.blocked,b.name,substring (replace (a.program_name, ' Sqlagent-tsql JobStep (Job ', '), 1,34) JobStep, A.last_batch
From master.dbo.sysprocesses a INNER join Msdb.dbo.sysjobs b
On MASTER.DBO.FN_VARBINTOHEXSTR (CONVERT (varbinary), job_id) = substring (replace (program_name, ' Sqlagent-tsql JobStep (Job ', '), 1,34)

--execution plan based on session ID

Select A1.session_id,b1.text,a1.query_plan,a1.cpu_time,a1.logical_reads,a1.sql_handle,a1.plan_handle from
(
SELECT * from sys.dm_exec_requests a cross apply sys.dm_exec_query_plan (A.plan_handle) b
where a.session_id<>@ @spid
) A1 cross apply sys.dm_exec_sql_text (A1.sql_handle) B1
ORDER BY a1.cpu_time Desc

--View Execution plan
SET STATISTICS IO on
Set STATISTICS TIME on
Set STATISTICS PROFILE on

--A single statement executes quickly, but the session is not over for a long time

Determines whether the statement loops Execution (view SP content), executes the same statement multiple times,

Causes the surface to be simple SQL, but repeated execution of the same statement, that is, a statement running slowly resulting in the session execution for a long time, in fact, nx a single time

Troubleshooting SQL Server Session