Quick Start (Postmortem Analysis)
This topic describes how you can use SQL Nexus to perform
offline analysis of Profiler trace data that was previously-collected from
either SQL Server 2000 or SQL Server 2005. For information about live
server monitoring, see the Quick Start (Live Server Monitoring)
topic.
- Capture a SQL Profiler trace that includes at least
the SQL:BatchCompleted and RPC:Completed
events. If you are using SQL Server 2005, one easy way to capture
such a trace is to use SQLDIAG. On the server you want to monitor,
click Start à Run, and execute
this command on the server that you want to monitor:
SQLDIAG
/I SD_General.XML
By default, the output files (including Profiler trace data) will be
written to C:\Program Files\Microsoft SQL
Server\90\Tools\Binn\SQLDIAG. You can redirect the trace to a
different folder by including the /O command line parameter, like this:
SQLDIAG
/I SD_General.XML /O m:\tracedata
To stop the data capture, hit CTRL+C in the
SQLDIAG console window. For additional information about using
SQLDIAG to capture data, see the SQLdiag Utility topic in SQL Server 2005 Books
Online.
- Use TraceBuster.exe to import the Profiler trace
data. You may collect the trace on one SQL Server instance and
import/analyze the data offline on another instance. You can find
TraceBuster.exe in the “Collection” subfolder within the directory where
SQL Nexus is installed. For example, if your trace files are in
C:\trace, and if you want to import the trace data to a SQL Server
instance called SQLQA01, you could use a command line like this one:
TraceBuster.exe -iC:\trace\*.trc
–Ssqlqa01 -E
- Once the trace data has been loaded, execute the TraceAnalysis.tem SQL Script (despite the non-standard
file extension, this can be executed just like is any other .SQL script
file). TraceAnalysis.tem is located in the
“Collection” subfolder within the directory where SQL Nexus is
installed. You can use a standard script execution tool like sqlcmd, osql, or Query Analyzer. This command
shows TraceAnalysis.tem being executed using sqlcmd:
sqlcmd –Ssqlqa01 –E –i<sql_nexus_dir>\Collection\TraceAnalysis.tem
- Finally,
launch SQL Nexus. When prompted to start the SQLDIAG data collector,
answer No.
- Select
the “Profiler Trace Analysis” to see a list of the most expensive queries
in the captured workload. You can sort the list by cumulative CPU
cost, reads, writes, or duration. Cick the
Report Parameters button in the report toolbar to zoom into a particular
time window. Click on any of the queries to drill into a more
detailed report showing the execution characteristics of that query over
time.