Gets transaction details from trace ordered by longest running to shortest running; includes stats for reads, writes, scans

getlongestrunningtransactionsfromtrace.sql

SELECT TOP 30 -- You can alter or ditch this if needs be
	d.[TransactionID],
	MIN(d.[StartTime]) AS StartTime,
	MAX(
		CASE
			WHEN d.[EndTime] IS NULL
				OR d.[StartTime] > d.[EndTime] THEN d.[StartTime]
			ELSE d.[EndTime]
		END) AS EndTime,
	DATEDIFF(
		SECOND,
		MIN(d.[StartTime]),
		MAX(
			CASE
				WHEN d.[EndTime] IS NULL
					OR d.[StartTime] > d.[EndTime] THEN d.[StartTime]
				ELSE d.[EndTime]
			END)) AS DurationSeconds,
	SUM (d.[Reads]) AS Reads,
	SUM (d.[Writes]) AS Writes,
	COUNT(CASE WHEN te.Name = 'Scan:Stopped' THEN 1 ELSE NULL END) AS ScanCount,
	SUM(CASE WHEN te.Name = 'Scan:Stopped' THEN [Reads] ELSE 0 END) AS ScanReads
FROM [YOUR_TRACE_SCHEMA].[YOUR_TRACE_TABLE] AS d -- Substitute your trace table schema and name
INNER JOIN sys.trace_events AS te
	ON te.trace_event_id = d.EventClass
WHERE d.[StartTime] > '2017-01-10 09:30:00.000' -- Substitute for your own filters
GROUP BY [d].[TransactionID]
ORDER BY DurationSeconds DESC;