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.
I guess the answer wasn't here because it's so simple! Here's what I figured out:
- Open SQL Server Profiler (in Performance Tools)
File -> New Trace...
- Connect to your database
- Click the Events Selection tab
- Select only events which correspond to SQL queries finishing:
- Click Column Filters...
- Click Duration in the list
- Expand Greater than or equal and enter the threshold time you consider "slow" in milliseconds
- Click OK
- Click Run
You can filter by
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
Here's a much more advanced treatment of the Profiler.
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
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.