If 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® 4.0 or Windows® 2000 user names in the trace to specific users reduces the output data to only those users in which you are interested.
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 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). You can specify filters by right-clicking on the appropriate trace event criteria value and entering the information.
To filter events in a trace template
To modify filters
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 Profiler. The values applicable depend on the event criteria chosen.
| Event criteria option | Description |
|---|---|
| Like | Specifies that the trace event data must be like the text entered. Allows multiple values. |
| Not like | Specifies that the trace event data must not be like the text entered. Allows multiple values. |
| Equals | Specifies that the trace event data must be equal to the value entered. Allows multiple values. |
| Not equal to | Specifies that the trace event data must not be equal to the value entered. Allows multiple values. |
| Greater than or equal | Specifies that the trace event data must be greater than or equal to the value entered. |
| Less than or equal | Specifies that the trace event data must be less than or equal to the value entered. |
The following table lists the trace event criteria (filters), and the options available for each.
Trace event criteria |
Description |
Like |
Not like |
Equals |
Not equal to | Greater than or equal | Less than or equal |
|---|---|---|---|---|---|---|---|
| Application Name | Application that generates the event, for example, Query Analyzer. | Yes | Yes | ||||
| Client ProcessID | The process ID of the application calling Microsoft® SQL Server™. | Yes | Yes | Yes | Yes | ||
| Column Permissions | Indicator of whether a column permission was set. | Yes | Yes | Yes | Yes | ||
| CPU | Amount of CPU time (in milliseconds). | Yes | Yes | Yes | Yes | ||
| Database ID | ID assigned to the database. The value for a database can be determined by using the DB_ID function. | Yes | Yes | Yes | Yes | ||
| Database Name | Name of the database in which the statement of the user is running. | Yes | Yes | ||||
| Duration | Amount of elapsed time for remote procedure calls (RPCs) and language statements, locks, sessions, and other events that have a defined elapsed time. | Yes | Yes | Yes | Yes | ||
| End Time | Time at which the event ended. This column is not populated for starting event classes, such as SQL:BatchStarting or SP:Starting. | Yes | Yes | ||||
| Error | Error number of a given event. Often this is the error number stored in sysmessages. | Yes | Yes | Yes | Yes | ||
| File Name | The logical name of the file being modified. | Yes | Yes | ||||
| Handle | Integer used by ODBC, OLEDB, or DB-Library to coordinate execution with the server. | Yes | Yes | Yes | Yes | ||
| Host Name | Name of the computer that generates the event. To determine the host name, use the HOST_NAME function. | Yes | 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 | Yes | Yes | Yes | ||
| Login Name | Name of the login of the user (either SQL Server security login or the Windows login credentials in the form of DOMAIN\Username). | Yes | Yes | ||||
| Mode | Integer used by various events to describe a state the event has received or is requesting. | Yes | Yes | Yes | Yes | ||
| NT Domain Name | Windows NT or Windows 2000 domain of the client that generates the event. | Yes | Yes | ||||
| NT User Name | Individual responsible for generating the event. | Yes | Yes | ||||
| Object ID | 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 | Yes | Yes | Yes | ||
| Object Name | The name of the object being referenced. | Yes | Yes | ||||
| Object Type | Value representing the type of the object involved in the event. This value corresponds to the type column in sysobjects. | Yes | Yes | Yes | Yes | ||
| Owner Name | Database user name of the object owner. | Yes | Yes | ||||
| Permissions | Integer value representing the type of permissions checked. Values are:
1 = SELECT ALL |
Yes | Yes | Yes | Yes | ||
| Reads | Number of logical reads performed by the server executing the statement. | Yes | Yes | Yes | Yes | ||
| Role Name | Name of an application role being enabled. | Yes | Yes | ||||
| Severity | Range of error severity levels. | Yes | Yes | Yes | Yes | ||
| SPID | Each connection has a unique SPID. | Yes | Yes | Yes | Yes | ||
| DBUserName | User who generates the event. | Yes | Yes | ||||
| Start Time | Time at which the event started, when available. | Yes | Yes | ||||
| State | Equivalent to an error state code. | Yes | Yes | Yes | Yes | ||
| Success | 1 = success. 0 = failure (for example, a 1 means success of a permissions check and a 2 means a failure of that check). |
Yes | Yes | Yes | Yes | ||
| Target Login Name | For actions which target a login (for instance, adding a new login), the name of the targeted login. | Yes | Yes | ||||
| Target User Name | For actions which target a database user (for instance, granting permission to a user), the name of that user. | Yes | Yes | ||||
| Text Data | Text contained within the event data. | Yes | Yes | ||||
| Writes | Number of disk writes performed by the server executing the statement. | Yes | Yes | Yes | Yes |
As a security mechanism, SQL Profiler automatically omits from the trace any of the security-related stored procedures affecting 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.
The following security-related stored procedures are not monitored:
sp_addmergepullsubscription_agent