How to find the slowest queries

Question

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

Slow = more than N seconds, 10 for an argument.

Answer 1

In SQL 2005, you can use control views to find slow search queries . A good script that I found some time ago in SQL Server performance will help you get started; it lists the data with the slowest execution in the first place.

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 2

Before using the profiler, I check the built-in usage reports. Right-click the database, reports, standard reports, then object execution statistics.

It lists the current cached execution plans, as well as the number of resources and the number of times they were run. This usually gives a very good idea of ​​what makes the server busy.

Answer 3

The duration column does this for me, but sometimes I also look at the read and write columns.

I use the TSQL: StmtCompleted filter to receive raw queries. You might want to add others to them, such as stored procedures, but tsql is the "base" you need to look at. As the MSDN article says

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