Screen 3 shows the Filters tab. Profiler supplies three kinds of filters: specific-value filters, range filters, and inclusion/exclusion filters. By default, Profiler defines a filter that excludes all events it generates. But you can remove this filter if you need to see what Profiler is doing behind the scenes.
Specific-value filters let you supply a value to filter on for the following data columns: connection ID, database ID, object ID, and SPID. You can set only one value for each of these columns except object ID, which also lets you exclude system objects.
Range filters, which let you filter on a minimum and/or maximum numeric value, are available for CPU (milliseconds), duration (elapsed time in milliseconds), reads (logical), and writes (physical). You might use a range filter, for example, to trace only queries that have a certain minimum CPU time when you're trying to find long-running queries.
Inclusion/exclusion filters let you include or exclude only events that have a certain string in the data those events capture. You can use the percent sign (%) as a wildcard replacement for any number of unknown characters. If you want to specify more than one string, you can separate the strings with a semicolon (;). For example, to include only SQL user names that begin with the string hr_ or mgmt_, except the user hr_mark, you can use hr_%;mgmt_% as the inclusion filter and hr_mark as the exclusion filter. This kind of filter is available for application name, Windows NT or Windows 2000 (Win2K) user name, server name, SQL Server user name, and text.
What if you don't know exactly what to filter on or you want to play with a few filters separately on the same output after you stop the trace? In these cases, you can define the most inclusive filters before you start the trace, and after you stop the trace, you can open the output file or table and experiment with more exclusive filters. If you're tracing a deadlock situation, for example, after you find out which SPIDs participated in the deadlock, you can filter the trace once by one SPID, then open the trace file in another window for filtering by the other SPID.
General Options
Screen 4 shows the General tab, which lets you choose the trace destination, or where to send the trace results. Not all possible destinations are available from Profiler. For example, you can send trace output to the NT application log or forward it to a different server only by using a set of SQL Server 7.0 extended stored procedures for tracing. I cover this set in "Problem-Solving with SQL Server." However, the following destinations are available from Profiler:
Screen. If you're using Profiler to trace events (instead of using SQL Server 7.0's extended stored procedures), trace output will always go to the screen—whether you select another destination or not. You can, however, later save the output as an SQL script file, a trace file, or a table by choosing the Save As option from the File menu. Note that stopping and restarting the trace clears the previous output, so you need to save your results before restarting the trace. Also, if you change the trace properties without reopening a saved trace, the properties won't take effect until you stop and restart the trace. (You can read more about stopping and restarting traces in "Problem-Solving with SQL Profiler.")
File. If you send the output to a trace file, you can analyze it later. In addition, files let you look at the trace from different views. You might want to organize the output by different groups or data columns or even remove certain traced events and data columns. The advantage to sending the output to a trace file instead of a table is that it consumes less memory and is a lot faster because direct file system I/O doesn't incur the memory and CPU overhead that table inserts do. For instance, with a file, you don't have to spend resources handling concurrency or consistency problems.
Table. Using a table to analyze trace output can consume memory and CPU resources, but it gives you much more flexibility than a file. For example, you can use a Microsoft Excel PivotTable that uses the trace table as its data source and lets you dynamically drag the information to different areas of the PivotTable (page, rows, columns, data) to form a desired trace view. Tables also give you more flexible filtering criteria than Profiler. With Profiler, for instance, you can't analyze all events that have, say, SPIDs 8 or 9. But you can create such a filter by performing the following simple query:
SELECT * FROM mytracetable WHERE SPID in (8, 9)
You can also create triggers on a table to take certain actions as a result of system events—a function you can't perform otherwise because you can't create triggers on system tables.
You can let Profiler create the output table based on the selected data columns, or you can create a table that meets your specific objectives. For example, I created a table that enables triggers to check the TextData column. In Profiler-generated tables, this column is the ntext datatype, which is inaccessible to triggers. For my table, I simply changed the table structure to use the nvarchar (instead of ntext) datatype in the TextData column. Even if you decide to create a table, you can let Profiler create it first, then generate a script for its creation from Enterprise Manager (right-click the table name, choose All Tasks, then Generate SQL Script) and slightly change the CREATE TABLE statement to fit your needs. An easier way to generate the CREATE TABLE statement is to right-click the table name in Enterprise Manager, choose Copy, switch to Query Analyzer, and choose Paste from the Edit menu (or simply drag it from Enterprise Manager to the Query Analyzer).
You'll find that Profiler-generated tables list an event as a representative decimal code and not the name of the event. However, you can run the SQL Server 7.0 extended stored procedure xp_trace_geteventnames with no parameters to list all event classes and their names. First, create a simple table, and send xp_trace_geteventnames' output to that table, as in Listing 1. Then join your trace output table to get the event names, as in Listing 2.
Also from the General tab, you can select whether the trace definition is private or shared with anyone who logs on to the computer. (You can also set a default of private or shared from Profiler's Trace Options dialog box before defining the trace. That way, you don't need to modify this option on the General tab unless you want to change the default.)
From the Source
Finally, the General tab lets you select the source server, which is the SQL Server implementation you want to trace events from. Profiler also gives you options for managing the event queues on the source server. You access these options by clicking the computer icon next to the source server name, which displays the Source Server dialog box and the following source server options:
Number of rows to buffer on server. This option lets you select the maximum number of rows on the server queue. The default is 1000, but you can select from 1 to 20,000.
Server time-out (seconds). If the queue buffer gets full and stays full for the number of seconds you define in this option, Profiler writes a record to the queue, specifying that the queue buffer is full and causing event producers to stop sending events to the queue. The default timeout value is 5 seconds; the value range is from 1 to 10 seconds.
Boost priority (%). This option specifies the queue buffer percentage you must reach before Profiler boosts the priority of the consumer thread that collects events from the queue. In other words, this option sets the buffer percentage at which the consumer process starts collecting events from the queue faster. When Profiler stops tracing events because of a full buffer, lowering the Boost priority value (instead of increasing the queue buffer size or the timeout value) will keep the queue from taking up more queue buffer space. The default value for this option is 95 percent.
Reduce priority (%). In contrast to Boost priority, this option determines the queue buffer percentage at which SQL Server decreases the priority of the consumer thread that collects events from this queue. The default value for this setting is 90 percent.
Don't Stop Now
Profiler's wide range of tracing capabilities makes it a powerful investigative tool. But don't stop with the graphical trace definition. We're just getting started with Profiler and SQL Server 7.0's other tracing functions. "Problem-Solving with SQL Profiler" digs into replaying traces, defining traces with SQL Server's extended stored procedures for traces, and more.