How to find slowest queries

Question

Using Sql Server 2005 Profiler, what events, columns, and filters do you trace to find your slowest queries and stored procedures?

Slow = greater than N seconds, 10 for sake of argument.

Answer 1

The duration column does it for me, but sometimes I look at the reads and writes columns too.

I use the TSQL:StmtCompleted filter to get the raw queries. You may want to add others like stored procedures to that, but the tsql is the 'base' you need to view. As the MSDN article says

"The execution of a stored procedure can be monitored by the SP:Starting, SP:StmtStarting, SP:StmtCompleted, and SP:Completed event classes and all the TSQL event classes."

Answer 2

In SQL 2005 you can use management views to find slow running queries. A good script i found a while ago on SQL server performance 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;

Answer 3

Before I use the profiler, I check the built-in usage reports. Right click a database, Reports, Standard Reports, then Object Execution Statistics.

It lists the currently cached execution plans, along with the amount of resources and the number of times they've been run. This generally gives a very good idea about what's keeping the server busy.