Friday 30 April 2010

MS SQL: 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

Also

SELECT TOP 20 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.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
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
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC




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:



<code><span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> INTO </span><span class="typ">TraceTable</span><span class="pln"><br />FROM </span><span class="pun">::</span><span class="pln">fn_trace_gettable</span><span class="pun">(</span><span class="str">'C:\location of your trace output.trc'</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">default</span><span class="pun">)</span><span class="pln"><br /></span></code>


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



<code><span class="pln">SELECT COUNT</span><span class="pun">(*)</span><span class="pln"> AS </span><span class="typ">TotalExecutions</span><span class="pun">,</span><span class="pln"> <br />    </span><span class="typ">EventClass</span><span class="pun">,</span><span class="pln"> CAST</span><span class="pun">(</span><span class="typ">TextData</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">2000</span><span class="pun">))</span><span class="pln"><br /> </span><span class="pun">,</span><span class="pln">SUM</span><span class="pun">(</span><span class="typ">Duration</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">DurationTotal</span><span class="pln"><br /> </span><span class="pun">,</span><span class="pln">SUM</span><span class="pun">(</span><span class="pln">CPU</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">CPUTotal</span><span class="pln"><br /> </span><span class="pun">,</span><span class="pln">SUM</span><span class="pun">(</span><span class="typ">Reads</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">ReadsTotal</span><span class="pln"><br /> </span><span class="pun">,</span><span class="pln">SUM</span><span class="pun">(</span><span class="typ">Writes</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">WritesTotal</span><span class="pln"><br />FROM </span><span class="typ">TraceTable</span><span class="pln"><br />GROUP BY </span><span class="typ">EventClass</span><span class="pun">,</span><span class="pln"> CAST</span><span class="pun">(</span><span class="typ">TextData</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">2000</span><span class="pun">))</span><span class="pln"><br />ORDER BY </span><span class="typ">ReadsTotal</span><span class="pln"> DESC<br /></span></code>


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

From http://stackoverflow.com/questions/257906/how-can-i-log-and-find-the-most-expensive-queries



0 comments: