How do I identify slow queries in sql server?

Question

I've found long, complicated instructions like this when googling for the answer to this question, an brief reference links such as in this post.

I'm looking for a succinct a procedure as possible to generate a list of SQL queries with execution time where execution time > some_threshold.

Solution 1

I guess the answer wasn't here because it's so simple! Here's what I figured out:

1. Open SQL Server Profiler (in Performance Tools)
2. File -> New Trace...
3. Connect to your database
4. Click the Events Selection tab
5. Select only events which correspond to SQL queries finishing:
   • RPC:Completed
   • SQL:BatchCompleted
6. Click Column Filters...
7. Click Duration in the list
8. Expand Greater than or equal and enter the threshold time you consider "slow" in milliseconds
9. Click OK
10. Click Run

You can filter by ApplicationName, NTUserName, etc if you have a lot of applications running and want to cut down on noise. You can also show only some columns, e.g. just TextData and Duration.

Here's a much more advanced treatment of the Profiler.

Solution 2

you can use this to get the top 10 expensive queries (If you are on Sql server 2005 and above):

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

Comments

[Bogdan Mart]

Wow, what's the difference between this query and provided by @DaniSQL ? It seems to produce really different results. THIS query mostly shows slow stored procedures for me. hm...