921
Chapter 38: Using Profi ler and SQL Trace
38
A Profiler trace is primarily viewed interactively (because otherwise you should be running
a scripted server-side trace), but the data can also be written to a file or a SQL Server table.
This is useful for further manual analysis, viewing alongside System Monitor counter data,
or importing into the Database Engine Tuning Advisor.When Profiler data is written to a file, SQL Server writes 128KB chunks of data at a time for
performance. Conversely, writing data to a table involves a series of row inserts that doubles
the transaction log workload and seriously hinders SQL Server’s performance. Avoid tracing
directly to a table on the server being traced; although, writing to a different server is OK.Best Practice
To s ave Profi ler data for further analysis, use the high-performance fi le method and a server-side
trace (discussed later). If you want to analyze the data using T-SQL (and analyzing trace data with SQL
aggregate queries and WHERE...LIKE clauses is extremely useful), save the trace to a fi le. After the
trace is complete, open the trace fi le using Profi ler and select File ➪ Save As ➪ Ta b l e.Selecting Events and Data Columns
The Events Selection tab (see Figure 38-2) determines the data points that SQL Trace cap-
tures. SQL Trace can monitor more than 175 key SQL Server events. The default templates
configure a trace with a few preselected events, but there’s so much more.Two important details of the interface are easily overlooked: The Show all events and Show
all columns check boxes in the lower-right side of the Events Selection tab enable viewing
and selecting from the complete set of events and columns. Without those options checked,
the form displays only the currently selected events and columns. Although this can be
useful to filter out noise, you must enable these options to select additional events or add
columns to existing events.The following list shows the event categories, along with the number of events in each cat-
egory and a comment:■ (^) Broker (13): The first category covers events related to Service Broker activity.
■ CLR (1): Only CLR assembly loads can be traced.
■ (^) Cursors (7): These events are not related to T-SQL server-side cursors. They track
ADO client-side cursor activity.
■ (^) Database (6): Tracks database file activity, such as autogrowth and mirror
connections.
■ (^) Deprecation (2): A useful set of events. You can run these events when exercising
code or running unit tests to highlight any deprecated features used in the code.
c38.indd 921c38.indd 921 7/31/2012 10:04:19 AM7/31/2012 10:04:19 AM
http://www.it-ebooks.info