Take the 2-minute tour ×

The activity monitor in sql2k8 allows us to see the most expensive queries. Ok, that's cool, but is there a way I can log this info or get this info via query analyser? I don't really want to have the Sql Management console open and me looking at the activity monitor dashboard.

I want to figure out which queries are poorly written/schema is poorly designed, etc.

Thanks heaps for any help!

share|improve this question

7 Answers 7

up vote 48 down vote accepted
  1. Use SQL Server Profiler (on the tools menu in SSMS) to create a trace that logs these events:

2. You can start with the standard trace template and prune it. You didn't specify whether this was for a specific database or the whole server, if it is for specific Db's, include the DatabaseID column and set a filter to your DB (SELECT DB_ID('dbname')). Make sure the logical Reads data column is included for each event. Set the trace to log to a file. If you are leaving this trace to run unattended in the background, it is a good idea to set a maximum trace file size say 500MB or 1GB if you have plenty of room (it all depends on how much activity there is on the server, so you will have to suck it and see).

  1. Briefly start the trace and then pause it. Goto File->Export->Script Trace Definition and pick your DB version, and save to a file. You now have a sql script that creates a trace that has much less overhead than running through the profiler GUI. When you run this script it will output the Trace ID (usually @ID=2); note this down.

  2. Once you have a trace file (.trc) (either the trace completed due to reaching the max file size or you stopped the running trace using

    EXEC sp_trace_setstatus @ID, 0 EXEC sp_trace_setstatus @ID, 2

You can load the trace into profiler, or use ClearTrace (very handy) or load it into a table like so:

SELECT * INTO TraceTable
FROM ::fn_trace_gettable('C:\location of your trace output.trc', default)

Then you can run a query to aggregate the data such as this one:

SELECT COUNT(*) AS TotalExecutions, 
    EventClass, CAST(TextData as nvarchar(2000))
 ,SUM(Duration) AS DurationTotal
 ,SUM(Reads) AS ReadsTotal
 ,SUM(Writes) AS WritesTotal
FROM TraceTable
GROUP BY EventClass, CAST(TextData as nvarchar(2000))

Once you have identified the costly queries, you can generate and examine the actual execution plans.

share|improve this answer
cheers mitch! sounds a bit more complex that i thought was required, but i'll most definitely give it a go :) cheers! –  Pure.Krome Nov 3 '08 at 4:46
Been flat out - i'm expecting to get a chance towards the end of nov .. and then i'll mark the question or ask more q's. –  Pure.Krome Nov 15 '08 at 6:11

A late answer...

Use DMVs to find out. Easier then profiler...

share|improve this answer
Thanks Glenn! Nice answer also :) –  Pure.Krome Jun 14 '09 at 0:50
I think the new link is sqlserverperformance.wordpress.com/2008/01/21/… –  David McClelland Mar 18 '13 at 15:25

The Following script gives you the result.

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_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 
share|improve this answer
This is exactly what I wanted. Its easy to create a job to save it out. Thanks. –  Jbecwar Jul 16 '12 at 20:20
Is this taken from here? => blog.sqlauthority.com/2010/05/14/… If so, it is a better style to mention it... –  YvesR Oct 26 '12 at 11:41

I had never heard of this tool before, but Microsoft provides a set of reports that do a fantastic job of giving you exactly this - including slowest queries. Check out their Performance Dashboard Reports.

Not sure if they're SQL 2008-compatible, but worth checking out.

share|improve this answer

Would the SQL Server Profiler do what you need? I haven't used 2008 yet so I don't know if the tool is still in there but if it is I believe you can set up a trace to log queries that meet specific criteria (such as those that execute and drive CPU up above a certain threshold).

We've used this on our project and it did a pretty good job of helping us troubleshoot poorly executing queries (though don't leave it on full time, rely on the general Windows Performance Counters for performance health tracking).

share|improve this answer
hmm.. i've used the profiler heaps, but that doesn't summarise information. it's a line per query type of thing. –  Pure.Krome Nov 3 '08 at 4:30

There's a new tool, Performance Studio in SQL Server 2008 which builds on top of Dynamic Management Views maintained automatically by the server, that gives an overview of the server performance. It worth checking out.

share|improve this answer

(DELL)Quest SQL Optimizer for SQL Server 9.0 introduces Find SQL module which allow users to locate the most resource-intensive SQL in your SQL Server. https://support.quest.com/softwaredownloads.aspx?pr=268445262

share|improve this answer

Your Answer


