Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

3 Answers 3

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

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 offer an alternative methodlogy:

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 clear 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

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
share|improve this answer

Your Answer

 
discard

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.