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

F Audit Login
F Audit Logout
F ExistingConnection
F RPC : Completed
F SQL:BatchCompleted
F SQL:BatchStarting
You may notice some TextData appearing multiple times in a trace for a
single execution of a T-SQL statement. For instance, in the previous example,
you will notice two events for SELECT DB_ID() statement even if we
executed it only once. These two entries here do not represent two executions
of the said statement. Rather, they represent two different related events
associated to one single execution of the statement. For example, both events
SQL:BatchStarting and SQL:BatchCompleted raised for a single
execution of batch containing SELECT DB_ID() statement and they both
show the same T-SQL command in TextData data column. This depends
upon what events you have selected in trace definition.
In the Trace Properties dialog box, we have set the maximum file size for our trace to 128 MB.
Option Enable file rollover was also enabled by default. Enabling this option is helpful while
working with large amount of trace data.
When large amount of event data is captured, the trace file can grow very quickly and become
very large. Enabling the Enable file rollover option can prevent a trace file from becoming
very large by limiting a file to the maximum file size specified. When the file size is reached to
the maximum file size specified, SQL Server creates a new roll-over file with the same name
appended with a suffix of an incremental number for the same trace. Thus, when we have this
option enabled and the size of trace data is greater than maximum file, we have multiple trace
files for the same trace.
In this example, we are saving our trace file as C:\MyTraces\CreatingTraceWorkload.
trc . A trace can also be started without having to save the trace data. In case a trace was
started in this way without enabling the Save to file: checkbox, SQL Server manages to
keep the captured data in queue temporarily. The unsaved trace data can be saved later
on as well after gathering the required data. This can be done with the Save or Save As
command from the File menu. With the Save As command, we can save trace data in our
desired format. Selecting the Trace Table... option in the Save As command, asks for the SQL
Server connection details and destination table details where the trace data will be stored.