Mastering SQL Trace Using Profiler - Microsoft SQL Server 2012 Performance Tuning - page 8

It's best to store the trace file on a separate disk other than the one which is used to store
data files and log files of SQL server databases. Storing the trace file on the same physical
disk where database files are stored can degrade the performance of normal I/O operations
of other databases.
Configuring a trace by enabling the Save to table checkbox in the Trace
Properties dialog box and saving trace data directly to trace table is less
efficient. If you want your trace data to be saved in a trace table then consider
saving the trace data first in a trace file; then export your trace data from
trace file to trace table by opening the trace file in SQL Server Profiler and
selecting the Save As command from the File menu with the Trace Table…
option. When you want to save your trace in a trace table, always consider to
save your trace in a separate database.
The Events Selection tab of Trace Properties dialog box displays the selected events only and
does not show all events by default. So, we checked the Show all events option to list all the
available events. Because we did not want to capture RPC:Completed event, we excluded this
event by un-checking its checkbox from the event list and included SP:Completed event under
Stored Procedures event category.
Once we finished configuring our trace, the trace was started. To demonstrate how the events
are captured, we produced some events by executing a few T-SQL statements from another
connection through SQL Server Management Studio.
In the final figure, we can see the trace data that is produced by the events included in trace
definition. Look at the trace data that we captured. By looking at the values in different data
columns, we can learn many different things. For example, for a given trace, by examining
LoginName , TextData , and HostName we can tell who is running which query and from
which machine. By examining StartTime and EndTime data columns we can determine
when a particular query was executed and when it finished its execution.
Pausing and Stopping a trace
Once a trace is started, it can be either paused or stopped. To do this, select
the Run Trace, Pause Trace, and Stop Trace commands from the File menu
or click on the corresponding shortcut command buttons on standard toolbar.
Pausing and resuming trace: When a trace is paused, event data stops
from being captured temporarily. Once a trace is paused, it can be resumed
by starting it again. Restarting a trace resumes and continues to capture
event data again without wiping out any previously captured trace data.
Stopping and restarting trace : When a trace is stopped, event data stops
from being captured. If a trace is stopped, it can be restarted by starting it
again. Restarting a stopped trace starts to capture event data again; but any
previously captured trace data is lost.
Remember that we cannot change the Trace Properties of a trace while it is
running. To do this, we must have to pause or stop the trace.