Like this article? We recommend

SQL Server comes with several excellent tools for monitoring and tuning
application performance. Profiler is perhaps the most powerful of these tools;
it allows you to spy on and analyze the impact of SQL statements sent to your
server.

This article provides a very brief overview of main Profiler features. I also
discuss some of the advanced Profiler features: profiler templates, replaying a
previously recorded trace, and recording traces without Profiler’s
graphical user interface.

Profiler Trace Contents

Profiler traces mainly consist of server events you want to monitor. Profiler
provides dozens of events you can choose from, or you can define your own
events. For example, you can record every login and logout event, start and stop
of stored procedure executions, recompilation of stored procedures, and much
more. In addition, you can choose from more than 40 data columns describing the
events that you could record. Data columns you could record with the trace
include the text of the SQL statement that was sent to your server, user name,
application name, amount of CPU used by the statement, number of reads
performed, and more.

As you might guess, a trace collecting all possible events and all possible
data columns can get very large and difficult to decipher, particularly on a
busy production server. I have seen traces that grow to be several gigabytes and
can be rather overwhelming. That’s why you need to carefully consider the
events you truly need to record.

Profiler also allows you to limit the output of your trace by adding filters.
You can filter the trace by particular application, database, specific user, and
number of other categories.

Note that not all data columns are available with each event; for example,
the FileName data column makes sense for Data File Auto Grow event, but not for
the SP:completed event which records the execution of the stored procedure. A
description of each event and data column is available directly within the
Profiler as well as in Books Online. I highly recommend examining these
descriptions directly with Profiler so that you know which events matter to you
the next time you wish to create a trace.