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.

Answer 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.

Answer 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

Answer 3

Using a Profiler trace, particularly when importing the trace into a database, is an excellent methodology.

If you are using SQL Server 2005 or later, DMVs (Dynamic Management Views) offer an alternative methodology:

SELECT TOP 100
(total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,
(total_logical_reads + total_logical_writes) AS total_IO,
qs.execution_count AS execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2, 
     (CASE WHEN qs.statement_end_offset = -1 
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
      ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS indivudual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
ORDER BY average_IO DESC;

One of the things to remember is that DMVs are cleared when SQL Server starts, so if your server has been up for 12 minutes it may not tell you a lot. Also, they are cumulative - so maintenance windows (checkDB) can skew the data.