Diagnose Server Performance Problems with SQL Server's SQL Profiler : Page 3

	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.

Previous Page

Next Page

---

• Download The Code for this Article

---