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. 

 

  1. 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.
  2. 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
  3. 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
  4. Finally, launch SQL Nexus.  When prompted to start the SQLDIAG data collector, answer No.
  5. 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.