Identifying long running tran as part of row-versioning, applicable on in 2005 & 2008

SELECT ses.original_login_name,trn.session_id,transaction_id, rtrim(ltrim(str(elapsed_time_seconds/3600)))+' Hours
'+rtrim(ltrim(str((elapsed_time_seconds/60)%60)))+' Minutes'
as 'row versioning since', ses.status,
[host_name]--,last_request_start_time,last_request_end_time
FROM sys.dm_tran_active_snapshot_database_transactions trn
inner join sys.dm_exec_sessions ses on ses.session_id=trn.session_id
ORDER BY elapsed_time_seconds DESC;

Identify queries that are generating the most IOs

SELECT TOP 10

(total_logical_reads/execution_count) AS

avg_logical_reads,

(total_logical_writes/execution_count) AS

avg_logical_writes,

(total_physical_reads/execution_count)

AS avg_phys_reads,

execution_count,

statement_start_offset as stmt_start_offset,

(SELECT SUBSTRING(text, statement_start_offset/2 + 1,

(CASE WHEN statement_end_offset = -1

THEN LEN(CONVERT(nvarchar(MAX),text)) * 2

ELSE statement_end_offset

END - statement_start_offset)/2)

FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,

plan_handle

FROM sys.dm_exec_query_stats

ORDER BY

(total_logical_reads + total_logical_writes) DESC