Have you tried using SQL Server Performance Dashboard? We're using this tool on SQL Server Escalation team with customers to troubleshoot real-time performance problems. It is a great way to quickly find queries with high-CPU, high-reads, waits, etc.
SQL Server 2005 Performance Dashboard Reports
http://www.microsoft.com/downloads/details.aspx?FamilyID=1d3a4a0d-7e0c-4730-8204-e419218c1efc&DisplayLang=en
We discovered a problem with one of the stored procedure when it calculates idle_connection_time for connections > 24 days. The revised stored procedure usp_Main_GetSessionInfo corrects this problem.
USE [msdb]
GO
/****** Object: StoredProcedure [MS_PerfDashboard].[usp_Main_GetSessionInfo] Script Date: 06/19/2008 15:35:54 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER procedure [MS_PerfDashboard].[usp_Main_GetSessionInfo]
as
begin
select count(*) as num_sessions,
sum(convert(bigint, s.total_elapsed_time)) as total_elapsed_time,
sum(convert(bigint, s.cpu_time)) as cpu_time,
sum(convert(bigint, s.total_elapsed_time)) - sum(convert(bigint, s.cpu_time)) as wait_time,
--sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,
--FIX to correct for sessions connected >24 days
sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute, DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,
case when sum(s.logical_reads) > 0 then (sum(s.logical_reads) - isnull(sum(s.reads), 0)) / convert(float, sum(s.logical_reads))
else NULL
end as cache_hit_ratio
from sys.dm_exec_sessions s
where s.is_user_process = 0x1
end