Table of Contents Previous Next


11 SQL Profiling and Analysis : 11.3 Analyzing a SQL Trace Output

When you start a new trace, SQL Profiler displays the collected trace data in the top pane of the SQL Profiler dialog. Initially the output will be blank; to see SQL statements that have been captured thus far, click the Refresh toolbar icon, or select Refresh from the Trace menu. If the toolbar is not visible, select Tool Bar from the View menu to add the toolbar to the display.
C:\Users\susan\AppData\Local\Temp\vmware-susan\VMwareDnD\9a9ba6f9\sqltracetwo.png
The lower-right pane displays either a graphical or text-based EXPLAIN plan for the statement highlighted in the top pane.
To display key statistical information collected by SQL Profiler, open the Select Columns dialog by selecting the Columns option from the View menu. The Select Columns dialog allows you to customize the columns SQL Profiler displays in the trace output.
Sorting data in the Trace Data pane is easy; just double-click the column header and SQL Profiler will sort the data within the table by that column. Double-click the column header again to reverse the sort order. This allows you to find, for example, the longest running SQL statement very quickly.
You can filter the contents of a trace to further restrict which SQL statements are displayed. To open the Trace Filter dialog, click the Filter toolbar button, or select the Filter menu option from the Edit menu. You can use the Trace Filter dialog to construct a filter to display only the SQL statements that match your specific criteria.
The Properties pane displays the complete SQL statement for the currently highlighted row in the Trace Data pane; you can use cut and paste to copy the SQL statement to the Query Tool SQL IDE for re-working.
Select the Metrics tab in the Properties pane to display a variety of statistics for the currently highlighted statement. The metrics include information about the number of times a statement has been executed, the overall percentage of execution time vs. all other collected statements, and more.
The graphical Explain pane, provides a graphical interpretation of the execution plan for the selected query. Click a segment of the graph to display statistics for that portion of the query plan.
To export the contents of a trace to file, choose the Export option from the File menu. The Export data to file dialog allows you to re-format the trace data as it is saved to file.
To stop an active trace, click the Stop Trace toolbar button, or select Stop Trace from the Trace menu.
To view the contents of a previous trace, click on the Open Trace toolbar button, or select Open Trace from the File menu, and select a saved trace. SQL Profiler will close the current trace, and display the newly selected trace.

11 SQL Profiling and Analysis : 11.3 Analyzing a SQL Trace Output

Table of Contents Previous Next