925
Chapter 38: Using Profi ler and SQL Trace
38
Running the Trace
Although Profiler is just a UI that consumes data generated by SQL Trace, it’s quite good at
controlling live traces. Profiler can start, pause, and stop the trace using the typical icons
in the toolbar.Following are a few details worth pointing out:■ (^) The eraser toolbar button clears the results in Profiler.
■ If the trace flies by too fast to view but you don’t want to pause or stop the trace,
the Auto Scroll Window toolbar button enables Profiler to continue to add new
events at the bottom of the list without scrolling the window.
■ (^) While a trace is stopped, its events and data columns may be changed using the
Properties button.
Using the Trace File
When the trace is captured, it can be browsed through using the Profiler trace window, and
a Find toolbar button can help navigate the data. However, the trace is likely to be so large
that it will be difficult to manually use the data.
The solution is to save the trace file to a SQL table using File ➪ Save As; the data can then
be analyzed and manipulated as in any other SQL table.
SQL Server Profiler has the capability to replay traces. If the trace is to be replayed, certain
events must be captured. For example, the SQL Batch Start event can be replayed, but SQL
Batch Complete cannot. Also, if data inserts are replayed, you need to reset the database
to the same state as the beginning of the trace; otherwise, you’ll likely have unique data
conflicts and issues with identity column values.
In addition, the entire trace file can be submitted as a workload to the Database Engine
Tuning Advisor so that it can tune for multiple queries. However, the authors of this
book are not big fans of the Database Engine Tuning Advisor, as explained in Chapter 45,
“Indexing Strategies.”
Integrating Performance Monitor Data
Both System Monitor and Profiler present their own unique perspective on the state of the
server. The two sets of information can be merged to produce a synchronized walk-through
scenario viewing both perspectives using SQL Server Profiler.
To set up the dual-perspective experience, simultaneously capture server performance logs
using both Performance Monitor’s Counter Logs and SQL Server Profiler. These steps are specific:
- Configure System Monitor with the exact counters you want to view later. Be
sure to get the scale and everything right. Set up the Counter Log to the same
configuration.
c38.indd 925c38.indd 925 7/31/2012 10:04:19 AM7/31/2012 10:04:19 AM
http://www.it-ebooks.info