Calculates individual wait stats weight on total waits for a given session

session-wait-stats-percentages.sql

WITH cte_total_session_waits AS
(
	SELECT	[session_id]
		,	SUM(waiting_tasks_count) AS waiting_tasks_count
		,	SUM(wait_time_ms) AS wait_time_ms
		,	MAX(max_wait_time_ms) AS max_wait_time_ms
		,	SUM(signal_wait_time_ms) AS signal_wait_time_ms
	FROM	sys.dm_exec_session_wait_stats
	GROUP BY [session_id]
)
SELECT
		S1.[session_id], W2.wait_type, W1.wait_time_ms AS total_wait_time_ms, W2.wait_time_ms
	,	CAST(W2.wait_time_ms / (NULLIF(W1.wait_time_ms, 0) * 1.0) * 100 AS decimal(5, 2)) AS perc_wait_on_total_elapsed
	,	W2.max_wait_time_ms
FROM	sys.dm_exec_sessions AS S1
LEFT
JOIN	cte_total_session_waits AS W1
  ON	S1.[session_id] = W1.[session_id]
LEFT
JOIN	sys.dm_exec_session_wait_stats AS W2
  ON	S1.[session_id] = W2.[session_id]
WHERE	S1.[session_id] = 118
ORDER BY perc_wait_on_total_elapsed DESC;