Database Reference
In-Depth Information
Figure 18-19.
Capturing the the ETL processing statements
When the SSIS package completes and you have recorded all of the trace data, you can pause or stop the SQL
trace from within the SQL Server Profiler window by clicking the Pause or Stop button on the toolbar (Figure
18-19
)
.
What you see in the trace can be overwhelming at first, because a lot of additional statements are included
beyond the ones you are trying to tune. If you look closely, you will see the beginning of your SQL statements
under the TextData column. You can see the whole statement by clicking an individual SQL statement in the
Trace window (Figure
18-19
).
Once you have isolated the statements in the trace, note the time it took to run them using the Duration
column. This provides your benchmark for relative performance tuning. Then make changes to the database, set
up a new trace, and run the ETL process again to measure whether you have improved performance relative to
the performance recorded in the first trace file.
Reviewing trace data within this dialog window works fine; however, you can close this window and review
the results at a later time from the trace file. To open the trace file, access the File
➤
New Trace
➤
Trace File
menu option, and review the data (Figure
18-20
).
