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

GO

SET QUOTED_IDENTIFIER ON

GO

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