SyteLine SQL Blocking and Long Running Query Script Generator

-- Read-only diagnostic. Run in SSMS against the SQL Server instance.
SELECT TOP (25)
  r.session_id,
  r.blocking_session_id,
  r.status,
  r.wait_type,
  r.wait_time AS wait_ms,
  r.command,
  DB_NAME(r.database_id) AS db_name,
  s.login_name,
  s.host_name,
  s.program_name,
  SUBSTRING(t.text, (r.statement_start_offset / 2) + 1,
    ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) AS running_statement
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
  AND r.wait_time >= 5000
  AND DB_NAME(r.database_id) = 'SL_App'
ORDER BY r.wait_time DESC;

SELECT DISTINCT
  r.blocking_session_id AS head_blocker_session,
  s.login_name,
  s.host_name,
  s.program_name,
  s.status,
  s.last_request_start_time,
  s.last_request_end_time,
  t.text AS last_sql_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.blocking_session_id
LEFT JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
WHERE r.blocking_session_id <> 0
  AND r.blocking_session_id NOT IN (
    SELECT session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0
  );

SELECT
  st.session_id,
  s.login_name,
  s.host_name,
  s.program_name,
  s.status,
  s.last_request_end_time,
  DATEDIFF(SECOND, s.last_request_end_time, GETDATE()) AS idle_seconds
FROM sys.dm_tran_session_transactions AS st
JOIN sys.dm_exec_sessions AS s ON s.session_id = st.session_id
WHERE s.is_user_process = 1
ORDER BY s.last_request_end_time;

SELECT TOP (25)
  r.session_id,
  r.total_elapsed_time / 1000 AS elapsed_s,
  r.cpu_time AS cpu_ms,
  r.logical_reads,
  r.status,
  r.wait_type,
  s.login_name,
  s.program_name,
  t.text AS sql_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID
  AND s.is_user_process = 1
  AND r.total_elapsed_time >= 30 * 1000
ORDER BY r.total_elapsed_time DESC;