Make sure you're selective in choosing both events and data columns to include in your trace. Including only events and data columns that you really need will reduce the amount of data that SQL Server will collect and transfer to Profiler.
Filters
Filters let you limit the events that a trace collects. On the Filters tab, which Figure 5 shows, you can place filters on 34 of the 43 data columns that SQL Server 2000 Profiler offers. The data columns you can't filter are BinaryData, EventClass, EventSubClass, IntegerData, LoginSid, NestLevel, ServerName, TargetLoginSid, and TransactionID. SQL Server 2000 considerably enhances Profiler's trace-filtering capabilities by supplying powerful new event criteria options. Each filter lets you specify one or more combinations of the following options: Like, Not like, Equals, Not equal to, Greater than or equal, and Less than or equal. However, not all event criteria options are available with each filter. In general, textual data columns such as ApplicationName, DatabaseName, and HostName let you specify the Like and Not like options. Numeric data columns such as DatabaseID, CPU, and SPID let you specify the Equals, Not equal to, Greater than or equal, and Less than or equal options. Temporal data columns, which include StartTime and EndTime, let you specify only the Greater than or equal and Less than or equal options.
SQL Server 2000 Profiler also introduced the new ability to specify multiple values in one filter. You can specify multiple values with the Like, Not like, Equals, and Not equal to event criteria options. For example, to capture events for SPIDs 52 and 54, expand the SPID filter, expand the Equals criteria option, specify 52 in the empty text box, press the Enter or semicolon (;) key, and specify 54 in the second text box. In SQL Server 7.0 Profiler, you can specify only one value for such a filter.
The check box Exclude system IDs lets you exclude events that reference system objects. By selecting this check box, you add the filter ObjectID >= 100. Because most system objects have an object ID that is less than 100, you eliminate most—but not all—references to system objects. This filter can considerably reduce the number of traced events because SQL Server itself performs a lot of activity against system objects.
To efficiently examine trace output, you don't necessarily need to pre-filter—place filters when you define a trace. By specifying the filters after you reopen the trace output file in Profiler, you can post-filter events that you've already collected. Pre-filtering is very important in SQL Server 7.0: Without adequate pre-filters, the trace output could become huge, causing SQL Server to miss some events. The consumer, which is the component responsible for collecting events from the queue, can't always clear the events fast enough to free space for new events when the queue reaches its threshold. The problem with pre-filtering is that you might accidentally filter out events that are vital for your examination. For example, if you're trying to find the cause of an increasing number of deadlocks but you don't know which SPIDs are involved in the deadlocks and which application issues the code that causes the deadlocks, pre-filtering on certain process IDs or certain applications might exclude the events that you need to examine.
In SQL Server 2000, pre-filtering is still important for reducing the size of the trace output and for reducing the load on the server, but you can be more lenient with filters. The trace architecture is much more efficient and stable. So as a rule, filter out what you're certain you don't need, but if you're not sure, don't specify a filter. By reopening the trace file and using post-filters, you can examine the trace output more dynamically.
Templates
SQL Server 2000 Profiler's templates let you recycle previously saved trace definitions when you create new traces. They replace the Profiler Trace Wizard, which is available with SQL Server 7.0's Enterprise Manager, and the Trace Definitions, which are available with SQL Server 7.0 Profiler. A template includes a certain combination of events, data columns, and filters. SQL Server provides nine predefined templates. For example, you can use the SQLProfilerTSQL_Replay template, which includes the events and data columns necessary for replaying traces, to keep from having to memorize and specify the exact list of items required for replay.
You can also create your own templates. To create a new template, select New, Trace Template from the File menu. You'll get a tabbed dialog box in which you can choose your events, data columns, and filters. When you're done, click the Save As button on the General tab to save your template. Alternatively, you can select File, Save As, Trace Template to create a template based on a trace that you create. This trace can be active or stopped—or even a reopened trace file or table. Before your template will appear in the list of available template names, you have to save it to \Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Server Profiler. To use a template when creating a new trace, from the General tab, simply choose the trace from the list of available template names or point to the template file.
SQL Server 2000 Profiler lets you trace SQL Server's activity much more efficiently than you can with SQL Server 7.0 Profiler. The new Profiler also gives you more information and investigative capabilities than its predecessor. You can examine the activities that applications are performing against SQL Server behind the scenes, locate slow-running queries, determine the cause of deadlocks, and more. By investing the time to master this tool, you can fully exploit its capabilities to fine-tune your SQL Server system. (For more tuning information, see the sidebar "Spotlight on the Index Tuning Wizard.")