WEBINAR:
On-Demand
Application Security Testing: An Integral Part of DevOps
|
 |
Figure 2 | The preset template selections in Profiler
|
You probably won't want to change the events and filters until you get a little experience with the tool, because you easily could eliminate something you need out of ignorance. (I've done it myself.) That's why it's handy to work with your own copy of the templates, so that you avoid hosing the selections that were preset there for you (see
Figure 2). In the event that you do hose your template selections, you'll need to skulk around the network to find another SQL Server installation and copy the trace template back over to your machine. Those are the files with the .tdf extensions. After a few tries, you will be creating your own templates tailored to your own requirements, but while you're learning, it's best to be cautious about changing the stock ones. Limit yourself to creating copies of them until you are confident in your selections.
Using the Filters
|
 |
Figure 3 | On the Filter page, the number of reads and the time in milliseconds before the event logs
|
In addition to limiting Events and Data columns, Profiler helps you get more pointed information with the power to filter events you want to trace and to further refine your selection by setting thresholds on these events. For example, the trace templates are sometimes a little too rich in their selection of events, and after a few tries you may want to reduce the information that you log. You can lighten the selections in your copy of the trace templates by limiting the events to only SQL Statement Starting or Batch Starting events and including the number of reads and the duration they take to complete. Then, on the Filter page, you can enter the number of reads you are interested in and the time in milliseconds you want to pass before you log the event (See
Figure 3). Additionally, you can exclude system events to screen out a ton of extraneous messages from the trace, letting you focus only on the duration of long-running SQL statements.
|
 |
Figure 4 | The trace output with the filters not set
|
With the filters not set, the trace output can be quite verbose (see Figure 4). So it's really easy to get confused by the blizzard of information Profiler produces. You'll really need to set its limits.