6

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.

10

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.

3

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

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.

1
  • 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... Feb 15 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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