Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 7 down vote accepted

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.

share|improve this answer

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
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
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
share|improve this answer

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:

(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, 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()

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.