How Can I Log and Find the Most Expensive Queries?

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

    RPC:Completed SP:Completed SP:StmtCompleted SQL:BatchCompleted SQL:StmtCompleted

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).

3. 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.

4. 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 TraceTableFROM ::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(CPU) AS CPUTotal ,SUM(Reads) AS ReadsTotal ,SUM(Writes) AS WritesTotalFROM TraceTableGROUP BY EventClass, CAST(TextData as nvarchar(2000))ORDER BY ReadsTotal DESC

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

The Following script gives you the result.

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,((CASE qs.statement_end_offsetWHEN -1 THEN DATALENGTH(qt.TEXT)ELSE qs.statement_end_offsetEND - 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_planFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qtCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpORDER BY qs.total_logical_reads DESC 

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.