By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.
Diagnose Server Performance Problems with SQL Server's SQL Profiler : Page 3
SQL Profiler is a great SQL Server tool that can help you reach your database optimization goals. This 10-Minute Solution presents a few preparatory steps to start using Profiler for monitoring performance problems. It discusses some techniques, tools, and commands that reveal information about server behavior.
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
