To filter events in a traceIf a filter is not set, all events of the selected event classes are returned in the trace output. Filters limit the events collected in the trace. For example, limiting the Microsoft® Windows NT® usernames in the trace to specific users reduces the output data to only those users in which you are interested.
There are three types of filters:
Used to include or exclude Microsoft SQL Server™ or Windows NT users and specific applications.
Used to specify a range of values, such as the minimum and maximum duration of an SQL statement.
Used to include a specific ID number, such as a server process ID (SPID) or connection ID.
Trace event criteria are parameters used to restrict (filter) the event data captured within the trace. For example, you can monitor the activity of a specific application or exclude an application from monitoring (the default trace event criteria excludes SQL Server Profiler from monitoring itself). For example, when monitoring queries to determine the batches that take the longest time to execute, you can set the trace event criteria to monitor (trace) only those batches that take longer than 30 seconds to execute (a CPU minimum value of 30,000 milliseconds).
Each trace event criteria parameter has a set of values that determine whether the event data is included in the trace when the event class is monitored by SQL Server Profiler. The values applicable depend on the event criteria chosen.
| Event criteria option | Description |
|---|---|
| Include and Exclude | Specifies that the trace event data must include or exclude the specified value or values listed. For example, by default, SQL Server Profiler is excluded from the list of applications to be traced. A percent (%) wildcard character can be used within the value to specify any series of characters. For example, the character string SQL Server Profiler% specifies any value beginning with the character string ‘SQL Server Profiler’. A value of % indicates any valid value.
Multiple values can be included or excluded by separating each value with a semicolon (;), for example, SQL%; ISQL%; MS%. |
| Minimum and Maximum | Specifies a range of values for applicable trace event data, such as CPU use in milliseconds, execution duration for remote procedure call (RPC) and language statements, and range of severity levels. Any event data value less than the minimum or greater than the maximum is not captured in the trace. This must be a numeric value. |
| Value | Indicates a single value for the trace event data, such as a specific SQL Server connection ID, to include in the trace. Any event data not matching the value specified is not captured in the trace. Only one value can be specified for the trace event criteria. This must be a numeric value. |
These are the trace event criteria.
Trace event criteria |
Description |
Include and Exclude |
Minimum and Maximum |
Value |
||
|---|---|---|---|---|---|---|
| Application Name | Application that generates the event, for example, SQL Server Query Analyzer. | Yes | ||||
| Connection ID | Unique ID for each connection. | Yes | ||||
| CPU | Amount of CPU time (in milliseconds). | Yes | ||||
| Database ID | ID assigned to the database. The value for a database can be determined by using the DB_ID function. | Yes | ||||
| Duration | Amount of elapsed time for RPC and language statements, locks, sessions, and other events that have a defined elapsed time. | Yes | ||||
| Host Name | Name of the computer that generates the event. To determine the host name, use the HOST_NAME function. | Yes | ||||
| Host Process ID | Host process ID for the computer that generates the event. To determine the host ID, use the HOST_ID function. | Yes | ||||
| Index ID | Index ID for the object. To determine the index ID for an object, use the indid column of the sysindexes system table. | Yes | ||||
| NT Domain Name | Windows NT domain of the client that generates the event. | Yes | ||||
| NT User Name | Individual responsible for generating the event. | Yes | ||||
| Object ID (1) | Unique ID for the monitored object. Alternatively, by selecting the Exclude system objects check box, all objects are monitored, except system objects such as the sysobjects table. | Yes | ||||
| Reads | Number of logical reads performed by the server that is executing the statement. | Yes | ||||
| Server Name | Name of the server used in the trace. | Yes | ||||
| Severity | Range of error severity levels. | Yes | ||||
| SPID | Each connection has a unique SPID. | Yes | ||||
| SQL User Name | User who generates the event. | Yes | ||||
| Text | Text contained within the event data. | Yes | ||||
| Writes | Number of disk writes performed by the server that executes the statement. | Yes | ||||
| (1) Not applicable when Exclude system objects is selected. | ||||||
As a security mechanism, SQL Server Profiler automatically omits from the trace any of the security-related stored procedures that affect passwords. This security mechanism is nonconfigurable and always in effect, preventing users, who otherwise have permissions to trace all activity on SQL Server, from capturing passwords.
These security-related stored procedures are not monitored:
To filter events in a trace
