slowest performing query

In SQL 2005 You CAN USE Management views the running Queries to find SLOW A good script i found a while ago on the WILL help get you started; it lists DATA, with the slowest Performing first.

SELECT creation_time 
 
 
 , 
 last_execution_time 
 
 
 , 
 total_physical_reads 
 
 
 , 
 total_logical_reads 
 
 
 , 
 total_logical_writes 
 
 
 , 
 execution_count 
 
 
 , 
 total_worker_time 
 
 
 , 
 total_elapsed_time 
 
 
 , 
 total_elapsed_time 
 / 
 execution_count avg_elapsed_time 
 
 
 , 
 SUBSTRING 
 ( 
 st 
 . 
 text 
 , 
   
 ( 
 qs 
 . 
 statement_start_offset 
 / 
 2 
 ) 
   
 + 
   
 1 
 , 
 
 
 
 (( 
 CASE statement_end_offset 
 
 WHEN 
 - 
 1 
 THEN DATALENGTH 
 ( 
 st 
 . 
 text 
 ) 
 
 
 ELSE qs 
 . 
 statement_end_offset 
 END 
 
 
 
 - 
 qs 
 . 
 statement_start_offset 
 )/ 
 2 
 ) 
   
 + 
   
 1 
 ) 
 AS statement_text 
 
 FROM sys 
 . 
 dm_exec_query_stats AS qs 
 
 CROSS APPLY sys 
 . 
 dm_exec_sql_text 
 ( 
 qs 
 . 
 sql_handle 
 ) 
 st 
 
 ORDER BY total_elapsed_time 
 / 
 execution_count DESC 
 ; 
 
 
 
 =================================================== 
 
 select top 100 
 substring(st.text, 1, 100) as BeginningText 
 , qs.execution_count 
 , qs.total_elapsed_time / (1000 * qs.execution_count) as AvgTime 
 , qs.last_execution_time 
 , SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
 ((CASE qs.statement_end_offset 
 WHEN -1 THEN DATALENGTH(st.text) 
 ELSE qs.statement_end_offset 
 END - qs.statement_start_offset)/2) + 1) AS statement_text 
 from master.sys.dm_exec_query_stats as qs 
 outer apply master.sys.dm_exec_sql_text(qs.sql_handle) AS st 
 where datediff(hh, qs.last_execution_time, getdate()) <= 24 
 order by qs.total_elapsed_time / qs.execution_count desc