Professional SQL Server® 2005 Performance Tuning

5.1. Preparations for Setting Up a Trace

Setting up a useful trace requires proper planning. The purpose of a trace should be clearly defined. Selecting SQL trace events in a trace requires a good understanding of both SQL trace event classes and terminologies. This section will provide you with what you should know for setting up a trace.

5.1.1. Checking for a Complete "Issue" Statement

A well-prepared issue statement will help to define the scope of a trace. Issues can be described in many forms; at the minimum, the following information is required:

• Who: This information confirms the status of the issue.

• What: These symptoms of the performance issue will help determine which SQL trace template or events to use for a trace.

• When: When did the issue occur? Does the issue persist? This information will determine when to schedule the next trace.

• System environment description: Operating system, SQL Server editions, and service pack levels.

• Error messages: Do you have system, application, or other related logs documented as evidence?

• Service Level Agreement (SLA): Do you have related measurable descriptions in the SLA for the issue?

Profiler trace setup requires many detailed decisions: how to connect to a SQL instance, how to save trace results, when to start and stop a trace, what events to select, and how to filter data for a specific need. Information in the issue statement may help to make these decisions.

Best Practice Each Profiler trace should have a specific purpose, whether ...