Performance Analysis SQL Server

sp_whoisactive: https://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/

CPU Real-Time SQL Utilization

EXEC dbo.sp_WhoIsActive
@get_transaction_info=0,
@output_column_list ='[session_id][start_time]
                  [cpu][status][context_switches][wait_info][program_name]
                 [database_name][sql_text][host_name][open_tran_count]', 
@sort_order='[CPU]DESC'

/*delta*/
EXEC dbo.sp_WhoIsActive
@delta_interval=5, @get_task_info = 2,
@output_column_list ='[session_id][start_time][context switches]
                  [CPU_delta][reads_delta][writes_delta][tempdb_writes_delta]
                  [tempdb_reads_delta][tempdb_current_delta]
                  [database_name][host_name][login_name]', 
@sort_order='[CPU_delta]DESC'

Query History Log:

SELECT --TOP 1000 
[dd hh:mm:ss.mss]
      ,[session_id]
      ,[sql_text]
      ,[sql_command]
      ,[login_name]
      ,[wait_info]
      ,[CPU]
      ,[tempdb_allocations]
      ,[tempdb_current]
      ,[blocking_session_id]
      ,[blocked_session_count]
      ,[reads]
      ,[writes]
      ,[physical_reads]
      ,[used_memory]
      ,[status]
      ,[open_tran_count]
      ,[percent_complete]
      ,[host_name]
      ,[database_name]
      ,[program_name]
      ,[start_time]
      ,[login_time]
      ,[request_id]
      ,[collection_time]
  FROM [dbwhoisactive].[dbo].[tablelogwhoisactive]
    ORDER BY [collection_time] DESC

Simplified CPU Real-Time SQL Utilization:

select
    r.session_id,
    r.cpu_time,
    ((r.total_elapsed_time/1000.0)/60.0) as MinutesRunning,
    s.login_name,
    c.client_net_address,
    s.program_name
    ,sjj.name as Job
    ,sj.step_name as 'Step Job'
    ,st.text as query
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s
on r.session_id = s.session_id
left join sys.dm_exec_connections c
on r.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
left JOIN msdb.dbo.sysjobsteps sj
  ON SUBSTRING(s.[program_name],30,34) =
  CONVERT(VARCHAR(34), CONVERT(VARBINARY(32), sj.job_id), 1)
left JOIN msdb.dbo.sysjobs sjj
ON sj.job_id = sjj.job_id
where s.login_name <> 'sa' 
and r.session_id <> @@spid
order by r.cpu_time desc, ((r.total_elapsed_time/1000.0)/60.0) desc

Blocking Sessions SQL:

SELECT  DISTINCT    
[Blocked By]    = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Host Name Block]     = ISNULL(ws.host_name, N''),        
[Login Block]         = ws.login_name,       
[Database Block]      = ISNULL(db_name(wp.dbid), N''),     
[Application Block]   = ISNULL(ws.program_name, N''),
[Session ID]    = s.session_id,
[Host Name]     = ISNULL(s.host_name, N''),        
[Login]         = s.login_name,       
[Database]      = ISNULL(db_name(p.dbid), N''),     
[Application]   = ISNULL(s.program_name, N''),
[SQL block] = ISNULL(wst.text,''),
[SQL] = ISNULL(st.text,'')
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id) 
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id) 
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id
AND r.request_id = t.request_id) 
LEFT OUTER JOIN   (     
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num     
FROM sys.dm_os_waiting_tasks   ) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1   
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
LEFT OUTER JOIN sys.dm_exec_sessions ws ON w.session_id=ws.session_id
LEFT OUTER JOIN sys.sysprocesses wp ON (ws.session_id = wp.spid)
LEFT OUTER JOIN sys.dm_exec_requests wr ON (ws.session_id = wr.session_id)
outer apply sys.dm_exec_sql_text(r.sql_handle) st
outer apply sys.dm_exec_sql_text(wr.sql_handle) wst
WHERE NOT w.blocking_session_id IS NULL
ORDER BY 1  desc;